Solved

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

Posted on 2014-07-29
9
558 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now