Get notified when a user is elevated to sysadmin

I recently came across an issue where a co-worker reached out to me about their SQL Server having issues with users getting added to the sysadmin role without their knowledge. After taking to him more about it, I figured the easiest way for him to stay in the loop when users are added to this role was to setup a server trigger.

Server triggers are great for tracking logins and when server wide events occur. To get notified, you simply setup the server trigger with a call to the msdb.dbo.sp_send_dbmail proc and that’s it. Below is the code for this. This code assumes you have already configured database mail. If you haven’t, you must do this for the trigger to fire.

USE [master]
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER trg_server_sec_notification
ON
ALL SERVER WITH EXECUTE AS ‘sa’
FOR DDL_SERVER_SECURITY_EVENTS

AS
BEGIN
BEGIN TRY

SET NOCOUNT ON;

DECLARE @data XML = EVENTDATA();

DECLARE @login_nm NVARCHAR(250) = @data.value(‘(/EVENT_INSTANCE/LoginName)[1]’,’NVARCHAR(255)’)
DECLARE @server_role NVARCHAR(255) = @data.value(‘(/EVENT_INSTANCE/RoleName)[1]’,’NVARCHAR(255)’)
DECLARE @tsql NVARCHAR(MAX) = @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’,’NVARCHAR(max)’)
DECLARE @login_that_was_elevated NVARCHAR(255) = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’,’NVARCHAR(255)’)
DECLARE @event NVARCHAR(255) = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’,’NVARCHAR(255)’)

— No notification necessary if no add event occurred
IF @event <> ‘ADD_SERVER_ROLE_MEMBER’ RETURN;

DECLARE @change_type VARCHAR(10)
IF CHARINDEX(‘ADD’,@tsql) > 0
SET @change_type = ‘added’
ELSE
SET @change_type = ‘removed’

DECLARE @msg VARCHAR(8000) = CONCAT(
‘Note: Login [‘,@login_that_was_elevated,’] has been ‘,@change_type,’ to server security group <b>[‘,@server_role,’]</b> by user [‘,@login_nm,’].<br><br>’
)
;

DECLARE @sub VARCHAR(500) = CONCAT( ‘Server Security Notification: [‘,@@SERVERNAME,’]’)

EXEC msdb.dbo.sp_send_dbmail @recipients = ‘YOUR_EMAIL@YOUR_DOMAIN.com’
,@body = @msg
,@body_format = ‘HTML’
,@subject = @sub
,@exclude_query_output = 1

END TRY
BEGIN CATCH
END CATCH

END
GO

ENABLE TRIGGER trg_server_sec_notification ON ALL SERVER
GO

 

Advertisements
Get notified when a user is elevated to sysadmin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s