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.