SQLTeam.com | Weblogs | Forums

Security question

sql2008r2

#1

Hello, I have added the database trigger to our development box as follows:

USE TheWatcheddatabase;
go
CREATE TRIGGER [DDLTrigger_Sample]
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, RENAME
AS
BEGIN
----------------------------------------------------------------------------------------------------
-- From https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
----------------------------------------------------------------------------------------------------
-- DISABLE TRIGGER [DDLTrigger_Sample] ON DATABASE;
-- ENABLE TRIGGER [DDLTrigger_Sample] ON DATABASE;
----------------------------------------------------------------------------------------------------
    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 Mydatabase.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;
-- End Trig

GO

Which runs fine for me (sysadmin), however a developer cannot alter a stored procedure. Error:
Msg 297, Level 16, State 1, Procedure DDLTrigger_Sample, Line 18
The user does not have permission to perform this action.

If I disable the trigger developer one can then alter the stored procedure.

A second developer was able to alter procedures. I notice that the second developer has db_ddladmin permission on the target database (Mydatabase) where as the first does not. Both have db_datareader and db_datawriter.

So my question is what is the minimum permissions needed to execute this?


#2

It's very common to add an EXECUTE AS clause to such triggers so that they run in a high-level security context regardless of the user performing the action. Typically the actions themselves are controlled by other permissions.


#3

Thank you. I will work on that.
I have trouble with EXECUTE AS and have tried to use it before with out a lot of success.
After reading your email I tried without success. I even used EXECUTE AS 'djj55' and then I got:
Msg 297, Level 16, State 1, Procedure DDLTrigger_Sample, Line 19
The user does not have permission to perform this action.
and djj55 has sysadmin permissions.
Here is what I tried

CREATE TRIGGER [DDLTrigger_Sample]
    ON DATABASE
    WITH EXECUTE AS 'Domain\djj55'  -- names changed to confuse the issue
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, RENAME
AS
...

#4

give required permissions to the user.
With this:
GRANT VIEW SERVER STATE TO user_name


#5

Thanks @jason_clark

Got rid of the execute as and did the grant, now working.

Thanks @ScottPletcher, but I could not get that to work. Not saying it would not but I could not. :slight_smile: