DbCreator role can restore a database but not then access it in MSSQL Server

Question

I am working on a tool which is having restore command to restore MSSQL databases. Till now tool was restoring the databases with sysadmin privileges. However, as per new requirement I want to minimize the access privilege for this command i.e restore should be done with dbcreator role instead of sysadmin. With dbcreator I am able to restore the databases, however this command also does some post-operation on the restored databases i.e. updating some value in table. Post-operation is failing with lack of access, since db_owner is required for this user. How to grant db_owner to current user(dbcreator) on the restored databases who is not sysadmin at run time so that my restore command succeeds along with post-operation.

Answer 1

One way to follow security principles of least privilege and role-based access controls is to encapsulate the T-SQL commands that require elevated permissions in a stored procedure. One can then sign the proc with a certificate to confer additional permissions without granting those permissions directly to users. Only execute permissions on the signed stored procedure are required and authorized users are limited to the encapsulated functionality.

Below is an example script to create a stored procedure and DBRestore role using this technique. If your actual RESTORE command contains options that can't be parameterized (e.g. WITH MOVE file locations), you'll need to use dynamic SQL in the proc and take special care to ensure the values validated and/or obtained from a trusted source (e.g. configuration table instead of ad-hoc parameter value).

USE master
GO
--create certificate in master database
CREATE CERTIFICATE sysadmin_cert_login_cert
   ENCRYPTION BY PASSWORD = '<cert-password>'
   WITH SUBJECT = 'For sysadmin privileges';
GO
--create login from certificate
CREATE LOGIN sysadmin_cert_login FROM CERTIFICATE sysadmin_cert_login_cert;
--confer sysadmin permissions to certificate login
ALTER SERVER ROLE sysadmin
    ADD MEMBER sysadmin_cert_login;
GO
--create role for restore user(s)
CREATE ROLE DBRestoreRole;
GO

--create restore proc in master database
CREATE PROC dbo.usp_RestoreDatabase
      @DatabaseName sysname
    , @BackupFilePath varchar(255)
AS
BEGIN TRY
    RESTORE DATABASE @DatabaseName FROM DISK=@BackupFilePath WITH REPLACE;
    --after restore, set database owner as desired
    ALTER AUTHORIZATION ON DATABASE::RestoreTest TO sa;
    --execute post restore DML
    UPDATE RestoreTest.dbo.SomeTable
    SET SomeColumn = 1;
END TRY
BEGIN CATCH
    THROW;
END CATCH;
GO
--grant execute permission to DBRestoreRole
GRANT EXECUTE ON dbo.usp_RestoreDatabase TO DBRestoreRole;
--sign proc with sysadmin certificate
ADD SIGNATURE TO dbo.usp_RestoreDatabase BY CERTIFICATE sysadmin_cert_login_cert WITH PASSWORD='<cert-password>';
--optionally, remove ephemoral private key after signing
ALTER CERTIFICATE sysadmin_cert_login_cert REMOVE PRIVATE KEY;
GO

--create example DBRestoreRole login/user
CREATE LOGIN RestoreTestLogin WITH PASSWORD = '<login-password>';
CREATE USER RestoreTestLogin;
ALTER ROLE DBRestoreRole
    ADD MEMBER RestoreTestLogin;
GO

--test execution
EXECUTE AS LOGIN = 'RestoreTestLogin';
GO
EXEC dbo.usp_RestoreDatabase
      @DatabaseName = N'RestoreExample'
    , @BackupFilePath = 'E:\BackupFiles\RestoreExample.bak';
GO
REVERT;
GO