Solved

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

Posted on 2014-07-29
9
577 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

627 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