Post reply

SQL server Trigger for Granting server role

  • April 26, 2012 at 8:08 am

    #143306

    Hi All,

    I am very new to the topic "Triggers" in SQL server.

    I am working on creating a trigger with the below specifications

    1. no one other than DBA team member should not create/delete or grant/deny the server roles to a login.

    2. If a non DBA tries to perform these activity, a error message should be displayed and rolled back. Also a mail has to be sent to the DBA team with the information

    I have created the script for only create and delete logins with the below options

    "CREATE TRIGGER [TR_DDL_AUDIT_LOGIN]

    ON ALL SERVER

    FOR CREATE_LOGIN, DROP_LOGIN"

    however I am not aware of the option for grant/deny the server roles to a login

    can any please guide what is the option i have to put to include for grant/deny the server roles with this trigger.

    Thanks in Advance 🙂

    Shree Varsha

  • April 26, 2012 at 8:26 am

    #1479778

    you need to add in the ADD_SERVER_ROLE_MEMBER event to capture anything adding to a fixed server role

    this URL might help in the future on finding the correct events to capture http://msdn.microsoft.com/en-us/library/bb510452%28v=sql.100%29.aspx

  • April 26, 2012 at 8:35 am

    #1479792

    Thanks for the very quick responce.

    I tried giving ADD_SERVER_ROLE_MEMBER, but i got the below error.

    Msg 1082, Level 15, State 1, Procedure TR_DDL_AUDIT_LOGIN, Line 56

    "ADD_SERVER_ROLE_MEMBER" does not support synchronous trigger registration. 🙁

  • April 26, 2012 at 8:43 am

    #1479799

    CREATE TRIGGER NameOfTrigger ON ALL SERVER

    FOR ADD_SERVER_ROLE_MEMBER

    AS

    IF SUSER_SNAME() NOT IN ('sa')

    BEGIN

    PRINT 'Only DBA''s can modify this server and it''s logins'

    PRINT 'You''r statement has been logged and emailed to the DBA team'

    ROLLBACK TRANSACTION

    EXEC MSDB.dbo.sp_send_dbmail ..............................

    END

    just need to fill in your db mail call with your parameters and put in the right accounts in the SUSER_SNAME() call

  • April 27, 2012 at 12:26 am

    #1480199

    Hi,

    I tried creating the tigger with this code.

    I got the below error

    Msg 1082, Level 15, State 1, Procedure NameOfTrigger, Line 9

    "ADD_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.

  • April 30, 2012 at 5:10 am

    #1481130

    it seems that this is one of the things in 2005 you cannot create a DDL trigger for.

    take a look at this topic, it details using extended events and service broker to do the same thing.

    http://www.sqlservercentral.com/Forums/Topic245308-149-2.aspx

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

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