I have created a trigger on one of our SQL server 2012 (SP3) servers. The trigger captures when a stored procedure is created, altered or deleted. We need this due to an illegal code change which no one has admitted to.
In testing this worked without issue but when I moved it to the live server our configuration team doing deployments started receiving the below error:
Msg 297, Level 16, State 1, Procedure DML_Capture_Stored_Procedure_Changes, Line 30 - (This is the name of my trigger.)
The user does not have permission to perform this action.
Unfortunately on our test server our development team have allowed SA for all the development and configuration team whereas in live they have locked down permissions which mean's my testing has been successful when it has not.
The trigger is at database level and not table level. What permissions do I need to allow the configuration team to deploy and modify code to the production server? As I said bad practice has been used in dev as SA has been given out which I avoid for anyone who is not a DBA. I gave our configuration team DDLAdmin as I have the same trigger running on another test and live server without issue but they are still receiving this error.