How to Grant Permission to IMPERSONATE any other user?

Question

In order to log the usage of an application I am developing I need every user using my application to execute queries against my SQL Server Database under their own credentials.

In order to not be storing their passwords in a retrievable fashion, I can't be creating a connection on a per-user basis (because that would entail knowing their password past the brief window when they log-in).

The, seemingly obvious, solution to this problem (which may be sub-optimal) is to run all sensitive queries as a generic "Application" user, impersonating the logged in user (requiring me to only associate the logged in user with a username...which is not a bad thing).

My problem is that I'm not sure how to grant impersonate to all users of a certain role, or all users in general (not the brightest idea, because I don't want the app impersonating a sysadmin, for instance).

grant impersonate on all to ApplicationLogin

doesn't work, and there's no documentation that I can find that suggests granting impersonation on members of a role would be doable...

Any ideas?

Answer 1

You can use Dynamic sql . the code below fetches all users related to a specific role and then grant permission impersonate on a user. You should create a user on application login to relate it with database , then grant permission to impersonate on all members of specific role. This is the code:

CREATE TRIGGER S2
ON DATABASE
FOR CREATE_USER
AS

CREATE TABLE #T
(PRINCIPAL_NAME NVARCHAR(100),ROLE_NAME NVARCHAR(100));
WITH L AS (SELECT *
            FROM (SELECT P.name AS 'PRINCIPAL_NAME',R.role_principal_id AS 'GH'
                FROM SYS.database_principals P,sys.database_role_members R
                WHERE P.principal_id=R.member_principal_id OR P.principal_id=R.role_principal_id
                AND type<>'R') S INNER JOIN (SELECT P.name AS 'ROLE_NAME',P.principal_id AS 'GHA'
                                                FROM SYS.database_principals P,sys.database_role_members R
                                                WHERE P.principal_id=R.member_principal_id OR P.principal_id=R.role_principal_id
                                                AND type='R') D
                ON D.GHA=S.GH)
INSERT INTO #T
SELECT DISTINCT PRINCIPAL_NAME,ROLE_NAME
FROM L
------------ ENTER ROLE NAME HERE 
WHERE ROLE_NAME LIKE '%%'
------------
DECLARE @P NVARCHAR(100),@TEXT NVARCHAR(MAX)=''
------------------------- CHANGE IT TO YOUR DESIRED NAME OF YOUR APPLICATION USER
DECLARE @APPUSER NVARCHAR(100)='APPLICATION_USER'
-------------------------
DECLARE C CURSOR FOR SELECT PRINCIPAL_NAME FROM #T
OPEN C
FETCH NEXT FROM C INTO @P
WHILE(@@FETCH_STATUS=0)
    BEGIN
        SET @TEXT+='GRANT IMPERSONATE ON USER::['+@P+'] TO '+@APPUSER+' '
        FETCH NEXT FROM C INTO @P
    END
CLOSE C
DEALLOCATE C
DROP TABLE #T
EXEC(@TEXT)

I hope it work for You.