1. Marco Meoni
  2. SnapDevelop
  3. Thursday, 26 October 2023 20:46 PM UTC


would anyone advise on how to efficiently setup user impersonation in a datacontext?

In PB I can connect a DB as dba and then issue the statement EXECUTE AS mmeoni, so that all subsequent SQL statements will execute as user mmeoni, but still use the connection established with the dba user. 

How the API would handle this with the connection pool?

I'd rather avoid wrapping each request into an explicit transaction with upfront EXECUTE AS:

ls_sql = "EXECUTE AS " + username;
_context.SqlExecutor.ExecuteNonQuery(ls_sql, out SqlResult sqlResult);
// any transaction here will impersonate user mmeoni

Ideally, impersonation should happen when an instance of datacontext is injected into the service class.

Any suggestion will be welcome.




Francisco Martinez @Appeon Accepted Answer Pending Moderation
  1. Thursday, 26 October 2023 22:59 PM UTC
  2. SnapDevelop
  3. # 1

If you want the impersonation to begin at injection, how about defining 2 DataContexts with different users and injecting them according to the user you need?



  1. Marco Meoni
  2. Friday, 27 October 2023 05:24 AM UTC
Hi Francisco, users are many.

Extreme case scenario, a different user per each client.

I want to use the pool as defined in the cachename settings (i.e dba/sa user), but run SQL statements as impersonated users (username can come from custom HTTP header in the client request).


  1. Helpful
  1. Francisco Martinez @Appeon
  2. Monday, 30 October 2023 14:46 PM UTC
How about inheriting from the DataContext class and adding your own custom methods that extend the basic functionality but also take in the user to impersonate and create/commit the transaction? Then you replace the current DataContext with your custom class and modify the code in the places where you want to support that functionality.

Regards - Francisco

  1. Helpful
There are no comments made yet.
  • Page :
  • 1

There are no replies made for this question yet.
However, you are not allowed to reply to this question.