February 25, 2009 at 6:10 am
Hi All,
I created a DDL trigger to log DDL events, I hve a generic database present on all production systems called DBA, the idea is to have objects relating to the collection of DMV data onto a seperate DBA database, I also decided to log the DDL events that the trigger traps on the DBA database as well.
The problem I am having now is that, ordinary users dont have permission on the DBA database, I dont want to have to create every user on the DBA database and give them permissions, I tried geting round the problem by having a stored procedure in the Trigger which does the insert, and in that stored procedure use the keyword EXECUTE AS, but this doesnt work as the users dont have a security context on the DBA database.
Is there a way to make this work seemlessly without the need to grant permissions, as I want to remove the need to grant permission on the DBA database before users can carry on with thier day to day activities.
February 25, 2009 at 6:47 am
In SQL Server 2005/2008 you can sign a Stored Procedure with a certificate which allows you to grant access to the Certificate User. Here is a link to the BOL entry on this:
http://msdn.microsoft.com/en-us/library/bb283630(SQL.90).aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply