Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 SQL sp_setapprole & MS Access

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-25 : 12:12:01
Steve writes "I have an Access 2000 MDB with linked tables via ODBC to a SQL Server 2000 database. I am currently setting up Application Role Security in SQL and have created an Access Pass-Through Query that executes sp_setapprole using odbc encryption. However I am getting some unexpected results.

The first time I run the query it seems to execute OK, I then attempt to use the new permissions by running a delete query on the linked table to be told either that 0 records will be affected if 0 records exist or Permission is denied if records do exist.

If I run the sp_setapprole query again it runs successfully however this time the delete query will execute as expected and further role permission reliant queries can be run successfully.

SQL is installed in a NT4 Server, with MDAC 2.7 and Jet 4 SP5.The PC is running NT4 workstation, with MDAC 2.7 and Jet 4 SP5."

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-25 : 16:27:18
The permissions for sp_setapprole only apply to the current connection. I think access may be making a new connection each time you issue a command to the linked tables. So you would never get the security of the approle. Try watching the results from sp_who in Query Analyzer while you go through each of your steps. This may help figure out what's going on.


Jeff Banschbach
Consultant, MCDBA
Go to Top of Page
   

- Advertisement -