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?

Does certain role means database role? – Amir Keshavarz Oct 18 '13 at 18:13 — Amir Keshavarz, Oct 18 '13 at 18:13
sure, as long as it can be a really broad one. – Chris Pfohl Oct 18 '13 at 18:45 — Chris Pfohl, Oct 18 '13 at 18:45

Amir Keshavarz · Accepted Answer · 2013-10-18T19:57:04.487

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.

Oh, I meant one that didn't require re-running the query every time a user was added or removed or changed... — Chris Pfohl, Oct 18 '13 at 19:24
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, Oct 18 '13 at 19:27
I found it You should Create a trigger on create use to do this. I will update my post. — Amir Keshavarz, Oct 18 '13 at 19:48
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 . — Amir Keshavarz, Oct 18 '13 at 19:59

score 1 · Answer 2 · answered Oct 21 '13 at 15:21

1

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.

answered Oct 21 '13 at 15:21

SQLburn

129
5

How to Grant Permission to IMPERSONATE any other user?

2 Answers2