Can I 'EXECUTE AS' a DML trigger? 1

[TheBugSlayer]

I have an audit FOR INSERT trigger on a client table. It saves information pertaining to the newly created client and which user created it in the audit table. Among the information recorded is the host name and the IP address, which is obtained from the DMV sys.dm_exec_connections. However, users must have VIEW SERVER STATE permissions to use the said view. I have granted the proper permission to two application logins already. It can be a little tedious to grant the same permission repeatedlly. My question is, is it possible to execute my trigger under the security context of another principal, as is possible with DDL triggers, SPs, queues and functions?

Thanks for your advice.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[SQLBill]

I'm not quite clear on what you are looking for, but I think this might be it.

You want people to use the view, but you don't want to have to keep adding new logins to the view. One way is to create a database role (ie. db_useview) and grant the role access to the view (GRANT SELECT ON <viewname> TO db_useview). Then you just grant the login db_useview and they will have that permission on the view.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[TheBugSlayer]

SQLBill, that's one way of doing it, the traditional way. However what I am asking is specifically whether the EXECUTE AS clause is possible within a DML trigger. From what I know and see, I believe it's not but maybe it is...
You can do this with SP, which allows a user who has no permission on the SP directly to run it as a user who does have permission on it and the objects it accesses:

USE Sales;
GO
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'CompanyDomain\SqlUser1'
AS
   SELECT user_name();
GO

What I am asking is, can you write something like this:

CREATE TRIGGER [dbo].[Client_Audit] 
   [COLOR=green]WITH EXECUTE AS sa[/color]
   ON  [dbo].[Client] 
   FOR INSERT
   AS
   ...
   [COLOR=green]SELECT @IPAddress = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID[/color]
   ...

or some impersonation of a user with the rights to the system DVM?
Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[fredericofonseca]

sql 2008 R2 does allow a "execute as" on triggers - so unless the version you are using doesn't allow it you should be good

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[TheBugSlayer]

Thanks Frederic. The server is 2005, my SSMS is 2008 R2, so I can't use it but it's good to know the feature is available.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)