In SQL Server, version 2008R2 Enterprise, 64 bit, we created a database to track DDL changes.
I was inspired by this solution: https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
Then we created a trigger, on each database, like this:
CREATE TRIGGER [CaptureChanges] ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE AS ....
Where, after the as, the code make a simple insert in the audit database.
At first, it works very well, but when a user, owner of a database, but with no permission on the audit database, tries to alter a stored, SQL Server rises an error for insufficient permission.
This is correct, because the user ha not permissions on the audit database.
So I used EXECUTED AS using a user with sysadmin role:
CREATE TRIGGER [CaptureChanges] ON DATABASE WITH EXECUTE AS 'useradmin' FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
But now it does not work because this error:
Msg 916, Level 14, State 1, Procedure CaptureChanges, Line 0 [Batch Start Line 0] The server principal "useradmin" is not able to access the database "userdatabase" under the current security context.
It seems, when you use EXECUTED AS, you are sandboxed to a single database: http://www.sommarskog.se/grantperm.html#EXECUTE_AS
So, I cannot use EXECUTED AS, because it does not work cross db. I dont like the idea to create the table to track changes in every DB where a user can be dbowner.
I can assign permission to every user to write in the destination table, but then I need to remember this for every new user. Someone will sure forgot this.
I also considered a trace, but it can be easly stopped or it will not restart at server start.
Is the trigger the wrong way? Can I fix this in some way?