允许存储过程使用 SQL Server 2012 中的证书从系统表中进行选择

Question

目前，我在尝试运行尝试从系统表中进行选择的存储过程时遇到与权限相关的问题。基本上，我试图通过直接从 sys.dm_tran_current_transaction 表获取我需要的数据来解决 SQL Server 2012 中 sp_bindtoken 系统存储过程的弃用问题。

我发现的一种解决方案是：

• 创建证书
• 从证书创建用户/登录
• 授予用户“查看服务器状态”权限
• 使用证书签署存储过程 执行
• 存储过程，该过程在用户的上下文中运行刚刚创建的

这种方法似乎在 SQL Server 2008 R2 中运行良好。然而，在 SQL Server 2012 中，即使脚本看起来运行正确，但在运行时，当尝试从上述系统表中进行选择时，存储过程会失败并出现权限错误。

我目前的脚本看起来有点像这样：

USE OurDatabase
GO

CREATE CERTIFICATE OurDatabaseProcCert
FROM FILE = 'C:\Path\To\OurDatabaseProcCert.cer'
WITH PRIVATE KEY (
FILE = 'C:\Path\To\OurDatabaseProcCert.pvk',
ENCRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd',
DECRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd');
GO

USE master
GO

CREATE CERTIFICATE OurDatabaseProcCert
    FROM FILE = 'C:\Path\To\OurDatabaseProcCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Path\To\OurDatabaseProcCert.pvk',
    ENCRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd',
    DECRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd');
GO

CREATE LOGIN OurDatabaseServerLogin
    FROM CERTIFICATE OurDatabaseProcCert
GO

CREATE USER OurDatabaseServerLogin

REVOKE CONNECT SQL FROM OurDatabaseServerLogin
GO 

GRANT AUTHENTICATE SERVER TO OurDatabaseServerLogin
GO

GRANT VIEW SERVER STATE TO OurDatabaseServerLogin
GO

USE OurDatabase
GO

ADD SIGNATURE TO dbo.bsp_getTransactionID BY CERTIFICATE OurDatabaseProcCert WITH PASSWORD = '$0m3$tr0ngp@$$w0rd'

这是创建我试图签署/执行的存储过程的代码：

CREATE Procedure bsp_getTransactionID
(
    @TransactionID  VARCHAR(255) OUTPUT
)
AS      
BEGIN
    IF @@TRANCOUNT > 0  
    BEGIN
        SELECT SYSTEM_USER
        SELECT @TransactionID = sys.dm_tran_current_transaction.transaction_id FROM sys.dm_tran_current_transaction
    END 
 RETURN 0
END
GO

以前有人遇到过此类问题吗？

Answer 1

虽然我不明白为什么它在 2012 年会有不同的反应，但我确实想知道您是否可以通过使用 CREATE PROCEDURE 中的 EXECUTE AS 子句来解决这个问题。

理论上，这应该与您的解决方案等效，因为默认值是EXECUTE AS Owner，但它的反应可能略有不同???我认为值得一试。