Creating a SQL Server login to backup and restore a specific database

Question

I would like to create a login on a SQL Server 2005 database server.  The server has multiple databases and the login should only have access to one database only (let's say database ABC).  On the database ABC, the login should have sysadmin privileges for database ABC only.  Specifically, the login should be able to backup and restore the database ABC and no other databases on the server.  How do I set the roles or schemas to do this?

 

Answer 1

When you create a login, it has no privileges in any database, except tempdb.  You have to add the login as a user to any DB to which you want them to have access.  At that point, they still have no privileges within the DB, except to connect to it.  If you then want to grant BACKUP and RESTORE permission, you can add the user to the db_backupoperator fixed DB role.

 

 

---

Tom
SQL Server MVP
Toronto, ON Canada

Answer 2

I created a login named test1 and opened the properties window.  Under Server roles, public is checked and under User Mapping, the database ABC is checked and the default schema is db_backupoperator.  Using the GUI, I successfully backed up the ABC database.  When attempting to restore the database ABC, I received the following error:

Restore failed for Server ''. (Microsft.SqlServer.Smo)

System.Data.SqlClient.SqlError: User does not have permission to RESTORE database 'ABC' .

(Microsft.SqlServer.Smo)

Answer 3

Unfortunately, you'd need CREATE DATABASE permsiion, which is at the server-level, not the database-level.

---

Tom
SQL Server MVP
Toronto, ON Canada

Answer 4

You Can Create Login With Providing dbowner On A Selected Database. User will be able to Do everything with that database.

but to restore database you need server role "dbcreater"

 

 

 

---

Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com

Answer 5

Tom 

Granting CREATE DATABASE perm could be dangerous

http://dimantdatabasesolutions.blogspot.com/2010/09/be-careful-to-grant-dbcreator-server.html

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 6

Or you can use the granular permissions. In the database you want to backup, grant the BACKUP DATABASE and BACKUP LOG permissions.

Restoring a database is creating something new, so in the master database grant the CREATE DATABASE permission. This won't grant the ability to drop databases.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 7

After some testing, I was able to get login 'test1' to backup and restore database 'ABC' with the following settings:

test1 Server roles: public and dbcreator.  test1 User Mappings: database ABC and default schema is db_owner.

Even though login 'test1' could not open other databases on the server, the unfortunate consequence was that login 'test1' was able to restore any other database on the server (as Uri pointed out).  

So, what is the best method of creating a login (test1) with the following restrictions:

1. Login 'test1' should be owner of database 'ABC' only and no other database on the server.

2. Login 'test1' should not have access to any other database on the server.

3. Login 'test1' should be able to backup (data and log files) and restore the database 'ABC' only.  Login 'test1' should not be permitted to backup or restore any other database on the server.

Will I need to create a custom t-sql script or can it be done using the GUI interface?

 

Stephen 

 

Answer 8

Hallo Stephen,

give a server wide trigger a try.
http://msdn.microsoft.com/en-us/library/ms189799.aspx

If a dedicated user will run a restore the trigger could prevent it.

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 9

How about a t-sql script that will grant restore database and the log to one database.