Solved

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

Posted on 2014-07-29
9
572 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
push and Pull replication 31 47
query analyser in sql server 2016 express edition 2 25
T-SQL to copy a database 37 66
How can I retrieve the column names from a T-SQL table? 2 16
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
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

756 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