Post reply

DDL Trigger / central storage of results for all DB

  • July 4, 2009 at 9:13 pm

    #132477

    Hi there,

    i've been trying to apply a ddl trigger that just logs all DDL DB events to a central DB.table. It works fine if the table and trigger are in the same DB.

    So I have DB1.dbo.Storage where I would like store all the events.

    I apply the same DDL Trigger to DB2; whenthe trigger tries to fire, it doesn't want to run because it looks like it's running in the security context of the user in DB2; this user does not have access to db1.dbo.storage.

    Is there a way to force the trigger to run without having to add additional security permissions on all the DBs?

    Or do I need to add all the users to the db1.dbo.storage table to allow this trigger to work?

    I tried added a WITH EXECUTE AS 'SA' hoping it would run SA which had access to all DBs.

    Cheers

  • July 6, 2009 at 8:37 am

    #1020427

    I have to ask, what kind of application do you have where the users are running DDL statements? This is not a typical situation, at least not in my experience.

    You should be able to do an Execute As that will work, or you can add those users that have CREATE table, etc... rights in the user database to a role in the repository database.

    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