DDL trigger permission Error

3 votes

2 answers

3199 views

sql-server sql-server-2008-r2 permissions trigger signature

                          I have a two users, user1 and user2.

User1 has sysadmin rights and I have created a database level trigger through sa.

Now I am trying to create a table through user2 who has db-ddladmin rights and db_datareader.

I'm getting the error:

     'Msg 297, Level 16, State 1, Procedure TR_CaptureDBChanges, Line 35
    The user does not have permission to perform this action.

Code:
 

    CREATE  TRIGGER [TR_CaptureDBChanges]
    ON DATABASE
    FOR  CREATE_PROCEDURE, ALTER_PROCEDURE,DROP_PROCEDURE,CREATE_TABLE, ALTER_TABLE,DROP_TABLE,CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,CREATE_VIEW,ALTER_VIEW,DROP_VIEW,CREATE_FUNCTION, ALTER_FUNCTION,
             DROP_FUNCTION
    BEGIN 
    DECLARE @ed XML 
    SET @ed = EVENTDATA() 
    DECLARE    @ip VARCHAR(32) =   (SELECT client_net_address FROM sys.dm_exec_connections  WHERE session_id = @@SPID);
    
        INSERT INTO DBChangesLogs ( EventDate,DBName)VALUES(GetDate(),@ed.value('(/EVENT_INSTANCE/DatabaseName)', 'varchar(256)'))
    END


                        

Asked by KD29 (53 rep)

Nov 9, 2015, 11:06 AM
Last activity: Nov 10, 2016, 08:34 PM

DDL trigger permission Error

Related Questions