question

basit 1 avatar image
basit 1 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
userexecuteddl-changes
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.