我正在考虑从审计触发器存储某种形式的事务ID.该解决方案似乎是使用sys.dm_tran_current_transaction在本帖子
SQL Server Triggers – grouping by transactions.
但是,我不能使用这个,因为运行sql语句的用户帐户将不具有“VIEW SERVER STATE”权限并导致错误：
Msg 297,Level 16,State 1,Line 3 The user does not have permission to perform this action.
有人知道这个视图的替代方法,它将提供类似的事务ID或者在触发器上使用“WITH EXECUTE AS”的方法,以允许从该视图中进行选择.
解决方法
您可以使用代码签名解决几乎任何安全问题.最细微和精细的访问控制,只是有点难以理解.
在触发器上使用EXECUTE AS OWNER,创建证书,对触发器进行签名,删除私钥(以便其他任何人都可以使用它来再次签名),导出证书(仅限公钥),在master中导入证书,创建从证书派生的登录名,授予该登录的身份验证(为extend the database execute as impersonation),然后将视图服务器状态授予此登录.这是防弹,完全控制的特权控制.如果需要更改触发器,则必须重新执行签名过程(包括证书派生登录和授权).从安全性的角度来看,这是希望的(您正在签署触发器的特定变体),从操作的角度来看,是一个皮塔饼,但是可以管理.
create table t (i int);
create table audit (transaction_id int);
go
create trigger t_audit_trigger
on t
with execute as owner
after insert,update,delete
as
begin
set nocount on;
insert into audit (transaction_id)
select transaction_id from sys.dm_tran_current_transaction;
if (@@ROWCOUNT != 1)
raiserror(N'Failed to audit transaction',16,1);
end
go
create certificate t_audit_view_server
encryption by password = 'Password#123'
with subject = N't_audit_view_server',start_date = '08/10/2009';
go
add signature to t_audit_trigger
by certificate t_audit_view_server
with password = 'Password#123';
go
alter certificate t_audit_view_server
remove private key;
backup certificate t_audit_view_server
to file = 'c:\temp\t_audit_view_server.cer';
go
use master;
go
create certificate t_audit_view_server
from file = 'c:\temp\t_audit_view_server.cer';
go
create login t_audit_view_server_login
from certificate t_audit_view_server;
go
grant authenticate server to t_audit_view_server_login;
grant view server state to t_audit_view_server_login;
go
