Can MS-Access 2003 Impersonate Windows user?

[NCYankee1]

(I originally posted this in the Access forms forum by mistake. Sorry for the duplication)

I have been given the task of converting an MS-Access 2003 application whose database currently resides on SQL2000 (mixed mode) and move the database to SQL2005. They want the database to reside on a Windows Authentication Mode SQL Server. This means that I would need to impersonate a Windows domain account that has authorization to hit that database. The MS-Access app links to the SQL tables and also calls stored procs on the SQL Server. Is there a way for MS-Access to impersonate a Windows user?

 

[dhookom]

Did you try my Run As suggestion?
Why don't you just update the SQL Server security to add the users?

Duane
Hook'D on Access
MS Access MVP

 

[NCYankee1]

Yes I tried your 'Run As' suggestion and it is a viable option. Thank you for that suggestion. The only problem with it is this: The MS-Access front end app needs to know who spcifically is logging in. And our DBA does not want to add/maintain a list of 80+ users' security on the SQL Server. So the goal is for people to log in as themselves, and the Access app will log into the SQL Server as an impersonated Windows User that has rights to the SQL Server. So what I am looking for is to find out if there is a way within the VBA code-behind to accomplish that kind of thing.

 

[dhookom]

We typically create a security group on the sql server machine like "MyApp_Users" and add users to this group using the computer management. Then in SQL Server, we grant the "MyServer\MyApp_Users" permissions as needed in databases.

I think it would be dangerous if you could use code to spoof a Windows user. I doubt this is viable. At least I hope not.

Duane
Hook'D on Access
MS Access MVP

 

[jsteph]

Nyc,
By saying you need to 'impersonate' a windows user, are you implying the users are not on any windows domain, or that they will not be on a domain trusted by the server's domain?

I don't see a problem if it's on the same domain and your users just join a group, as suggested by Duane. Then if you're linking tables with odbc connections you just set up trusted connection pointing to that db and they're good.
--Jim