--html--
DECLARE @xml NVARCHAR(MAX)
SET @xml = CAST(( select User ID AS 'td','', Name AS 'td','',
SECurity AS 'td','', LST_LOGIN AS 'td','',LOGIN_FAIL AS 'td','',
LOGIN_inactive AS 'td'
FROM DBA..table_Nmae
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
--select @xml
SET @body ='<html><body><H3>DBA_audit permissions Monthly Report</H3>
<table border = 1>
<tr>
<th> User ID </th> <th> Name </th> <th> SECurity </th> <th> LST_LOGIN </th> <th> LOGIN_FAIL </th>
<th> LOGIN_inactive </th> </tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ajayMail',
@body = @body,
@body_format ='HTML',
@recipients = 'ajeyudub@sqlajay.com',
@subject = 'DBA_audit permissions Monthly Report' ;
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( select User ID AS 'td','', Name AS 'td','',
SECurity AS 'td','', LST_LOGIN AS 'td','',LOGIN_FAIL AS 'td','',
LOGIN_inactive AS 'td'
FROM DBA..table_Nmae
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
--select @xml
SET @body ='<html><body><H3>DBA_audit permissions Monthly Report</H3>
<table border = 1>
<tr>
<th> User ID </th> <th> Name </th> <th> SECurity </th> <th> LST_LOGIN </th> <th> LOGIN_FAIL </th>
<th> LOGIN_inactive </th> </tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ajayMail',
@body = @body,
@body_format ='HTML',
@recipients = 'ajeyudub@sqlajay.com',
@subject = 'DBA_audit permissions Monthly Report' ;
No comments:
Post a Comment