set right permission to get connection ip for a user mapped to login

Question

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.

Answer 1

sys.dm_exec_connections will require the permissions you've stated of VIEW SERVER STATE. However, by looking at the code you only ever want to get the IP of the session the user is already in, so there is no need for the over-provisioning. Using CONNECTIONPROPERTY will fulfill the same request and NOT require any extra permissions.

I'd make it something as such:

CREATE PROCEDURE TestProc
AS
BEGIN
 SELECT CONNECTIONPROPERTY('client_net_address')
END

GRANT EXECUTE ON dbo.TestProc TO TestUser

You can then test:

USE YourDatabase
GO

EXECUTE AS LOGIN = 'TestLogin'
 EXEC dbo.TestProc
REVERT