You can resolve almost any security problem using code signing. Most granular and finely tuned access control, is just a bit on the hard side to understand.
Use EXECUTE AS OWNER
on the trigger, create a certificate, sign the trigger, drop the private key (so that noone else can use it to ever sign anything again), export the certificate (public key only), import the certificate in master, create a login derived from the certificate, grant authenticate to this login (in order to extend the database execute as impersonation), then grant view server state to this login. This is bullet proof, perfectly controled priviledge control. If the trigger need to be changed, the signing process (including the cert derived login and grants) have to be done again. From a security point of view, this is desired (you are signing a specific variant of the trigger), from operational point of view is rather a pita, but is manageable.
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