Restore database permissions

Introduction:

I came across a couple of subtle gotchas around the permissions required to restore a database in SQL Server 2008+ recently. I’m interested in situations where a DBA needs to allow certain users the ability to restore a given database, for example refreshing UAT, while maintaining minimum permission levels and following accepted best practice.

BOL documentation states that:

“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.”

“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.”

Now the principal of least privilege means I’m not really interested in members of the sysadmin server role.

Also, according to generally accepted practice our database should be owned by the sa account. (Interestingly, if a DBA chooses not to have databases owned by the sa account, they face further restrictions. SQL Server will not allow a database to be owned by a login created from a Windows group, leaving individual windows accounts or SQL Server accounts.)

So I’m interested in how dbcreator server role membership, db_owner database role membership and create database permission in the master database relate to the following three restore scenarios: restore into an existing database, restore into an existing database with replace, and restore to a new database.

Setup:

To test I’ll create a database and take a backup, and then create a SQL Server login.

USE [master]
GO
CREATE DATABASE TestRestore
GO
ALTER AUTHORIZATION ON DATABASE::TestRestore TO sa
GO
BACKUP DATABASE TestRestore 
TO DISK = N'<backup path>\TestRestore.bak'
GO
CREATE LOGIN [OwnerRoleMember] 
WITH PASSWORD = '123', CHECK_POLICY = OFF
GO

Now I’ll gradually elevate permissions and see what happens with the restores, starting with.

db_owner database role membership:

Map the user and add to the db_owner role.

USE TestRestore
GO
CREATE USER [OwnerRoleMember] FROM LOGIN [OwnerRoleMember]
EXEC sp_addrolemember 'db_owner','OwnerRoleMember'

Impersonate the login/user we created:

EXECUTE AS LOGIN = 'OwnerRoleMember'

SELECT suser_sname()

And try some restores:

USE master
GO
RESTORE HEADERONLY 
FROM DISK = N'<backup path>\TestRestore.bak'
GO
RESTORE FILELISTONLY 
FROM DISK = N'<backup path>\TestRestore.bak'
GO
RESTORE DATABASE TestRestore 
FROM DISK = N'<backup path>\TestRestore.bak'
GO
RESTORE DATABASE TestRestore 
FROM DISK = N'<backup path>\TestRestore.bak' 
WITH REPLACE

All four restores error. If we drop the existing database and rerun the restores they will not surprisingly error again. If you didn’t delete the database and try the restores then you need to clean up and remove the db_owner membership.

REVERT --exit the security context from earlier
GO
USE TestRestore
GO
EXEC sp_droprolemember 'db_owner','OwnerRoleMember'

Create database permission in the master database:

Recreate the database if you dropped it during the last tests, map the login into the master database and grant the permission.

USE master
GO
CREATE USER [OwnerRoleMember] FROM LOGIN [OwnerRoleMember]
GO
GRANT CREATE DATABASE TO [OwnerRoleMember]

Have a look at the server scoped and database (in the master database) scoped permissions

EXECUTE AS LOGIN = 'OwnerRoleMember'

SELECT suser_sname()

SELECT * FROM fn_my_permissions(null,'database')
SELECT * FROM fn_my_permissions(null,'server')

We can see that our login has view any database server scoped permission and create database permission in the master database. Now we stay in our impersonated security context and test the restores against an existing database.

USE master
GO
RESTORE HEADERONLY 
FROM DISK = N'<backup path>\TestRestore.bak'
RESTORE FILELISTONLY 
FROM DISK = N'<backup path>\TestRestore.bak'
RESTORE DATABASE TestRestore 
FROM DISK = N'<backup path>\TestRestore.bak'
RESTORE DATABASE TestRestore 
FROM DISK = N'<backup path>\TestRestore.bak' WITH REPLACE

The restore headeronly and filelistonly both succeed but the actual restores fail. Delete the database and rerun the restores (you’ll have to revert security contexts to be able to drop the database).

REVERT
GO
DROP DATABASE TestRestore 
GO
EXECUTE AS LOGIN = 'OwnerRoleMember'
GO
RESTORE DATABASE TestRestore 
FROM DISK = N'<backup path>\TestRestore.bak'
GO
REVERT
DROP DATABASE TestRestore 
GO
EXECUTE AS LOGIN = 'OwnerRoleMember'
GO
RESTORE DATABASE TestRestore 
FROM DISK = N'<backup path>\TestRestore.bak' 
WITH REPLACE

All restores succeed. So this level of permission will allow you to restore a new database, but not restore over an existing database.
To make sure we start with a clean slate I’m going to drop the user/login. (After the most recent restore the database will be owned by our login so we need to set that back to sa)

ALTER AUTHORIZATION ON DATABASE::TestRestore TO sa
GO
REVOKE CREATE DATABASE FROM [OwnerRoleMember]
GO
DROP USER [OwnerRoleMember]
GO
DROP LOGIN [OwnerRoleMember];

dbcreator server role:

Recreate the database and login and add it to the dbcreator server role. Check the permissions and notice that we have the same permissions as before with the addition of the server scoped create any database privilege.

CREATE LOGIN [OwnerRoleMember] 
WITH PASSWORD = '123', CHECK_POLICY = OFF
GO
EXEC sp_addsrvrolemember [OwnerRoleMember],'dbcreator'
GO
EXECUTE AS LOGIN = 'OwnerRoleMember'

SELECT suser_sname()

SELECT * FROM fn_my_permissions(null,'database')
SELECT * FROM fn_my_permissions(null,'server')

And run the restores again. This time all restores succeed.

Conclusion:

In order to be able to restore a backup onto SQL Server requires some sort of server scoped permissions. The create database permission in the master database will also grant the view any database server scoped permission. This will allow the principal to restore into a new database but not over an existing database. Add the create any database server scoped permission (which is granted by the dbcreator server role) and you can also restore over the top of an existing database.

About these ads
This entry was posted in Database options, Disaster Recovery, Security, SQL Server, T-SQL and tagged , , . Bookmark the permalink.

8 Responses to Restore database permissions

  1. Dan says:

    I ran into an issue trying to restore a backup taken from another server even though I was in the dbcreator role on the destination server. I could restore, but once restored the database was not accessible. After doing some experimentation I discovered that I needed to be added to the diskadmin role to resolve the issue, although I can’t say I understand why completely. So it seems the dbcreator role isn’t sufficient in all cases. Here’s the SO post I created on the issue:

    http://stackoverflow.com/questions/21145524/database-not-accessible-after-restore

    Would be curious if you have run into this, or know why this is. My guess is that it has something to do with the MDF / LDF files being in different locations on the source and destination servers.

  2. matt.bowler says:

    Hi Dan, That is a curious situation and I admit that I don’t really have a definitive answer for it. The DBCreator server permission is a server scoped role that will allow us to perform restore – but has nothing to do with the permissions of the restored database. The database will bring it’s permissions with it – and if it’s from a different domain then I assume your login may not be mapped to the database. It is a mystery why the diskadmin role solves your issue. Ultimately it is the SQL Server service account that is interacting with files in the file system….

    • Dan says:

      Thanks for the reply! You are correct about the logins not existing or being mapped, but the restore process makes the restorer the owner by default, which you would think would be sufficient.

  3. Dan says:

    An interesting wrinkle…

    So I did my experimentation on a test server, and that was how I arrived at diskadmin as the solution. However, being added to the diskadmin role on the server where the error was originally occurring has not resolved the issue. Ugh…

    Seems like a really common scenario – restoring a backup from one server to another, not sure why there isn’t more info out there on this issue.

  4. matt.bowler says:

    The owner of a database is something of a ceremonial role and does not automatically confer any permissions. There is a subtle difference between owning the database, owning the dbo schema and being a member of the db_owner role. If you restore a new database using a specific login then that login will “own” the database, but the owner of the dbo schema and any memberships of the db_owner role will be defined within the database and obviously come from the backup.

    • Dan says:

      Aren’t all the database level users and permissions useless when you restore from another server anyway? None of the users in the database are linked to logins on the destination server immediately following the restore, so it would seem the only way to access the database prior to db users getting linked to server logins is be virtue of server level permissions. The question is – *which* server level roles / permissions? In my case dbcreator is not doing the trick, and apparently diskadmin is not either, but it seems hard to believe this is a task reserved for sysadmins?

  5. matt.bowler says:

    Security principals are all identified by SIDs.

    Windows/AD principals will have the same SID throughout the domain so should map automatically (not sure how this works in trust relationships) but moving between domains will render these accounts defunct.

    SQL Server logins can be moved between servers with the help of something like sp_help_revlogin.

    http://support.microsoft.com/kb/246133

    And then remapped after a restore (because the SID will be different) using something like sp_change_users_login.

    http://technet.microsoft.com/en-us/library/ms174378.aspx

    Other alternative is to use a sysadmin account to restore the database and configure the security settings. Not really that strange to have to use an admin account to restore a new database from a different domain into your environment? Presumably not an every day activity? (Actually – thinking about this securityadmin should be enough to remap the necessary logins…)

  6. Dan says:

    Thanks for the additional info. I did some more experimentation, and it seems the fundamental issue is that the dbo user is not mapped to the login that was used to restore the database, even though the restore process makes that user the “owner.” Unfortunately, securityadmin does not seem to be sufficient to remap the login. Since the database is not accessible, there is no no way to alter any users, call sp_changedbowner, ALTER AUTHORIZATION, etc. My work around for now is to restore to an intermediary server in the domain where I am sysadmin, change the db owner (which maps the dbo user to my Windows login), and then restore again to the destination server, where I am not a sysadmin.

    Long story short – it seems the only way to access a database that has been restored from another domain is via a sysadmin login. It’s possible to do the restore without being a sysadmin, but the restored database is then inaccessible until a sysadmin creates / remaps users to logins in the domain.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s