SQL permissions issue since trigger deployment
Hi,
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_Procedu
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.
Many Thanks
Jordan
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_Procedu
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.
Many Thanks
Jordan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Using WITH EXECUTE AS 'My Login' resolved this. You can use EXECUTE AS OWNER on table level triggers but not database (ON ALL SERVER) level triggers.
Could be a few different things....
1) assuming you are capturing data / events and writing to a log of some description.
2) assuming you are capturing some Dynamic Management View information to identify the connection (standard approach using the various examples around the internet. Usually suspect is sys.dm_exec_connections )
3) assuming the config group have same role and dont have to be individually identified....
so..
grant VIEW SERVER STATE to config_group_members_or_ro
Make sure they can read / write to the DB and Table where log files live.
1) assuming you are capturing data / events and writing to a log of some description.
2) assuming you are capturing some Dynamic Management View information to identify the connection (standard approach using the various examples around the internet. Usually suspect is sys.dm_exec_connections )
3) assuming the config group have same role and dont have to be individually identified....
so..
grant VIEW SERVER STATE to config_group_members_or_ro
Make sure they can read / write to the DB and Table where log files live.
ASKER
I had tried that but it told me I could not use the OWNER. I have now made it EXECUTE as another account. I should have spotted that earlier as EXECUTE AS OWNER only works at table trigger level. My brain got crossed wires so many thanks for unscrambling it for me.
Many thanks
Jordan