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.
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 BanschbachConsultant, MCDBA |
 |
|
|
|
|