Solved

the right need to backup/restore /detach/attach database

Posted on 2014-07-29
9
570 Views
Last Modified: 2014-07-31
Deer all,

please correct me if I am wrong, in MS SQL, only the login with the sysadmin role in SQL server can do the backup/restore/attach/detach of database?
0
Comment
Question by:marrowyung
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 300 total points
ID: 40228389
db_backupoperator allows a user to backup a DB
db_owner of a particular db will allow for restore or dettach
db_creator will allow for attach

https://www.simple-talk.com/sql/database-administration/sql-server-security-cribsheet/
0
 
LVL 22

Assisted Solution

by:Om Prakash
Om Prakash earned 100 total points
ID: 40228391
I think users who are member of sysadmin or dbcreator can perform these activities
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 100 total points
ID: 40228392
There are 9 fixed server roles in SQL Server: http://msdn.microsoft.com/en-us/library/ms188659%28v=sql.110%29.aspx

According to the documentation, dbcreator can also restore a database.

Would suggest testing if it can also backup / detach / attach :)

But for the most part, it would appear you are correct.

As an aside, these are really only actions that a DBA should be doing anyway ... they aren't things I'd farm out to someone else....
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Expert Comment

by:QPR
ID: 40228398
I wouldn't say the poster was correct.
Giving SA permissions unnecessarily is a dangerous game. The server/DB roles are there for this very reason.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40228401
The OP asked if it was correct - apart from dbcreator which states it can restore but doesn't state the others, he is correct in his question.

As I mentioned (and you did too), it's not necessarily a WISE decision to hand out elevated permissions, but that isn't what he asked.
0
 
LVL 29

Expert Comment

by:QPR
ID: 40228406
The question was can only the SA perform these tasks. The answer is no. Therefore incorrect :)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40228601
what I mean is if it is only anyone with sysadmin role can do the DB backup and restore, is it?

all these has sysdamin role:
1)NT SERVICE\MSSQLSERVER
2) NT SERVICE\SQLSERVERAGENT

so I belive that if the sysadmin role removed from these account, SQL server is not going to operate, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40228612
but the one who can assign/add/remove role must be sysadmin, right?
0
 
LVL 29

Expert Comment

by:QPR
ID: 40229964
No. Please read the link that explains each role and what it does
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question