问DbCreator角色可以还原数据库，但不能在MSSQL中访问它

EN

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