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?

Solution 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.

Solution 2

You could create your users via a stored procedure. The last line of your stored procedure would be to grant impersonation.

To set up the current users, you would have to cycle through them all and set the grant impersonation this one time.

Comments

[Chris Pfohl]

Oh, I meant one that didn't require re-running the query every time a user was added or removed or changed...

[Amir Keshavarz]

there is no mechanism that the dbms could find out and do sth on adding users. but if you add users frequenty you can add this code to sql server agent to do it frequently or almost hourly. this query is not heavy to execute.

[Amir Keshavarz]

I found it You should Create a trigger on create use to do this. I will update my post.

[Amir Keshavarz]

Now it will work for every user where created and map to a role. but if the user is created without mapping to a role and then user map to role this trigger wont fire. The combination of this and sql server agent would reach you to your goal . there is no other way .