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.
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.
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 ?
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
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
ASKER
"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 ?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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. 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!
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!
ASKER
yes !
but I don't want to make it complex. ahhahah
but I don't want to make it complex. ahhahah
ASKER
tks.
http://www.sqlbackuprestore.com/backupandrestorerights.htm