Change DB security roles based on connecting Application 1

[monkeylizard]

Assume the following:
SQL Server 2000.
Application AppX uses SQL security (not Active Directory).
UserBob is a valid AppX user and is allowed to make changes to data using AppX.
AppX uses a SQL database named DataBaseX.

Scenario 1
UserBob opens AppX, which makes an ODBC connection to DatabaseX using SQL security with UserBob's login ID and password. SQL Server knows that AppX is the connecting application.

Scenario 2
UserBob opens Microsoft Access, and makes a connection to DatabaseX. SQL Server knows that MS Access is the connecting application.

Question
Can UserBob's security profile change on the fly based on the connecting application? In Scenario 1, he's allowed to have change access to all of the tables and execute access to the stored procedures. Internal application profiles in AppX control what UserBob can and can not change. In Scenario 2, he should only have Read access to the tables, and no access to the stored procedures.

Similarly, what if I wanted UserJane to only be able to connect using AppX and not allow any other applications to connect using her SQL account. Is that possible?

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[mrdenny]

Sort of. Assign Bobs account the rights that it would need when he connects via Access, Excel, etc. Have your application activate an Application Role after connecting. This would remove his user rights, and give him the rights which have been assigned to his application role.

There's no way to do this automatically.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[monkeylizard]

So the application role has to be activated by the connecting application?

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[monkeylizard]

Nevermind. I Googled my own question. It makes sense now. Thanks Denny.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.