由證書籤名的儲存過程無法將成員添加到數據庫角色

我創建了一個執行以下任務的儲存過程：

• 創建數據庫
• 將所有者更改為 sa
• 設置恢復模式
• 在數據庫中創建使用者
• 將使用者添加到數據庫角色。
• 授予使用者執行和查看定義

然後我創建了一個證書，從證書登錄，從證書使用者，在 SP 上授予使用者 exec，授予控制伺服器登錄，通過證書向 sp 添加簽名。

當非系統管理員執行 SP 時，除了將使用者添加到數據庫角色之外，一切正常，但出現錯誤：無法更改角色“db_datareader”，因為它不存在或您沒有權限。

如果我將登錄名添加到系統管理員伺服器角色，當非系統管理員執行 SP 時，它將起作用。理想情況下，登錄將具有執行這些功能的最小權限。是否可以在不授予使用者系統管理員權限的情況下執行此操作？

這是我用來創建證書、登錄名、使用者、簽名等的程式碼：

use master;
go

CREATE CERTIFICATE sp_CreateNewReportingDatabase_cert
ENCRYPTION BY PASSWORD = 'SuperSecretPassword'
WITH SUBJECT = 'Certificate for sp_CreateNewReportingDatabase',
   START_DATE = '20201015', EXPIRY_DATE = '21000101'
GO

CREATE LOGIN sp_CreateNewReportingDatabase_cert_user FROM CERTIFICATE sp_CreateNewReportingDatabase_cert;
GO
CREATE USER sp_CreateNewReportingDatabase_cert_user FROM CERTIFICATE sp_CreateNewReportingDatabase_cert;
GO
GRANT EXECUTE ON dbo.CreateNewReportingDatabase TO sp_CreateNewReportingDatabase_cert_user;
GO
GRANT CONTROL SERVER TO sp_CreateNewReportingDatabase_cert_user;
GO
ADD SIGNATURE TO [dbo].[CreateNewReportingDatabase] BY CERTIFICATE sp_CreateNewReportingDatabase_cert
WITH PASSWORD = 'SuperSecretPassword';
GO

下面是創建儲存過程的程式碼：

CREATE OR ALTER PROC dbo.CreateNewReportingDatabase @DatabaseName NVARCHAR(50), @ReportingPod NVARCHAR(50) = NULL
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @SqlCommand NVARCHAR(2000);

--Error Checking
IF @ReportingPod NOT LIKE 'TAC[_]NT\Analysis-%' AND (@DatabaseName LIKE 'Reporting[_]%' OR @DatabaseName LIKE '[_]Salesforce')
   BEGIN
       RAISERROR('For Reporting_<client> and <client>_Salesforce databases you must populate @ReportingPod with a value that follow formate TAC_NT\Analysis-<reportingpod> Contact DBA',16,1)
       SET NOEXEC ON --Stops running the script --I believe that this requires sysadmin rights.
   END
ELSE IF (@DatabaseName LIKE 'Reporting[_]%' OR @DatabaseName LIKE '[_]Salesforce') AND @ReportingPod IS NULL
   BEGIN
       RAISERROR('For Reporting_<client> and <client>_Salesforce databases you must populate @ReportingPod with a value that follow formate TAC_NT\Analysis-<reportingpod> Contact DBA',16,1)
       SET NOEXEC ON --Stops running the script --I believe that this requires sysadmin rights.        
   END
ELSE IF EXISTS (
   SELECT [name]
   FROM sys.databases
   WHERE [name] = @DatabaseName
   )
   BEGIN
       RAISERROR('There is already a detabase with that name!',16,1)
       SET NOEXEC ON --Stops running the script --I believe that this requires sysadmin rights.
   END
   
