Post reply

Want to add a DDL trigger when a member is added to a server role

  • March 18, 2010 at 10:40 am

    #142075

    Hi,

    CREATE TRIGGER Trigger_ServerAddServerRoleMember

    ON ALL SERVER FOR ADD_SERVER_ROLE_MEMBER

    AS

    BEGIN

    DECLARE @data XML

    SET @data = EVENTDATA()

    INSERT INTO dbo.ServerRoleMember--A table I created

    SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')

    , @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

    , @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)')

    , @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)')

    , @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)')

    , @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')

    , @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)')

    , @data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(85)')

    , @data.value('(/EVENT_INSTANCE/RoleSID)[1]', 'nvarchar(85)'

    , @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

    END

    GO

    I have the following message when creating the trigger on SQL Server 2005 (in SQL Server 2008 it can be created)

    Msg 1082, Level 15, State 1, Procedure Trigger_ServerAddServerRoleMember, Line 19

    "ADD_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.

    Any help.

    I think it does not work in SQL Server 2005 but I am not sure.

    Thanks

  • March 19, 2010 at 12:48 pm

    #1136980

    u r right.

    This event type or event group can be specified when you create Event Notifications to monitor and respond to activity in the database or server instance.

    http://msdn.microsoft.com/en-us/library/ms180599(SQL.90).aspx

    MJ

  • March 22, 2010 at 5:10 am

    #1137493

    Thanks a lot MANU-J

Viewing 3 posts - 1 through 2 (of 2 total)

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