I have a DDL trigger defined for database- and server-level events in SQL Server 2008R2 (Standard) which exists to log all DDL events into an audit table in an admin database. It's only function is to extract the relevant data from EVENTDATA() and insert into this table. For the purpose of the insert (as only sysadmins can access the admin database), I have created a dedicated SQL login with only INSERT permission to this table, and granted IMPERSONATE on this login to public. This is intended to prevent permissions-related errors from the trigger firing and attempting to insert into the audit table, when the caller does not have the necessary access to the database/table.
Here is the trigger definition:
CREATE TRIGGER [ddl_log] ON ALL SERVER FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS AS BEGIN DECLARE @data XML SET @data = EVENTDATA() EXECUTE AS LOGIN='<dummy login>' INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName) VALUES ( GETDATE(), CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()), @data.value('(/EVENT_INSTANCE/EventType)', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)'), CONVERT(NVARCHAR(100), HOST_NAME()), @data.value('(/EVENT_INSTANCE/DatabaseName)','nvarchar(100)') ) ; REVERT END
I have had no issues with this trigger since implemented months ago. However, now it appears to be preventing even a sysadmin from executing an ALTER LOGIN, DROP LOGIN, etc. under certain circumstances as follows:
My environment also includes MS Dynamics GP 2010 (Great Plains). Great Plains allows an admin to manage users, and for each new Great Plains user, the software creates a SQL login for that user in SQL Server. Resetting a password in the Great Plains interface resets the SQL password. And so forth...
However, even if logged into Great Plains as 'sa' as long as the above trigger is enabled any attempt to alter or drop a login fails with error 15151 (Cannot alter the login 'loginname', because it does not exist or you do not have permission). If I disable the trigger, everything works normally.
The same operations executed in SSMS, or through some other interface, are successful, even for non-sysadmins who have some level of DDL permissions. It only fails when performed in Great Plains. A profiler trace of the operation shows that GP is merely submitting a standard T-SQL 'ALTER LOGIN' or 'DROP LOGIN' statement, and that the statement correctly shows as called by the sa account. It does not appear that the session ever switched to a different context, other than for the insert into the audit table (which it never actually got to, as no record was logged for the statement). And just in case the session somehow was maintaining the wrong context after that impersonation, I tried making the dummy-insert login a sysadmin with no success.
My question is, are there certain combinations of SET options/connection settings, etc. that could result in this type of behavior or issues with DDL trigger execution that could prevent a sysadmin from performing certain operations? Or is there some other avenue to investigate that I am completely missing?