I'm trying to put the following query into a trigger (after declaring the variables):
SELECT @IPAddress = c.client_net_address
,@Port = c.local_tcp_port
,@Host = s.host_name
,@Program = s.program_name
,@Login = s.login_name
FROM sys.dm_exec_sessions s JOIN sys.dm_exec_connections c ON
s.session_id = c.session_id
WHERE s.session_id = @@spid
When I log into the server as 'sa' and run it from a New Query window in SQL 2008, it runs fine; however, if I try to hit it from a client (.NET web) application (which comes in as a user with limited permissions), I get an error stating that I don't have adequate permissions.
If I try to wrap the query in the following:
EXECUTE AS USER = 'dbo'
I get Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
If I try altering the trigger to WITH EXECUTE AS SELF, I get the same results.
In both cases, this happens even when I run it in a New Query window, when I AM LOGGED IN AS 'sa'. In this case the client application simply times out.
I've also tried creating a separate function WITH EXECUTE AS OWNER to return just the IP Address from sys.dm_exec_connections, and got the same Msg 15517 (above).
I would RATHER NOT try to create a login to the server with "VIEW SERVER STATE" permissions, as that has to be granted on a login, and can't be granted on a user created "WITHOUT LOGIN".
Does anyone know how to get the EXECUTE AS functionality working/what other settings I might need to look at to permit this?
Thank you for any help you can offer.