Giving backup & Restore access to user having read,write permission

Question

Giving backup & Restore access to user having read,write permission

Abhijit More

SSCertifiable

Points: 6897
More actions
November 24, 2009 at 9:07 am

#139592

Hi All,
here is the situation i am in,
I have list of user who got the read write access to specific database.
Now my manager is asking me give them rights to BACKUP & RESTORE the specific database on the db server.
1. How can i give the db specific rights?
2. If I gave them dbcreator rights, they will get the access to perform the above activity, but this
will lead to access to other database which they dn't have.
3. GRANTing ALTER ANY daatabase, CREATE any database permission will lead the same situation.
Please let me know what will be the best solutions in this situation.
Abhijit - http://abhijitmore.wordpress.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Here's an article that summarizes the permissions needed to backup and restore: http://www.sqlbackuprestore.com/backupandrestorerights.htm. Unfortunately, there isn't a very restrictive role for restoring a backup. Hopefully, this isn't a production instance.

Greg

Lowell SSC Guru Points: 323518 More actions · Answer 2

we have a third party database on our server, and if an issue is found in thier application, they often need a backup of the database to diagnose.

I simply created a stored procedure with EXECUTE AS, and gave my normal users access to a link on our intranet website that calles the stored proc on demand, which goes thru the following steps:

backs up the database via TSQL.

zips it with a password(outside of SQL)

creates and sends an email (outside of SQL)

loads the backup to our FTP site.(outside of SQL)

you could basically do the same, create a procedure and give your end users permissions to the procedure, and have the proc use EXECUTE AS to do the backup.

because an end user could potentially step on critical data, i would never give them the ability to RESTORE, just do full backups.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Lowell (11/24/2009)
we have a third party database on our server, and if an issue is found in thier application, they often need a backup of the database to diagnose.
I simply created a stored procedure with EXECUTE AS, and gave my normal users access to a link on our intranet website that calles the stored proc on demand, which goes thru the following steps:
backs up the database via TSQL.
zips it with a password(outside of SQL)
creates and sends an email (outside of SQL)
loads the backup to our FTP site.(outside of SQL)
you could basically do the same, create a procedure and give your end users permissions to the procedure, and have the proc use EXECUTE AS to do the backup.
because an end user could potentially step on critical data, i would never give them the ability to RESTORE, just do full backups.

This would be a very good solution (IMO) for this scenario. I might add an additional step - cleanup any backup files that are x days old (or via some other requirement).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I'm going to agree with Lowell and Jason on this one. Especially the part about not giving restore rights. If they restore over top of the existing database there could be, likely will be, data lost. Let them backup and if they need to restore give them another instance to restore to. Even if it is Express edition.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question