--Execute Scrips
ELSE
BEGIN
   --Create the database
   PRINT 'Creating database ' + @databasename;
   SET @SqlCommand = N'CREATE DATABASE [' + @DatabaseName + ']'    
   EXEC dbo.sp_executesql @SqlCommand

   -- change owner to sa
   PRINT 'Switching owner to sa';
   SET @SqlCommand = N'ALTER AUTHORIZATION ON DATABASE::[' + @DatabaseName + '] TO [sa];'
   EXEC dbo.sp_executesql @SqlCommand;
   
   -- set recovery model to simple
   IF @DatabaseName LIKE 'Reporting[_]%' OR @DatabaseName LIKE '%[_]Salesforce'
       BEGIN
           PRINT 'Setting recovery model to simple';
           SET @SqlCommand = N'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY SIMPLE;'
           EXEC dbo.sp_executesql @SqlCommand
       END
   ELSE PRINT 'Revoery model not modified from default';

   -- Create users for ServiceAccount and reportingPod in Reporting_ databases.
   IF @DatabaseName LIKE 'reporting[_]%' OR @DatabaseName LIKE '%[_]Salesforce'
       BEGIN
           SET @SqlCommand = N'
               USE [' + @DatabaseName + ']
               IF NOT EXISTS(
                   SELECT [name]
                   FROM sys.database_principals
                   WHERE
                       [type] NOT IN (''s'',''r'')
                       AND [NAME] = ''TAC_NT\ServiceAccount''
               )
               BEGIN
                   PRINT ''Creating [TAC_NT\ServiceAccount] user''
                   CREATE USER [TAC_NT\ServiceAccount] FOR LOGIN [TAC_NT\ServiceAccount]
               END         

               IF NOT EXISTS(
                   SELECT [name]
                   FROM sys.database_principals
                   WHERE
                       [type] NOT IN (''s'',''r'')
                       AND [NAME] = ''' + @ReportingPod + '''
               )
               BEGIN
                   PRINT ''Creating ' + @ReportingPod + ' user''
                   CREATE USER [' + @ReportingPod +  '] FOR LOGIN [' + @ReportingPod + '];
               END'
           EXEC sp_executesql @SqlCommand;
       END
   ELSE PRINT 'Skipping the creation of the ServiceAccount and Reporting POD database principals'

   ---Grant role membership for ServiceAccount
   IF @DatabaseName LIKE 'Reporting[_]%'
   BEGIN
       PRINT 'Adding !gampelan to roles: reader, writer, ddladmin';
       SET @SqlCommand = N'
       USE [' + @DatabaseName + ']
       ALTER ROLE db_datareader ADD MEMBER [TAC_NT\ServiceAccount]
       ALTER ROLE db_datawriter ADD MEMBER [TAC_NT\ServiceAccount]
       ALTER ROLE db_ddladmin ADD MEMBER [TAC_NT\ServiceAccount]
       '
       EXEC sp_executesql @SqlCommand
   END
   ELSE IF @DatabaseName  LIKE '%[_]salesforce'
   BEGIN
       PRINT 'Adding ServiceAccount to roles: reader';
       SET @SqlCommand = N'
       USE [' + @DatabaseName + ']
       ALTER ROLE db_datareader ADD MEMBER [TAC_NT\ServiceAccount]
       '
       EXEC sp_executesql @SqlCommand
   END


   --Grant role membership for ReportingPod
   IF @DatabaseName  LIKE 'Reporting[_]%' AND @ReportingPod LIKE 'TAC_NT\Analysis%'
   BEGIN
       PRINT 'Adding ' + @ReportingPod + ' to roles: reader';
       SET @SqlCommand = N'
       USE [' + @DatabaseName + ']
       ALTER ROLE db_datareader ADD MEMBER [' + @ReportingPod + ']'
       EXEC sp_executesql @SqlCommand
   END
   ELSE IF @DatabaseName  LIKE '%salesforce' AND @ReportingPod LIKE 'TAC_NT\Analysis%'
   BEGIN
       PRINT 'Adding ' + @ReportingPod + ' to roles: reader';
       SET @SqlCommand = N'
           USE [' + @DatabaseName + ']
           ALTER ROLE db_datareader ADD MEMBER [' + @ReportingPod + ']'    
       EXEC sp_executesql @SqlCommand
   END
   
   --Grant execute and view definition for ReportingPod
   IF @DatabaseName  LIKE '%[_]Salesforce' OR @DatabaseName  LIKE 'Reporting[_]%'
   BEGIN
       PRINT 'Granting execute and view definition to ' + @ReportingPod;   
       SET @SqlCommand = N'
       USE [' + @DatabaseName + ']
       GRANT EXECUTE TO [' + @ReportingPod + '];
       GRANT VIEW DEFINITION TO [' + @ReportingPod + '];'  
       EXEC dbo.sp_executesql @SqlCommand;
   END
END
GO

當非系統管理員執行 SP 時，除了將使用者添加到數據庫角色之外，一切正常，但出現錯誤：無法更改角色“db_datareader”，因為它不存在或您沒有權限。

根據ALTER ROLE文件：

此外，要更改固定數據庫角色的成員資格，您需要：

• db_owner 固定數據庫角色中的成員身份

創建數據庫後，執行 proc 的使用者最初是數據庫所有者（dbo 使用者是 db_owner 角色成員），但 proc 程式碼將所有者更改為 sa。從那時起，使用者不再具有更改固定數據庫角色的權限。

通常我會建議在最後一步更改數據庫授權，以便使用者擁有所有需要的權限。不過，這裡棘手的部分是 proc 還將呼叫者添加為數據庫使用者，這是有問題的，因為在更改數據庫所有者之前，使用者是 dbo。

鑑於證書使用者具有CONTROL SERVER權限，您可以EXECUTE AS LOGIN 'sa';在更改數據庫所有者後。這樣，呼叫者不需要 sysadmin 角色成員資格，並且僅限於儲存過程中的提升功能。

引用自：https://dba.stackexchange.com/questions/278158