asked
Disabling an AD account causing an audit trigger to fail
Hi, On Friday the AD account of a DBA who has left was deleted, the account had been disabled for 24 hours. Almost as soon as the account was disabled, there were a whole host of issues with a particular SQL 2005 named instance. A server wide audit trigger was blamed. I have checked the trigger and the database / schema / table that it writes to and I can see no trace of the account in terms of ownership. The only mention is that the audit trigger excludes auditing on a number of named accounts. I have tried to recreated the situation in test, using a fake account in place of the DBA's account, but I can not recreate the issue. Following are extracts from the SQL server error log. Message Error: 17892, Severity: 20, State: 1. Logon failed for login 'domain\svc_cluster' due to trigger execution. Error: 17892, Severity: 20, State: 1. Could not obtain information about Windows NT group/user 'domain\oldDBA', error code 0x534. So - I think that it may have something to do with the fact that the DBa created the login trigger and within the code for that we've got - CREATE trigger [ServerWideLoginLogs] on all server with execute as self Am I right in saying that the 'execute as self' means that it will execute as the user that created the trigger? Thanks in advance! :-) M
You are correct in saying that [Execute As Self][1] is the person who created (or altered) the trigger. If you change that to execute as a service account with sufficient permissions, that might be enough to solve your problem--as long as there aren't any other instances of "execute as self" or explicit hard-coding of that old DBA account. [1]:
http://msdn.microsoft.com/en-us/library/ms188354.aspx
Just a thought - you could use the fact that "The actual user ID of the person creating or modifying the modules is stored in the execute_as_principal_id column in the sys.sql_modules or sys.service_queues catalog view" (from the MSDN link in Kevin's answer) to query your systems for any other instances where this account would be used, and proactively modify them to avoid a potential future issue.
10 People are following this question.
