HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Audit login trigger for permission changes sql server

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
triggerauditsqlpermissionloginchangesforserver

Problem

In my office some one changed the permissions of users on production database. so after that i have decided to write a ddl kind of trigger to audit permission changes. I have googled for this but i couldn't find a proper solution. So can you people suggest me few links or any sample code?

I would like to capture who changed the permissions and when permissions got changed.

Thanks in advance.

Solution

Check this
out.

CREATE TRIGGER DDL_AUDIT_Logins ON ALL SERVER
FOR DDL_DATABASE_SECURITY_EVENTS AS

SET NOCOUNT ON;

DECLARE @EventsTable TABLE (
    EType NVARCHAR(max)
    ,EObject VARCHAR(100)
    ,EDate DATETIME
    ,EUser VARCHAR(100)
    ,ECommand NVARCHAR(max)
    );
DECLARE @EType NVARCHAR(max);
DECLARE @ESchema NVARCHAR(max);
DECLARE @DBName VARCHAR(100);
DECLARE @Subject VARCHAR(200);
DECLARE @EObject VARCHAR(100);
DECLARE @EObjectType VARCHAR(100);
DECLARE @EMessage NVARCHAR(max);
DECLARE @ETSQL NVARCHAR(max);

SELECT @EType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',
 'nvarchar(max)')
,@ESchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',
 'nvarchar(max)')
,@EObject = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
 'nvarchar(max)')
,@EObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(max)')
,@DBName = EVENTDATA().value('
(/EVENT_INSTANCE/DatabaseName)[1]',
 'nvarchar(max)')
,@ETSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 
'nvarchar(max)');

INSERT INTO @EventsTable
SELECT @EType
    ,@EObject
    ,GETDATE()
    ,SUSER_SNAME()
    ,@ETSQL;

SET @EMessage = 'Login_Event: ' + @EType + CHAR(10) + 'Event Occured at: '
 + Convert(VARCHAR, GETDATE()) + CHAR(10) + 'Changed Login: ' + @EObject + 
CHAR(10) + 'Changed by: ' + SUSER_SNAME() + CHAR(10) + 'Executed T-SQL: ' + 
@ETSQL

SELECT @Subject = 'SQL Server Login changed on ' + @@servername;

EXEC msdb.dbo.sp_send_dbmail @recipients = 'DDL_Alert@companydomain.com'
    ,@body = @EMessage
    ,@subject = @Subject
    ,@body_format = 'HTML';

SET NOCOUNT OFF;
GO


Result:

Code Snippets

CREATE TRIGGER DDL_AUDIT_Logins ON ALL SERVER
FOR DDL_DATABASE_SECURITY_EVENTS AS

SET NOCOUNT ON;

DECLARE @EventsTable TABLE (
    EType NVARCHAR(max)
    ,EObject VARCHAR(100)
    ,EDate DATETIME
    ,EUser VARCHAR(100)
    ,ECommand NVARCHAR(max)
    );
DECLARE @EType NVARCHAR(max);
DECLARE @ESchema NVARCHAR(max);
DECLARE @DBName VARCHAR(100);
DECLARE @Subject VARCHAR(200);
DECLARE @EObject VARCHAR(100);
DECLARE @EObjectType VARCHAR(100);
DECLARE @EMessage NVARCHAR(max);
DECLARE @ETSQL NVARCHAR(max);

SELECT @EType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',
 'nvarchar(max)')
,@ESchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',
 'nvarchar(max)')
,@EObject = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
 'nvarchar(max)')
,@EObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(max)')
,@DBName = EVENTDATA().value('
(/EVENT_INSTANCE/DatabaseName)[1]',
 'nvarchar(max)')
,@ETSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 
'nvarchar(max)');

INSERT INTO @EventsTable
SELECT @EType
    ,@EObject
    ,GETDATE()
    ,SUSER_SNAME()
    ,@ETSQL;

SET @EMessage = 'Login_Event: ' + @EType + CHAR(10) + 'Event Occured at: '
 + Convert(VARCHAR, GETDATE()) + CHAR(10) + 'Changed Login: ' + @EObject + 
CHAR(10) + 'Changed by: ' + SUSER_SNAME() + CHAR(10) + 'Executed T-SQL: ' + 
@ETSQL

SELECT @Subject = 'SQL Server Login changed on ' + @@servername;

EXEC msdb.dbo.sp_send_dbmail @recipients = 'DDL_Alert@companydomain.com'
    ,@body = @EMessage
    ,@subject = @Subject
    ,@body_format = 'HTML';

SET NOCOUNT OFF;
GO

Context

StackExchange Database Administrators Q#180196, answer score: 5

Revisions (0)

No revisions yet.