I have a problem with properly settings the rights permissions for my user to get access to connections ips.
I know that, to get the conn ip I must set the VIEW SERVER STATE to the login that I use to connect to db. I know hot to do it and it works ok. The problem is when I want to execute a stored procedure that is executed as other user. This user (mapped to the login with the above permission) doesn't have permission to get the ip. What I have missed?
A script to reproduce:
CREATE LOGIN TestLogin WITH PASSWORD=N'TestLogin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF CREATE USER TestUser FOR LOGIN TestLogin use master GRANT VIEW SERVER STATE TO TestLogin
With this I can log to the database and execute the query:
SELECT client_net_address FROM sys.dm_exec_connections WHERE Session_id = @@SPID
But when I add the procedure I can't execute it:
CREATE PROCEDURE TestProc WITH EXECUTE AS 'TestUser' AS BEGIN SELECT client_net_address FROM sys.dm_exec_connections WHERE Session_id = @@SPID END GRANT EXECUTE ON OBJECT::dbo.TestProc TO TestUser
All I got is
Msg 297, Level 16, State 1, Procedure TestProc, Line 5 The user does not have permission to perform this action.