SQL Server Audit Report to Email

Question

Post reply

SQL Server Audit Report to Email

vamshi.sql

SSCommitted

Points: 1770
More actions
April 4, 2013 at 9:59 pm

#394527

Hi Team,
I am trying to configure SQl srerver logins audit report email generation.
Once I run the query, Result should generate in to email. But I am getting some error and not able to solve that.Can you please help me on this..
***************
declare @query nvarchar(max)
set @query =
'SELECT l.name,
CAST(CASE sp.[state] WHEN N'D' THEN 1 ELSE 0 END AS bit) AS DenyWindowsLogin,
CASE WHEN N'U' = l.type THEN 0
WHEN N'G' = l.type THEN 1
WHEN N'S' = l.type THEN 2
WHEN N'C' = l.type THEN 3
WHEN N'K' = l.type THEN 4 END AS LoginType,
CAST(CASE WHEN (sp.[state] IS NULL) THEN 0 ELSE 1 END AS bit) AS HasAccess,
CAST(sl.is_policy_checked AS bit) AS PasswordPolicyEnforced,
CAST(sl.is_expiration_checked AS bit) AS PasswordExpirationEnabled,
l.create_date AS CreateDate,
l.modify_date AS DateLastModified,
LOGINPROPERTY(l.name, N'BadPasswordCount') AS BadPasswordCount,
LOGINPROPERTY(l.name, N'BadPasswordTime') AS BadPasswordTime,
LOGINPROPERTY(l.name, N'DaysUntilExpiration') AS DaysUntilExpiration,
LOGINPROPERTY(l.name, N'IsExpired') AS IsExpired,
LOGINPROPERTY(l.name, N'IsLocked') AS IsLocked,
LOGINPROPERTY(l.name, N'IsMustChange') AS IsMustChange,
LOGINPROPERTY(l.name, N'LockoutTime') AS LockoutTime,
LOGINPROPERTY(l.name, N'PasswordLastSetTime') AS PasswordLastSetTime,
l.is_disabled AS IsDisabled
FROM sys.server_principals AS l
LEFT OUTER JOIN sys.server_permissions AS sp
ON sp.grantee_principal_id = l.principal_id
AND sp.[type] = N'COSQ' -- Connect permissions
LEFT OUTER JOIN sys.sql_logins AS sl
ON sl.principal_id = l.principal_id
LEFT OUTER JOIN sys.credentials AS c
ON c.credential_id = l.credential_id
WHERE
l.[type] IN ('U', 'G', 'S', 'C', 'K')
AND l.principal_id NOT BETWEEN 101 AND 255 -- ##MS% certificates
AND
( sp.[state] = N'D' -- DenyWindowsLogin
OR sp.[state] IS NULL -- HasAccess
OR CAST(sl.is_policy_checked AS bit) = 0
OR CAST(sl.is_expiration_checked AS bit) = 0
OR l.create_date > GETDATE()-1
OR l.modify_date > GETDATE()-1
OR l.is_disabled > 0
OR LOGINPROPERTY(l.name, N'DaysUntilExpiration')<= 5
OR LOGINPROPERTY(l.name, N'IsExpired') > 0
OR LOGINPROPERTY(l.name, N'IsLocked') > 0
OR LOGINPROPERTY(l.name, N'IsMustChange') > 0
OR LOGINPROPERTY(l.name, N'BadPasswordCount') > 2
',
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MS SQL Support',
@recipients = '@wipro.com',
@subject = 'Login Aduit Report',
@importance= HIGH,
@query = @query;
**********************
Error :
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'D'.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 1

It's all dynamic SQL. You will need to double up all single quotes between the very first quote and the last one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)