Trigger permission

Using SQL SERVER 2016, Is it possible to grant Alter permission to one user and deny alter permission to any other users?

I'm creating an audit trigger while logged in as master user (AWS RDS) and what to prevent anyone else to ALTER / DISABLE the trigger
thank you
Paulo LeitaoGlobal Hardware ManagerAsked:
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
There's no ALTER permission on Triggers. Only way to an user be able to alter a trigger is to have ALTER permission on the Table.
With that said, do you still have the same requirement?
0
Paulo LeitaoGlobal Hardware ManagerAuthor Commented:
The need is the same, I need to assure than only the master user is allowed to make changes to the trigger.
How can I assure that no user has access to alter the table?
Thanks
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Is it possible to grant Alter permission to one user and deny alter permission to any other users?

As mentioned by Vitor, there is no ALTER TRIGGER permission in SQL Server.
However, if you want one user to have ALTER rights on the trigger and no one else to have ALTER rights, then you need to create a DDL trigger scoped either on your Database or Server for ALTER_TRIGGER event, check the username which tries to ALTER your TRIGGER and report/log/rollback it based upon your requirements. This way you can audit logs for anyone who tries to change your TRIGGER.
Sample code to ROLLBACK below.. If you want to log the entries, then log it by replacing the ROLLBACK statement.
CREATE TRIGGER ALTERTrigger
    ON DATABASE
    FOR ALTER_TRIGGER 
AS
BEGIN
IF ORIGINAL_LOGIN() <> 'ur_authorized_login_name'
ROLLBACK

END

Open in new window

1

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Vitor MontalvãoMSSQL Senior EngineerCommented:
Paulo, when you're creating a new user in a database you need to provide the role where to add the user. So when you're adding an user take in consideration one of the fixed database roles presented in this MSDN article.

NOTE: Mind that any the sysadmin Server Role overrules any database level that you set to the user.
0
Paulo LeitaoGlobal Hardware ManagerAuthor Commented:
Thank you both, I'm running without time today, but tomorrow will give it a try.

Raja, your solution looks promising, I was not thinking to create a trigger to prevent editing a trigger, but sound like a good solution.
Will let you know If solved my problem
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I was not thinking to create a trigger to prevent editing a trigger, but sound like a good solution.

Just thought about it, since we are creating a DDL trigger for ALTER_TRIGGER event, this will be applicable for all Triggers and not only for your trigger. if you want to set only for your trigger, then you might need to capture the object name and check in addition to the Login Info check as I have shown above..
0
Paulo LeitaoGlobal Hardware ManagerAuthor Commented:
Raja,

Works like a charme, capturing the name of the object I was able to rollback any unwanted changes.
Thank you
0
Scott PletcherSenior DBACommented:
Be aware you should also prevent them from DROPping the trigger.
1
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Thanks, Paulo, glad to help..
0
Paulo LeitaoGlobal Hardware ManagerAuthor Commented:
Thank you Scott, I did revoke rights to disable to mortals and create a trigger preventing any changes or drop to triggers started with "audit_"
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.