SQL Server应用角色赋VIEW SERVER STATE权限的替代方案及ct version时间获取

我来帮你梳理下这个问题的解决方案，针对应用角色无法直接获取VIEW SERVER STATE权限的问题，以及替代的ct version时间确定方法，结合SQL Server 2014及更高版本的特性，给你几个可行的方案：

一、为应用角色获取sys.dm_tran_commit_table访问权限的替代方案 #

首先得明确：你遇到的GRANT VIEW SERVER STATE to [ApplicationRole]失败，是因为应用角色是数据库级主体，而VIEW SERVER STATE是服务器级权限，SQL Server不允许直接给数据库级主体授予服务器级权限。下面是两种靠谱的替代方案：

1. 使用证书签名的存储过程（推荐） #

这个方案不需要修改应用程序的连接逻辑，通过证书给存储过程赋予服务器级权限，应用角色只需拥有存储过程的执行权限即可。具体步骤如下：

-- 1. 在目标数据库中创建证书（记得替换密码和过期时间）
CREATE CERTIFICATE Cert_ViewCommitTable
ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!'
WITH SUBJECT = 'Certificate for accessing sys.dm_tran_commit_table',
EXPIRY_DATE = '2030-12-31';

-- 2. 备份证书到本地文件（路径需SQL Server服务账户有权限）
BACKUP CERTIFICATE Cert_ViewCommitTable
TO FILE = 'C:\SQL_Certificates\Cert_ViewCommitTable.cer';

-- 3. 在服务器级别创建关联证书的登录名
CREATE LOGIN Login_ViewCommitTable
FROM CERTIFICATE Cert_ViewCommitTable;

-- 4. 给该登录名授予VIEW SERVER STATE权限
GRANT VIEW SERVER STATE TO Login_ViewCommitTable;

-- 5. 创建读取sys.dm_tran_commit_table的存储过程
CREATE PROCEDURE dbo.GetLatestCommitTableData
AS
BEGIN
    SET NOCOUNT ON;
    -- 可根据需求调整查询字段，避免返回全量数据
    SELECT commit_ts, commit_lsn, commit_time 
    FROM sys.dm_tran_commit_table
    ORDER BY commit_ts DESC;
END;

-- 6. 用证书给存储过程签名，让存储过程继承登录名的权限
ADD SIGNATURE TO dbo.GetLatestCommitTableData
BY CERTIFICATE Cert_ViewCommitTable
WITH PASSWORD = 'YourStrongPasswordHere!';

-- 7. 给应用角色授予存储过程的执行权限
GRANT EXECUTE ON dbo.GetLatestCommitTableData TO [ApplicationRole];

执行完这些步骤后，应用角色调用EXEC dbo.GetLatestCommitTableData就能获取到sys.dm_tran_commit_table的最新数据了，而且权限控制非常安全——应用角色只能通过这个存储过程访问视图，不能直接执行其他服务器级操作。

2. 改用服务器级登录名激活应用角色 #

如果业务允许调整连接逻辑，可以创建一个拥有VIEW SERVER STATE权限的服务器登录名，让应用程序先用这个登录名连接数据库，再激活应用角色。这样连接上下文同时拥有服务器级权限和应用角色的数据库权限：

-- 创建服务器登录名（替换密码）
CREATE LOGIN Login_AppServerAccess WITH PASSWORD = 'SecurePass123!';
GRANT VIEW SERVER STATE TO Login_AppServerAccess;

-- 在目标数据库中创建对应用户
CREATE USER User_AppServerAccess FOR LOGIN Login_AppServerAccess;

-- 给用户授予激活应用角色的权限
GRANT ACTIVATE APPLICATION ROLE TO User_AppServerAccess;

应用程序连接时，先用Login_AppServerAccess登录，再执行sp_setapprole激活应用角色：

EXEC sp_setapprole @rolename = 'ApplicationRole', @password = 'AppRolePassword';

之后就能直接查询sys.dm_tran_commit_table获取最新数据了。

二、确定ct version创建时间的其他方法 #

如果不想依赖sys.dm_tran_commit_table，还有这些替代方式：

1. 自定义版本时间戳表（最直接） #

在业务流程中同步记录变更跟踪版本和对应的时间戳，比如创建一个专用表：

CREATE TABLE dbo.CT_Version_Timestamps (
    ct_version BIGINT PRIMARY KEY,
    create_time DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    -- 可选：添加变更来源等额外信息
    source_table NVARCHAR(128) NULL
);

然后通过触发器、ETL任务或者业务代码，在生成新的变更跟踪版本时自动插入这条记录。之后只需查询这个表，就能直接获取ct version的创建时间，完全不需要访问服务器级视图。

2. 利用LSN映射时间函数 #

变更跟踪的systrans_id对应sys.dm_tran_commit_table中的commit_ts，而commit_lsn可以通过sys.fn_cdc_map_lsn_to_time函数转换为时间。不过这个方法还是需要访问sys.dm_tran_commit_table，适合已经解决权限问题的场景：

DECLARE @target_ct_version BIGINT = 12345; -- 替换为你的目标版本号

SELECT 
    sys.fn_cdc_map_lsn_to_time(commit_lsn) AS ct_version_create_time
FROM sys.dm_tran_commit_table
WHERE commit_ts = @target_ct_version;

3. 关联事务快照视图 #

sys.dm_tran_transactions_snapshot视图包含事务快照的创建时间，如果你能将变更跟踪版本关联到对应的事务快照，也能获取时间范围。不过这个方法关联逻辑较复杂，不如前两种实用。

内容的提问来源于stack exchange，提问作者user20130987