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?