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)[1]', 'varchar(256)'))
END