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_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.

Many Thanks

Jordan
LVL 1
Jordan WebbDatabase Administrator and Infrastructure AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Add an "EXECUTE AS" clause to the trigger to run it as a separate id.  Then it won't matter who causes the trigger to run, because their id won't be used to run it anyway.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jordan WebbDatabase Administrator and Infrastructure AnalystAuthor Commented:
Hi Scott,

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
0
Jordan WebbDatabase Administrator and Infrastructure AnalystAuthor Commented:
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.
0
Mark WillsTopic AdvisorCommented:
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_role  

Make sure they can read / write to the DB and Table where log files live.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.