MS SQL server backup and security admin role


I want to setup MS SQL account for backup only , run sp_who2  , what SQL serve role should it be ?

how about for security audit only , what SQL server role should it have?

I am currently using SQL 2016 enterprise edition.

marrowyungSenior Technical architecture (Data)Asked:
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.

Nitin SontakkeDeveloperCommented:
There is a db_backupoperator role. Have a look at the following for the further details.
marrowyungSenior Technical architecture (Data)Author Commented:
how about security admin right ?

by your link , it seems we have to grant DB restore to a use by script only but not by  UI  ?

how about DB restore ?
Nitin SontakkeDeveloperCommented:
The link provided earlier also shows UI method. Further down, 'Restore rights' is also discussed.

Didn't notice that there are two distinct questions. There is also a db_securityadmin role. See following for complete overview.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

marrowyungSenior Technical architecture (Data)Author Commented:
"The link provided earlier also shows UI method. Further down, 'Restore rights' is also discussed."

what I also mean is, it seems that restore role can only configure by granting create database by script, not UI.

it said If the database already exists, the user can perform the restore if he is a member of the dbcreator fixed server role, or is the database owner.

or I grant them by dbcreator role only ?
Nitin SontakkeDeveloperCommented:
Got it.

dbcreator sounds suitable, however, please be aware that it also has drop privileges.

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
marrowyungSenior Technical architecture (Data)Author Commented:
"dbcreator sounds suitable, however, please be aware that it also has drop privileges."

yes. sure. just like when restoring, it has to drop existing DB first and then write on top of it.

make sense, right?
Nitin SontakkeDeveloperCommented:
Yes. However, I just wanted to point out the negative aspect of it too.

It's like a knife. Should I give it to Mr X to cut the vegetables? The answer is, yes. While I should be aware of the fact that Mr X might land up killing someone.

Bottom line, it is all left to your "informed" decision and discretion.

Hope that helps!
marrowyungSenior Technical architecture (Data)Author Commented:
yes !
 but I don't want to make it complex. ahhahah
marrowyungSenior Technical architecture (Data)Author Commented:
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
Microsoft SQL Server

From novice to tech pro — start learning today.