DDL trigger permission Error

Question

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

Answer 1

Most likely the problem is that testuser does not have the VIEW SERVER STATE server-level permission. That permission (assigned to the Login in the [master] database, not to the User in the database with the DDL Trigger) is required to access the sys.dm_exec_connections DMV. So you need to do one of the following:

1. GRANT the testuser Login the VIEW SERVER STATE server-level permission, or

2. Create a Certificate-based Login that is then assigned the VIEW SERVER STATE server-level permission, and then sign the DDL Trigger with that Certificate using ADD SIGNATURE. The steps for this are provided in the follower answer:

   What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

From a security stand-point, I would recommend doing the little bit of extra work to handle the Certificate-based Login and signing the Trigger. This approach doesn't grant any actual Login / User any extra permissions, and will apply to any User that has the proper DDL permissions to perform the actions that would cause the DDL Trigger to fire. Also, assuming that testuser is merely a test case and that these permissions will need to apply more broadly in the final system, then you probably don't want to grant VIEW SERVER STATE to all of these Users or even to a Role.

Along these same lines, I thought that Ownership Chaining would take care of the INSERT permission on DBChangesLogs such that the permission did not have to be explicitly granted to any User(s). If this is not the case, then rather than granting this permission to one or more Users -- or certainly better than adding any User to the db_datawriter Role that grants access (and more than just INSERT) to all tables -- you can simply do two additional, simple steps beyond what is already outlined in that other answer to grant this Trigger the ability to insert into the DBChangesLogs table:

1. (start with the steps outlined in the "What minimum permissions..." answer linked above, the last part of it being to create the Certificate in the database where the DDL Trigger exists)
2. Create a User from the Certificate-based Login created in those prior steps (or from this same Certificate that is now also in this database -- the User created will be the same in either case)
3. GRANT this User the INSERT permission on the DBChangesLogs table

CREATE USER [MrDoStuff] FROM LOGIN [MrDoStuff];
GRANT INSERT ON dbo.DBChangesLogs TO [MrDoStuff];

PS: you declare and set the @ip variable. I assume you will use it once this permission issue is settled?

Answer 2

Your SELECT statement is on sys.dm_exec_connections which will need VIEW SERVER STATE permissions.

So to grant the permissions, you can run:

USE MASTER
GO
GRANT VIEW SERVER STATE TO user2;
GO

Next thing is the INSERT statement in the trigger. The login user2 would need db_datawriter permissions on the DATABASE where you are creating the tables to write to the DBChangesLogs table.

You can use sp_addrolemember to grant read and write:

USE [DATABASE_NAME]
GO
EXEC sp_addrolemember N'db_datareader', N'user2'
GO
USE [DATABASE_NAME]
GO
EXEC sp_addrolemember N'db_datawriter', N'user2'
GO

Last thing, the code you are using to create the trigger is missing an 'AS' before the BEGIN.