SQL Server track DDL CREATE USER

Question

I am trying to track user creation. I have looked at the DDL triggers in many posts but those seem only to track objects, not users. Is there a way for me to track/record when a user is created or deleted in SQL Server?

Answer 1

CREATE_USER is absolutely a trackable DDL event, as is DROP_USER, and both have been since SQL Server 2005. BOL is hard-pressed for decent examples, though. The truth is the DDL trigger eventdata schema is not flexible enough to always have an entity named the way you want (like UserName). It's not intuitive, and may be the source of your confusion, but you actually need to pull the name of the created user from ObjectName:

USE [your_database_name];
GO

CREATE TRIGGER CatchUser
ON DATABASE
FOR CREATE_USER, DROP_USER
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @x XML = EVENTDATA();

  -- INSERT dbo.LoggingTable(Columns)
  SELECT 
    EventType = @x.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(256)'),
    UserName  = @x.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(256)'),
    LoginName = @x.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)'),
    StartTime = @x.value('(/EVENT_INSTANCE/PostTime)[1]',  'datetime');
END

However, if you are just trying to audit this data after the fact, you can also pull this information from the default trace, if you poll frequently enough.

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces WHERE is_default = 1;

SELECT EventType = CASE EventSubClass WHEN 3 THEN 'CREATE_USER'
  WHEN 4 THEN 'DROP_USER' END, TargetUserName, LoginName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 109 -- Create DB User Event
AND DatabaseName = N'your_database_name'
ORDER BY StartTime DESC;

This will get adds and drops, and you're supposed to be able to tell from the EventSubClass which event it was, but my experience is not matching with the documentation - I get 3 for Add, 4 for Drop, but they say 1 is Add, 2 is Drop, 3 is grant access, and 4 is revoke access. shrug