Permission Error when enable DDL Trigger (Database Level)

  • 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

  • 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’! **
  • 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.

  • 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’! **
  • Thank you

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

  • 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
  • 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 ?

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

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

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