Post reply

DDL Trigger fails for privileges.

  • September 25, 2015 at 9:35 am

    #308200

    I created a trigger (ON ALL Server) to capture the DDL commands and write to a table, conceded grant to insert the logins that have in the instance.

    However I have an application that login of the database by Active Directory, these User has no privilege to insert record in the table, and the trigger fails.

    Does anyone have any idea how to solve?

  • September 25, 2015 at 10:04 am

    #1829560

    if your DDL trigger is writing to a table, you want to either have the trigger EXECUTE AS OWNER, or maybe GRANT INSERT ON MyAuditTable TO PUBLIC, so that no matter who does something, it can save the data you want to capture.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 25, 2015 at 10:09 am

    #1829563

    I did "GRANT INSERT on MyauditTable to PUBLIC", but did not work.

  • September 25, 2015 at 10:10 am

    #1829564

    IF EXECUTE AS OWNER, I don`t get SYSTEM_USER.

  • September 25, 2015 at 11:49 am

    #1829596

    i believe the function ORIGINAL_LOGIN() will return the caller, regardless of execute as.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 25, 2015 at 11:50 am

    #1829598

    fabio.lopes (9/25/2015)

    I did "GRANT INSERT on MyauditTable to PUBLIC", but did not work.

    "did not work" doesn't provide enough info.

    it depends on what your trigger is doing. if it is doing any objectother than INSERT into MyauditTable (meaning select or join will fail with that explcit permission too), that would stop the trigger as well.

    what is the specific error you get?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 25, 2015 at 11:55 am

    #1829601

    worked with ORIGINAL_LOGIN().

    Thanks friend.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply