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?
Best Answer
You can have a server side trace as a SQL Agent Job and set up the job to start whenever SQL agent start and a second schedule to start every xx number of minute. That way job will always start and second schedule will take care if somebody stop it by mistake.
I suggest you create a database role and make all the users member of that role. I understand your concern of forgetting to add users to the role or assign permission. I personally do not prefer to have too many users with privilege to change schema in my database so the list should be small and a role will make it easy for you to manage the privilege.