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 |
|
nicoz
Starting Member
1 Post |
 Posted - 2006-06-19 : 01:29:47
|
| Hi All,I have an VB application using DAO/ODBC to connect to a SQL Server database. I recently wanted to use application roles to improve security and I face the following issue:once my user is logged on the server/database, the application executes:EXEC sp_setapprole 'myApprole', 'myPassword'Everything is fine until the application is using db.Execute mySQL, dbSQLPassThroughBy using the Execute method, DAO creates a new SQL session (you can see the session being created in SQL Profiler as soon as you run this line of code in debug mode). The problem is that the application role previously activated does not apply to the new SQL session. So I end up having a permission problem as soon as I use db.Execute...The weird thing is that it works if you use:db.OpenRecordSet(mySQL, dbOpenSnapshot, dbSQLPassThrough)Any thoughts or workarounds?Cheers, |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-19 : 02:57:58
|
| thoughts: approle works with one session, close the opened session before opening a new oneworkaround: do not use execute, create an sproc for mysql and call that sproc instead--------------------keeping it simple... |
 |
|
|
|
|
|
Using Application roles in a VB/DAO application