How to grant IMPERSONATE permission to any other user?

Question

To register the use of the application I'm developing, I need each user using my application to query my SQL Server database under their credentials.

In order not to save passwords in recoverable mode, I cannot create a connection for each user (because this will entail knowing their password behind a short window when they log in).

Obviously, the solution to this problem (which may not be optimal) is to run all sensitive requests as the general user of the "Application", posing as an input user (requiring me to associate only the registered user with the username ... that not so bad).

My problem is that I’m not sure how to provide to impersonate all users of a certain role or all users in general (not the brightest idea, because I do not want the application to impersonate a system administrator, for example).

grant impersonate on all to ApplicationLogin 

does not work, and there is no documentation that I can find that involves providing an avatar to members of the role, doable ...

Any ideas?

Answer 1

You can use dynamic sql. the code below selects all users associated with a specific role, and then grants permission to impersonate a user. You must create a user to log in to associate with the database, and then grant permission to impersonate all members of a particular 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 this works for you.

Answer 2

You can create your users using a stored procedure. The last line of your stored procedure will be to provide an impersonation.

To configure current users, you will have to cycle through them all and set the grant impersonation at a time.