8/16/2012

Audit server roles membership

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'' +
    N'' +
    N'' +
    N'' +
    N'' +
    CAST ( ( SELECT td = EventType, '',
    td = ObjectName, '',
    td = EventDate, '',
    td = CurrentUser, '',
    td = TSQLCommand, ''
    FROM  @eventtable
FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'
Event TypeObject NameDateCurrent UserTSQL Command
' 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

No comments:

Post a Comment