SQL Server - 通過證書向儲存過程添加簽名不起作用

我有一個儲存過程，用於更新和Database 1中的幾個表Database 1``Database 2

為了避免授予呼叫登錄/使用者權限來進行表更新，我嘗試使用程式碼簽名

在master數據庫中創建證書，並從此證書創建登錄

然後我為這個證書映射登錄創建了使用者，在Database 1and中Database 2，並授予他們執行表更新的權限

當我嘗試在 T-SQL 下執行時：

use [Database 1]
add signature to MyProcedure
by certificate MyCertificate

它拋出：

消息 15151，級別 16，狀態 1，行 168 找不到證書“MyCertificate”，因為它不存在或您沒有權限。

然後我嘗試

use [master]
add signature to [Database1].[dbo].MyProcedure
by certificate MyCertificate

我是系統管理員伺服器角色，但它拋出：

消息 15151，級別 16，狀態 1，第 168 行無法更改對象“Database1.dbo.MyProcedure”，因為它不存在或您沒有權限。

問題：

如何通過數據庫Database 1中的證書正確地將簽名添加到我的儲存過程中master？

您需要將證書從 master 複製到您的使用者數據庫。例如：

-- Copy master certificate to user database
USE master;
DECLARE @cert_id int = cert_id('your_certificate')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
       @private_key varbinary(MAX) =
          certprivatekey(@cert_id,
             'your encryption password',
             'your decryption password')

--these values should not be NULL
SELECT @cert_id, @public_key, @private_key;

DECLARE @sql nvarchar(MAX) =
     'CREATE CERTIFICATE your_certificate
      FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
      WITH PRIVATE KEY (BINARY = ' +
         convert(varchar(MAX), @private_key, 1) + ',
         DECRYPTION BY PASSWORD = ''your encryption password'',
         ENCRYPTION BY PASSWORD = ''your decryption password'')'

EXEC YourUserDatabase.sys.sp_executesql @sql;

編輯

如果證書是使用數據庫主密鑰而不是密碼加密的，則省略解密密碼 from CERTPRIVATEKEY（使用源數據庫 DBMK 解密）並省略ENCRYPTION BY PASSWORD子句 of CREATE CERTIFICATE（使用目標數據庫 DBMK 加密）。在這種情況下，密碼僅用於加密傳輸中的私鑰。

-- Copy cert to user database
USE master;
DECLARE @cert_id int = cert_id('your_certificate')
DECLARE @public_key  varbinary(MAX) = CERTENCODED(@cert_id),
       @private_key varbinary(MAX) =
          CERTPRIVATEKEY(@cert_id,
             'your encryption password');

--these values should not be NULL
SELECT @cert_id, @public_key, @private_key;

--create certificate in target database encrypted with DBMK
DECLARE @sql nvarchar(MAX) =
     'CREATE CERTIFICATE your_certificate
      FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
      WITH PRIVATE KEY (BINARY = ' +
         convert(varchar(MAX), @private_key, 1)
       + ', DECRYPTION BY PASSWORD = ''your encryption password'');'
PRINT @sql

EXEC YourUserDatabase.sys.sp_executesql @sql;
GO

我將補充一點，僅針對跨數據庫權限，不需要從證書創建登錄名。在使用者數據庫中創建證書，根據需要將證書複製到其他數據庫，從每個數據庫中的證書創建使用者並授予證書使用者權限，最後用證書對proc進行簽名。

僅當必須授予伺服器級權限時才需要伺服器級主體。

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