We are interested in a similar concept to control permissions. We currently do the same thing but basically login with a full access user id (however the users don't know what that user id is and/or the password.

The impersonator concept would work for this as well, but I assume there is no way to prevent the user for executing the impersonator command outside the application? So my question then would be how do you connect to MS SQL Server using a domain account? I.e. you are already logged into the PC/network with your own ID, but the app needs to login with a different domain account. Some how it needs to be able to accept a password in this process.

Thanks,

Kim

Thanks much Michael for the response. While that sounds interesting, how does it work for datawindows. We have too many of these datawindows with some of them using stright SQL statements & some using Stored Procedures, some are updateable, etc.,

Also I was thinking there would be some easier way to just change the impersonation right after the Connect (as opposed to change the whole application (our existing apps are too big) in so many places. We used to use "sp_setapprole", then it was also depreciated as it invloves user id & password and admins wanted to use only Windows Authentication.

Hey Jein,

Impersonation using PB app connected to MS SQL Server is straightforward.

Assume:

• Some AD account (EX: ID = MyAppUser) has required DB access 
• Your AD account only has access to
  • Log on to DB server - and
  • Perform EXECUTE AS USER='MyAppUser'

Now, this is the app logic you need in your PB client app:

// Set up SQLCA for Wndows Authentication
SQLCA.DBMS = ...
SQLCA.DBParms = ...

// Connect to database
CONNECT USING SQLCA;
if SQLCA.SQLCode <> 0 then throw create ex_DBConnectFailed

// Impersonate as_appUser (argument to function)
string ls_sql
ls_sql = "EXECUTE AS USER = N'" + as_appUser + "'"
EXECUTE IMMEDIATE :ls_sql USING SQLCA;
if SQLCA.SQLCode <> 0 then throw create ex_DBConnectFailed

Note: Code throws exceptions when DB connect or impersonation fails. You may want to use return values but the main logic is the same: CONNECT; EXECUTE AS USER.

Same type of logic applies if end-users have different roles that map to different AD accounts each having different access rights granted like read-only users, call center worker, manager, etc.

So your AD group may define which app user you can EXECUTE AS. Code above also suits such scenario.

HTH /Michael