• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 102
  • Last Modified:

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.
0
marrowyung
Asked:
marrowyung
  • 5
  • 4
1 Solution
 
Nitin SontakkeDeveloperCommented:
There is a db_backupoperator role. Have a look at the following for the further details.

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

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
0
How do you know if your security is working?

Protecting your business doesn’t have to mean sifting through endless alerts and notifications. With WatchGuard Total Security Suite, you can feel confident that your business is secure, meaning you can get back to the things that have been sitting on your to-do list.

 
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 ?
0
 
Nitin SontakkeDeveloperCommented:
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
0
 
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?
0
 
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!
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
yes !
 but I don't want to make it complex. ahhahah
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
0
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

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

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now