Permission Error when enable DDL Trigger (Database Level)

Question

Permission Error when enable DDL Trigger (Database Level)

MotivateMan1394

Hall of Fame

Points: 3876
More actions
January 12, 2016 at 11:18 pm

#399558

Hi
I wrote a DDL Trigger .
I checked in with sysadmin user. I Worked.
But with a user on Specific Database generate an error . (about permission)
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Procedure DDLTrigger_LogAll_DDLChanges_ToDataBase, Line 20
The user does not have permission to perform this action.
what is the Problem ?
This is my DDL Trigger :
Alter TRIGGER [DDLTrigger_LogAll_DDLChanges_ToDataBase]
ON DATABASE
with execute as 'dbo'
FOR
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,ALTER_TABLE, DROP_TABLE, ALTER_INDEX, DROP_INDEX,CREATE_Function, ALTER_Function, DROP_Function,
ALTER_VIEW, DROP_VIEW,
ALTER_TRIGGER, DROP_TRIGGER
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@EventData XML = EVENTDATA();
DECLARE
@ip VARCHAR(32) =
(
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
);
INSERT DB_DBA.dbo.DDLEvents
(
EventType,
EventDDL,
EventXML,
DatabaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END

Viewing 8 posts - 1 through 7 (of 7 total)

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

The error mentions the missing permission: VIEW SERVER STATE

This required permission is on the instance level. Your trigger is executing as the 'dbo' and this has only permissions within the database. You could create a login and grant it permissions for VIEW SERVER STATE and make it member of the db_owner role of the database. Execute the trigger as this new login.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 2

Grant VIEW SERVER STATE to [NewLogin]

Secondly :

Create a user based on this login in my db .

then

ALTER ROLE [db_owner] ADD MEMBER [NewLogin]

GO

finally :

Create TRIGGER [DDLTrigger_LogAll_DDLChanges_ToDataBase]

ON DATABASE

WITH EXECUTE AS 'NewLogin'

FOR

CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,ALTER_TABLE, DROP_TABLE, ALTER_INDEX, DROP_INDEX,CREATE_Function, ALTER_Function, DROP_Function,

ALTER_VIEW, DROP_VIEW,

ALTER_TRIGGER, DROP_TRIGGER

AS

BEGIN

But when I login with our Global User (That is owner on mydb) , and try to create a stored procedure , I get this error :

Msg 15562, Level 16, State 1, Procedure DDLTrigger_LogAll_DDLChanges_ToDataBase, Line 18
The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

take a look at this link Tutorial: Signing Stored Procedures with a Certificate

edit: corrected the link

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 4

Thank you

But this link is refrenced to this page again !!!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Here it is, was easy enough to find from google with the title.

https://msdn.microsoft.com/en-us/library/bb283630.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 6

Here it is, was easy enough to find from google with the title.
https://msdn.microsoft.com/en-us/library/bb283630.aspx

But what is the relation between this and my abstacle?

The DDL trigger Dont work with a general user? what do i do ?

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 7

Check out this link hope this will help you out: https://support.microsoft.com/en-us/kb/2714785