Support for Impersonation of an User using "Execute As"?

[gautam-taygo]

Question
I am bit curious on "Users without login" feature provided by SQL server. Currently we have a single DB user that we user via a Pool to connect and query database. The Application level logins/users are completely separate. Due to obvious reasons, such as better auditing and visibility in database layer, I am planning to map application users to "Users without login" in the database level.

Next step would be to use the same Singular login to access the database but use command like

EXECUTE AS USER = 'John'

to switch the context.

I am looking into how this can be easily managed via tedious or node-mssql pool? In SQL, I think I need to add the above statement on top of a batch to make sure that it affects the current session. But at the same time, adding this as a statement to every SQL command going out of the application seems ineffective.

Is there any other way to achieve the same?
Thank you for your time.

[arthurschreiber]

I haven't tried this out, but I imagine that you can just run a EXECUTE AS USER ... statement before whatever statements you want to run as that user, and then switch back using REVERT whenever you want to get back to the original user - or never revert if you want to keep impersonating that user forever.

You're right that with connection pooling, things become a bit more complicated. I'm not familiar with the node-mssql pool library, but you could either build some functionality on top that checks out a connection from the pool, impersonates the user, and reverts before checking the connection back to the pool, or you might use completely different pools for different impersonations.