Backup / Restore user with minimum permissions

  • I would like to create a user (SQL Auth. or Windows Auth., doesn't matter) which is only able to backup and restore one or more (but not all) database(s) using SSMS. The user should not be able to do anything else on the SQL server, it should have a minimum set of permissions. Basically i want to create a sort of "backup operator" user which can backup/restore specific databases on the SQL server without being able to do anything else. So far i only found "solutions" for SQL server 2008 or 2005, but unfortunately those "solutions" don't work with SQL server 2012 anymore, apparently because of changes to the permissions/server roles in SQL 2012.

    The main problem is, that to execute the RESTORE command, the user must have either sysadmin or dbcreator server roles or be the owner (dbo) of the database (the database role db_owner is not enough, it doesn't include the RESTORE permission). I don't want the user to have any of those three permissions/server roles because these permission allow the user to do way more than just backup/restore. sysadmin is out of the question (you can do practically anything with that permission), dbcreator is out of the question (as you can create, alter and drop ANY database with that permission) and the owner of the database should be sa and nobody else.

    Apparently it is not possible to specifically give a user the RESTORE permission, because there simply is no such permission. The RESTORE permission is only "part" of sysadmin, dbcreator or the dbo and can't be "assigned" to any other server/database role or user.

    Has anybody implemented such a "back operator user" with SQL 2012 before? Right now i'm completely stuck in implementing this, and i see no way of doing this with SQL 2012.

  • I think the only way is to make the user the owner of the databases in question. From the documentation:

    If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

    RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

    John

  • One method I've seen people use to get around this is to create a SQL Agent job that runs the RESTORE. Then, you give people permission to execute the job, but it runs under a different context than the user executing it, so you're OK on security.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

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