MS SQL server backup and security admin role

marrowyung
marrowyung used Ask the Experts™
on
hi,

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.

tks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
There is a db_backupoperator role. Have a look at the following for the further details.

http://www.sqlbackuprestore.com/backupandrestorerights.htm
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 ?
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.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

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 ?
Got it.

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

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles
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?
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:
tks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial