4

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.

Improve this question

1 Answer 1

Reset to default
5

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 = '[email protected]'
    ,@body = @EMessage
    ,@subject = @Subject
    ,@body_format = 'HTML';

SET NOCOUNT OFF;
GO

Result:

enter image description here

Improve this answer
4
  • thank you for your answer i need to add something like changes in databases like adding new database alerting database,dropping database .. when it got happen that information i need to store it in table
    – Chanukya
    Jul 20, 2017 at 11:20
  • Something that was very non-obvious to me was that "GRANT DATABSE" event included any DB permissions changes to users.
    – Robert Sievers
    Jul 20, 2020 at 20:58
  • Where can I find the information for these records in the database or how can I generate a report of that?
    – Lucas Matos
    Jan 14, 2021 at 20:48
  • If you want you can change the table variable to a real table and SELECT any record which you want to.
    – Yunus UYANIK
    Jan 15, 2021 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.