Re: MSSQL 2005 Application Roles and MS-Access DAO Applications

The short answer is no, application roles from Access/Jet simply don't
work well, mainly because additional connections are opened under the
covers by ODBC/Jet.

SQL Server 2005 has much better options, such as context switching
with the EXECUTE AS statement with its NO REVERT and WITH COOKIE
clauses. You can create a user account in a database that is not
mapped to a login. You then assign permissions to this account. Using
EXECUTE AS with a login-less user is more secure than an approle
because it is permission-based, not password-based.

See http://msdn2.microsoft.com/en-us/library/ms190384.aspx and
http://msdn2.microsoft.com/en-us/library/ms178106.aspx in SQL Server
2005 Books Online.

-Mary

On Wed, 31 Oct 2007 10:19:02 -0700, Dedge
<Dedge@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Is it possible to utilize MSSQL 2005 Application Roles from MS-Access DAO
(MDB)Applications? If so, why does the following code return an "Insufficient
Privilieges" message?

The following code refers to a MSSQL Application Role with DEFAULT_SCHEMA=dbo

Dim strODBCConnect As String
strODBCConnect = "ODBC;DSN=ConnectOnlyUser;UID=MyUID;PWD=MyPWD; Pooling=false"
Dim qd As DAO.QueryDef, rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.CreateQueryDef("")
qd.Connect = strODBCConnect
qd.ReturnsRecords = False
qd.SQL = "exec sp_setapprole 'MyApp','MyAppRolePWD';"
qd.Execute
'procedure call returns without error
qd.ReturnsRecords = True
qd.SQL = "select * from tblCompanyCode"
Set rs = qd.OpenRecordset()
'Insufficient Privileges Error!

TIA
.