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.