mstjean (5/5/2008)
I have a user (developer) that tried to alter a view in a db. He does not have rights to the db ("Admin") I write the 2 tables to. He DOES have rights to do what he was doing.Granting him even write rights to the 2 audit tables seems counter intuitive; I thought the db and server triggers should execute in another security context... maybe this is my incorrect assumption. I don't want to turn on cross db ownership chaining-- that would expose me to a different set of risks when I am trying to ratchet those down.
His Error:
Msg 916, Level 14, State 1, Procedure trgMonitorChange, Line 33
The server principal "the users domain name" is not able to access the database "Admin" under the current security context
Any thoughts/recommendations?
Perhaps I should have put something in my article regarding my intention for these triggers as I originally put them on production servers that had limited access. The database and server triggers will run in the security context of the individual account that is triggered by the DDL change. Therefore when the trigger goes off and wants to write the data to a separate database, that user requires write permission to the destination table.
In your case you have the following options:
1. Change the database trigger to point to an auditing table within the local database instead of a separate database and give that local auditing table write permissions to the developer.
2. Give the developer write permissions on the table that is in the auditing database. This may not be effective if you have more than one account making changes to your database.