none
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?

     

    Wednesday, September 07, 2011 7:22 PM

All replies

  • 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
    Wednesday, September 07, 2011 7:48 PM
  • 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)

    Wednesday, September 07, 2011 8:13 PM
  • Unfortunately, you'd need CREATE DATABASE permsiion, which is at the server-level, not the database-level.
    Tom
    SQL Server MVP
    Toronto, ON Canada
    Thursday, September 08, 2011 2:10 AM
  • 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
    Thursday, September 08, 2011 6:11 AM
  • 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/
    Thursday, September 08, 2011 7:09 AM
  • 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
    Thursday, September 08, 2011 3:18 PM
  • 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 

     

    Thursday, September 08, 2011 7:13 PM
  • 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

    Friday, September 09, 2011 4:17 AM
  • How about a t-sql script that will grant restore database and the log to one database. 
    • Edited by stephenbp66 Friday, September 09, 2011 5:59 PM
    Friday, September 09, 2011 5:59 PM