Sql-server – Is it possible to deny RESTORE permissions to login with db_owner role membership

permissionsrestoresql serversql-server-2008-r2

Is it possible to deny RESTORE permissions to a login with db_owner role membership?

Is dbcreator required to RESTORE over an existing DB with a BACKUP of that DB? Or is that only needed when creating a new DB from a BACKUP?

I'm trying to prevent a user with db_owner membership from accidentally restoring to the wrong server/instance.

Best Answer

The Microsoft RESTORE statement Docs say this about database permissions:

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.

I'd suggest providing the person who requires db_owner level permissions be a separate login from actual database owner. Membership in the db_owner database role prevents that user from performing a restore.

Logins can be prevented from performing RESTORE DATABASE by denying them CREATE ANY DATABASE permission.

As a test, I did this on my SQL Server 2008 R2 instance.

Create a test login:

USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.server_principals sp WHERE sp.name = 'Test_db_owner')
BEGIN
    CREATE LOGIN Test_db_owner WITH PASSWORD = 'awsdaqwer0987)(FA&897afda2345';
END
GO

Create a test database:

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'Test_db_owner')
BEGIN
    ALTER DATABASE Test_db_owner SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE Test_db_owner;
END
CREATE DATABASE Test_db_owner;
GO

Create a user in the test database for the login above, and add the user to the db_owner database role:

USE Test_db_owner;
CREATE USER [Test_db_owner] FOR LOGIN [Test_db_owner];
EXEC sys.sp_addrolemember @rolename = 'db_owner', @membername = 'Test_db_owner';
GO

Create a backup of the database:

USE master;
BACKUP DATABASE Test_db_owner TO DISK = 'D:\SQLServer\MV\Backup\Test_db_owner.bak' WITH INIT;

Test the restore statement as the login:

EXECUTE AS LOGIN = 'Test_db_owner';
RESTORE DATABASE Test_db_owner FROM DISK = 'D:\SQLServer\MV\Backup\Test_db_owner.bak' 
    WITH REPLACE, RECOVERY;
REVERT;

Output:

Msg 3110, Level 14, State 1, Line 25
User does not have permission to RESTORE database 'Test_db_owner'.
Msg 3013, Level 16, State 1, Line 25
RESTORE DATABASE is terminating abnormally.

Cleanup:

DROP LOGIN Test_db_owner;
DROP DATABASE Test_db_owner;

FYI, the database owner is changed like this:

ALTER AUTHORIZATION ON DATABASE::[xyz] TO [login_name];

(or with sys.sp_changedbowner, which is deprecated)

Membership in the db_owner role is granted like this:

ALTER ROLE [db_owner] ADD MEMBER [login_name];

Although both statements seem to affect the "database owner", that is not the case; only the ALTER AUTHORIZATION statement can modify which login owns the database. See this excellent answer by Remus Rusanu.

If you want to allow the login to restore databases in the non-production environment, make them a member of the dbcreator server role:

EXEC sys.sp_addsrvrolemember @loginame = 'Test_db_owner'
    , @rolename = 'dbcreator';
Related Question