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.
Last Comment
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
http://www.sqlbackuprestore.com/backupandrestorerights.htm