asked
DDL trigger execution problem
Hello Everyone, I have created a DDL Triger to capture all the event and get notified, the script is working fine if i am executing from sysadmin user. but when i try to make any change in database from non-sysadmin user ( dbowner ) it giving me error Msg 297, Level 16, State 1, Procedure DDLTrigger_Sample, Line 13 The user does not have permission to perform this action. In this script i have create a database [AUDIT] in which there is table which keep the record and created the trigger on the database from where i want to capture the event. I have given full access on audit db too. Thanks Basit K
For a DDL-trigger to work, the user who issues a DDL-command in a database, say DB1, need to have permission to both execute the database trigger and to write to the AUDIT-table in the AUDIT-database. I thought for a while that using WITH EXECUTE AS clause in the DDL-trigger would solve it, but that really doesn't do the trick for you, since the WITH EXECUTE AS clause locks the permissions to only the AUDIT-database, unless you have set the TRUSTWORTHY option on DB1 to ON (which you probably don't want to do, because that opens up a security hole in itself). How is your DDL-trigger created? Are you using WITH EXECUTE AS clause in the trigger? If not - which permissions are set for the users changing objects? On the source database and on the AUDIT database.
15 People are following this question.
