Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

MS SQL server backup and security admin role

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.
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

There is a db_backupoperator role. Have a look at the following for the further details.

http://www.sqlbackuprestore.com/backupandrestorerights.htm
Avatar of marrowyung
marrowyung

ASKER

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
"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 ?
ASKER CERTIFIED SOLUTION
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"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!
yes !
 but I don't want to make it complex. ahhahah
tks.