Trigger to track changes

Question

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?

Answer 1

I also considered a trace, but it can be easly stopped or it will not restart at server start.

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.

Is the trigger the wrong way? Can I fix this in some way?

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.