Re: MS-Access and SQL 2005 Application Roles

Application roles don't work well from Access because the ODBC driver
opens additional connections under the covers that do not have the
application role activated.

-Mary

On Wed, 24 Oct 2007 09:19:01 -0700, Dedge
<Dedge@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I have created an Application Role with DEFAULT_SCHEMA=dbo.

From MS-Access, I retrieve the AR password and execute sp_setapprole as
follows:
qd.Connect = strODBCConnect
qd.ReturnsRecords = False
qd.SQL = "exec sp_setapprole 'MyAppName','" & strAppRolePWD & "';"
qd.Execute

Fine until now...but when I try to open a recordset:
Dim qdSel As DAO.QueryDef
Set qdSel = db.CreateQueryDef("")
qdSel.Connect = strODBCConnect
qdSel.SQL = "select * from MyTable"
qdSel.ReturnsRecords = True
Set rs = qdSel.OpenRecordset()

the message: The SELECT permission was denied on the object ...

Any help is appreciated,
.

Loading