Post reply

sql server permissions on triggers

  • February 4, 2013 at 8:32 pm

    #273786

    In a sql server 2008 r2 database I just added 3 triggers to a specific table. Can you tell me if the user and/or application that uses the triggers need to have specific permission levels for the triggers to run. If these permissions are required, can you tell me what permissions are required for the user and/or application to run an access the triggers?

  • February 4, 2013 at 11:11 pm

    #1584178

    Hi,

    Example: a user has permission to insert, but denied on delete for tableA.

    When user inserts a row into tableA > insert triggers fire.

    When user deletes a row from tableA > delete triggers don't fire.

    So the triggers fire depend on type of action (insert/update/delete) and what permissions (insert/update/delete) were granted for the user on the table.

    --------------------------------------------------------------------------------------
    Hai Ton
    My Db4Breakfast blog.

  • February 5, 2013 at 6:10 am

    #1584336

    When I was orginally using the code, I had full 'sa' permissions. Now when I using the other database, there are no special permissions that have been setup.

    Thus can you tell me what permissions are required for a user to have update, insert, and delete permissions? What role is required like sysadmin?

  • February 5, 2013 at 6:14 am

    #1584340

    Either db_datawriter role or UPDATE, INSERT and DELETE granted on the table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 5, 2013 at 6:50 pm

    #1584700

    THANKS!:-)

Viewing 5 posts - 1 through 4 (of 4 total)

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