When you have a huge environment and some senior developers have sysadmin rights on some of you DB servers(Yeah yeah, I know it's bad) it's good to have a trigger to
audit server roles membership. Also it's good to receive an email when
server roles membership changes. Below is my trigger to
audit server roles membership, I use 2 events: ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER.
ALTER TRIGGER MYSERVERRROLEAUDIT
ON ALL SERVER
FOR ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER
AS
SET NOCOUNT ON
DECLARE @eventtable TABLE (EventType NVARCHAR(MAX), ObjectName varchar(100), EventDate datetime, CurrentUser varchar(100), TSQLCommand NVARCHAR(MAX))
DECLARE @EventType NVARCHAR(MAX)
DECLARE @SchemaName NVARCHAR(MAX)
DECLARE @DBName VARCHAR(100)
DECLARE @Subj VARCHAR(200)
DECLARE @ObjectName varchar(100)
DECLARE @ObjectType varchar(100)
DECLARE @Message NVARCHAR(MAX)
DECLARE @TSQL NVARCHAR(MAX)
SELECT
@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')
,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
,@DBName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
,@TSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
insert into @eventtable
select @EventType, @ObjectName, getdate(), SUSER_SNAME(), @TSQL
SET @Message =N'
' +
N'Event Type | ' +
N'Object Name | ' +
N'Date | ' +
N'Current User | ' +
N'TSQL Command |
' +
CAST ( ( SELECT td = EventType, '',
td = ObjectName, '',
td = EventDate, '',
td = CurrentUser, '',
td = TSQLCommand, ''
FROM @eventtable
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
'
SELECT @Subj='Role membership has changed on '+@@servername+' server'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'your_profile_name',
@recipients = 'your@distributionlist.com',
@body = @Message,
@importance= 'High',
@subject =@Subj,
@body_format = 'HTML';
SET NOCOUNT OFF
GO