EXECUTE AS in trigger...Not working 1

[TheBugSlayer]

All,

I have a trigger that writes some data into an audit table. Among that data is the client's IP address, which it retrieves from the sys.dm_exec_connections DMV. When the user does not have permission to select from the DMV a The user does not have permission to perform this action. error is returned, obviously. These are service accounts that I must grant VIEW SERVER STATE permission to.

However, I would like to instead the trigger instead under the security profile with the proper permission, VIEW SERVER STATE. I have created the trigger with a

WITH EXECUTE AS 'Domain\administrator'

clause alright. Nonetheless, when I run an update statement on the table to test the trigger, it still fails with the same error! I have tried different impersonations to no avail.

What am I doing wrong? Is there any way I can switch the security context just for the call to the DMV and switch it back to the current user before the next statement? EXECUTE AS CALLER may not work for all logins, obviously.

PS: I am writing the code in SQL Server 2012 MS but the underlying DB is in 2008 R2.

Thanks for your help.

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

 

[yelworcm]

Execute as works for invoking database users' rights, but not for logins' rights. View server state is of course, a server permission. Can you use the function host_name() instead?

 

[TheBugSlayer]

Hi Yelworcm,

Are you saying that if my 'Domain\administrator' is a server login but not a database user it won't work? In this instance it is also a DB user.

I am also using the HOST_NAME(). Here is excerpt of my code:

...
 SELECT @IPAddress = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID;
...
INSERT INTO [Audit] ...
		 VALUES
			   ('Client Audit Trigger'
			   ,SUSER_SNAME()
			   ,CONVERT(CHAR(8), GetDate(), 112)
			   ,REPLACE(CONVERT(CHAR(10), GetDate(), 108),':', '')
			   ,'Client'
			   ,@Operation
			   ,'Main: ' + @Main + ', Sub: ' + @Sub
			   , @ClientID
			   ,PROGRAM_NAME()
			   ,HOST_NAME() + '/' + @IPAddress
			   ,@FacilityID
			   ,null);

I can do without the IP address if need be. It's working otherwise, I am explicitly granting VIEW SERVER STATE to the service accounts that update the table.

Thanks.

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

 

[yelworcm]

That is correct. when you "EXECUTE AS" a user, you only gain that user's database permissions. You do not gain the underlying login's rights. Not even to other databases that login has access rights on.