Audit sys.database_principals?

• Veronka

  Default port

  Points: 1425

  More actions

  March 26, 2010 at 11:31 am

  #386586

  Hi,

  Is there a way to track who is creating or droping users in the DB, and changing permissions for users? -- from which machine this happened would be the best.

  We have an issue on sporadic disapearing some login from different DB, and we cannot pin point how this is happening. Any idea how to track this?

  Thanks.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 26, 2010 at 11:41 am

  #1140613

  keep in mind that if someone is restoring the database, the restore point might not have the users you are adding back constantly; that's a probable thing to look for first.

  after that, if you are sure someone is actually dropping users, you'd have to use a DDL trigger;

  here's one i put together after reading BOL, it is untested:

  CREATE TRIGGER TR_DB_UserEvents

  ON DATABASE

  FOR --CREATE_USER,-- (Applies to the CREATE USER statement, sp_adduser, and sp_grantdbaccess.)

  --ALTER_USER,-- (Applies to ALTER USER statement and sp_change_users_login.)

  DROP_USER

  AS

  -- Get the EventData XML:

  DECLARE @x AS XML

  SELECT @x = EVENTDATA()

  -- Check DB & Schema:

  IF @x.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') = 'YourDB'

  BEGIN

  RAISERROR ('DB Trigger "TR_DB_UserEvents" does not allow Users to be dropped in this schema.', 16, 1)

  ROLLBACK

  END

  END

  GO

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Paul White

  SSC Guru

  Points: 150467

  More actions

  March 26, 2010 at 8:57 pm

  #1140869

  For an ad-hoc requirement, you could set up a Profiler trace on Security Audit -> Audit Database Principal Management Event.

  Event sub-class 3 captures user drops.

  You could also use an Event Notification based on the AUDIT_DATABASE_OBJECT_MANAGEMENT_EVENT trace event. Event Notifications are less intrusive than DDL Triggers, since they do not form part of the initiating transaction. You also have to be careful of session SETtings, like QUOTED_IDENTIFIER, when calling XML methods in a DDL Trigger.

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi