So I’ve been tasked with a number of security related tasks lately.  Here is another script that I just finished and, once again, I figured that I would share.

Recently someone asked me if they could get an email alert from SQL Server with the details of the action every time a security related change happened on the server.  He wanted the email to trigger on things like a login being created or a user being added to a database or a login being added to a server role, etc.

Below is the script that I wrote to create a server level trigger that would automatically send an email with the pertinent details whenever one of these events occur.  This script will create a SQL Server trigger at the server level that will send an email to the specified email address with details of changes when various security related events happen on the server.

There are a whole cornucopia of different events that you can tell your trigger to fire on.  I chose several that seemed most pertinent to the task at hand.

** If you happen to use the script be sure to update the email address and the mail profile name to the correct values for your environment.

As always, please comment if you have come up with a better solution and would be willing to share.

Enjoy!

USE [master]
GO

create trigger [trig_security_notification] on all server
for ALTER_AUTHORIZATION_SERVER
 , GRANT_SERVER, REVOKE_SERVER
 , CREATE_LOGIN, DROP_LOGIN, ALTER_LOGIN
 , ALTER_AUTHORIZATION_DATABASE -- ******* Comment out this line SQL Server 2005. ******************
 , GRANT_DATABASE, REVOKE_DATABASE -- ******* Comment out this line SQL Server 2005. ******************
 , CREATE_USER, DROP_USER, ALTER_USER -- ******* Comment out this line SQL Server 2005. ******************
 , ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER -- ******* Comment out this line SQL Server 2005. ******************
 , ADD_ROLE_MEMBER , DROP_ROLE_MEMBER -- ******* Comment out this line SQL Server 2005. ******************
 , CREATE_ROLE, DROP_ROLE, ALTER_ROLE -- ******* Comment out this line SQL Server 2005. ******************
as

 declare @EventType nvarchar(512), @SchemaName nvarchar(128), @ObjectName nvarchar(128), @ObjectType nvarchar(128), @DatabaseName nvarchar(128), @CommandText nvarchar(max), @PostTime datetime, @LoginName nvarchar(128), @ServerName nvarchar(128), @suser_sname nvarchar(128);
 declare @body nvarchar(max), @recipients nvarchar(max), @page_recipients nvarchar(max), @subject nvarchar(255), @from_address nvarchar(max), @mailProfile nvarchar(128);

 select @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)') --as [EventType]
 , @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)') --as [SchemaName]
 , @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)') --as [ObjectName]
 , @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)') --as [ObjectType]
 , @DatabaseName = EVENTDATA().value(' (/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)') --as [DatabaseName]
 , @CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)') --as [CommandText]
 , @PostTime = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') --as [PostTime]
 , @LoginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)') --as [LoginName]
 , @ServerName = EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(128)') --as [ServerName]
 , @suser_sname = suser_sname(); --as [UserName];

 SELECT @recipients = 'YourEmail@YourDomain.com', @mailProfile = 'YourServerMailProfileName';
 select @from_address = replace(isnull(convert(nvarchar(128), serverproperty('servername')), convert(nvarchar(128), @@servername)), '\', '_') + '@yourdomain.com'

 set @subject = isnull(@EventType, '') + ' occurred on ' + isnull(@ServerName, convert(nvarchar(128), serverproperty('servername'))) + ' at ' + substring(isnull(convert(varchar(50), @PostTime, 121), ''),1,19) + ' by ' + isnull(isnull(@LoginName, @suser_sname), '') + ' on object ' + isnull(@ObjectName, '') + '. Command text: ' + isnull(@CommandText, '');

 set @body = 'An Security change occurred on SQL Server ' + isnull(@ServerName, convert(nvarchar(128), serverproperty('servername'))) + char(13)+char(10) + char(13)+char(10);
 set @body = @body + 'Event Type: ' + isnull(@EventType, '') + char(13) + char(10);
 set @body = @body + 'Action performed by: ' + isnull(isnull(@LoginName, @suser_sname), '') + char(13) + char(10);
 set @body = @body + 'Object Name: ' + isnull(@ObjectName, '') + char(13) + char(10);
 set @body = @body + 'Object Type: ' + isnull(@ObjectType, '') + char(13) + char(10);
 set @body = @body + 'Command Text: ' + isnull(@CommandText, '') + char(13) + char(10);
 set @body = @body + 'Action occurred at: ' + isnull(convert(varchar(50), @PostTime, 121), '') + char(13) + char(10) + char(13) + char(10);
 set @body = @body + 'Please investigate if this action was not expected.';

 --*******************************************************************
 -- send email
 --*******************************************************************
 if len(@subject) > 255 set @subject = substring(@subject,1,255);
 exec msdb.dbo.sp_send_dbmail @profile_name = @mailProfile, @recipients = @recipients, @from_address = @from_address, @reply_to = @from_address, @subject = @subject, @body = @body, @importance = 'High';
 print @body;

GO

ENABLE TRIGGER [trig_security_notification] ON ALL SERVER
GO