DDL Trigger to log database level security commands.

In my last post I talked about reasons why your permissions might go missing. One of the reasons, and in my experience, one of the more unusual reasons, is that a command was run that changed the permissions. SQL doesn’t natively log these. Well, technically it does. They can be found in the default trace. But I don’t generally consider that to be terribly useful because on a busy server what’s available in the default trace may not last long. Regardless I ended up creating a DDL trigger to collect any database level security commands run. There are other ways to do this but for various reasons I decided to go with a trigger. Primarily because I could create one piece of code and cover every database in the instance.

The logging table.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SecurityLog](
[Event] [sysname] NULL,
[EventDate] [datetime] NOT NULL,
[Instance] [sysname] NULL,
[DBName] [sysname] NULL,
[LoginName] [nvarchar](4000) NULL,
[Username] [sysname] NULL,
[ObjectName] [nvarchar](128) NULL,
[ObjectType] [nvarchar](128) NULL,
[Permission] [sysname] NULL,
[RoleName] [sysname] NULL,
[Command] [nvarchar](max) NULL,
[EventData] [xml] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX ci_SecurityLog ON [dbo].[SecurityLog]([EventDate])
GRANT INSERT ON dbo.SecurityLog TO PUBLIC
GO

A couple of important things you’ll notice. I put the table in master and granted INSERT access to the public role. The best practice is not to put things into master and for good reason. You don’t want master to be any larger than it needs to be and in case of instance migrations and/or disaster recovery you want to avoid having too much to worry about in the system databases, at least outside of the normal jobs, SSIS packages (if in msdb), permissions, etc. And again, the best practice says you shouldn’t grant permissions to the public role because that gives that permission to everyone (that doesn’t have a specific deny). So why did I do it this way?

To be honest I could have put the table in another database but generally everyone has at least CONNECT to master. Combine that with granting the INSERT permission to the public role and everyone now has the ability to insert into the table. Basically I’m avoiding potential issues down the line. To be fair this is somewhat of a “Do as I say not as I do.” situation. There are certainly other ways to handle this that might be better. This was just easy. On a side note you can read about my opinions on best practices here.

Last but not least you’ll notice I put limited indexing on the table. You might want to add some indexes depending on your needs but I’m not expecting this table to grow by more than a few thousand rows a month at the absolute worst. Also in a live environment I’d probably put some kind of cleaning job in place to only keep the last couple of weeks to a month worth of information to keep the size down even more.

The trigger.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [SecurityLogging]
ON ALL SERVER
FOR CREATE_USER, ALTER_USER, DROP_USER, 
CREATE_ROLE, ALTER_ROLE, DROP_ROLE,
ADD_ROLE_MEMBER, DROP_ROLE_MEMBER,
GRANT_DATABASE, DENY_DATABASE, REVOKE_DATABASE
AS 
DECLARE @EventData XML = EVENTDATA()
--
BEGIN TRY
INSERT INTO master.dbo.SecurityLog
   SELECT  
'Event'= Event.Data.value('(//EventType)[1]', 'sysname') 
              ,'EventDate'= Event.Data.value('(//PostTime)[1]', 'datetime') 
              ,'Instance'= Event.Data.value('(//ServerName)[1]', 'sysname') 
              ,'DBName'= Event.Data.value('(//DatabaseName)[1]', 'sysname') 
  ,'LoginName'= ORIGINAL_LOGIN() 
              ,'Username'= ISNULL([Grant].ee.value('(.)[1]', 'sysname'),
Event.Data.value('(//ObjectName)[1]', 'sysname'))
              ,'ObjectName'= CASE WHEN [Grant].ee.value('(.)[1]', 'sysname') IS NOT NULL 
THEN Event.Data.value('(//ObjectName)[1]', 'sysname')
ELSE NULL END
              ,'ObjectType'= CASE WHEN [Grant].ee.value('(.)[1]', 'sysname') IS NOT NULL 
THEN Event.Data.value('(//ObjectType)[1]', 'sysname')
ELSE NULL END
              ,'Permission'= Perm.ission.value('(.)[1]', 'sysname') 
              ,'RoleName'= Event.Data.value('(//RoleName)[1]', 'sysname') 
              ,'Command'= Event.Data.value('(//TSQLCommand)[1]', 'nvarchar(max)')
  ,'EventData'  = @EventData
FROM @EventData.nodes('EVENT_INSTANCE') AS Event(Data)
OUTER APPLY @EventData.nodes('//Grantee') [Grant](ee)
OUTER APPLY @EventData.nodes('//Permission') Perm(ission)
END TRY
BEGIN CATCH
THROW 51000, 'Please contact the DBA team when making security changes.', 1
END CATCH
GO
ENABLE TRIGGER [SecurityLogging] ON ALL SERVER
GO

A couple of things of note in the trigger.

• I’ve mentioned the events individually because I find that easier to read.
• Only the database level security events are mentioned because if I want to log instance level security events I’m going to log them differently, to a different table at the very least, and I’ll put them in a different trigger.
• My XML skills are at best so-so. I copied some of this from elsewhere (don’t remember where) and had a friend double check me.
• On the other hand my naming skills for the OUTER APPLYs are exceptional IMO.
• My error just says “the DBA team”. Several of my co-workers asked “which DBA team?” to which I responded “good question”. At least in our case it’s going to depend on which instance this is on, which database, and to be quite frank, what day of the week it is. If the person has access to change security they’d better know who’s in charge of that instance.