MsAccess to SqlServer：sp_setapprole问题 [英] MsAccess to SqlServer: sp_setapprole problem
问题描述
我正在使用Access2002前端和Sql2005
后端的应用程序。我必须使用集成安全性。我想阻止我的用户使用另一种方式来改变数据，而不是通过前端。
在我看来，这样做的机制是Sqlserver
sp_setapprole程序。在直接编程到
Sqlserver时工作正常，并且第一次看到Access数据项目似乎是通过调用sp_setapprole proc来实现它的工作原理。
但由于多种原因，我想使用普通的MDB作为数据访问方法使用Dao3.6作为
前端。当我使用SqlServer作为后端时，这通常工作正常
，但现在当我想使用
综合安全时，必要的sp_setapprole不会表现为
预期：
1）使用DSN时通过passthrough查询执行它似乎
工作，但突然发生了tablepriviliges的变化（因为
激活角色）可能会消失。似乎机制是
不稳定。
2）使用DSN less connectionstring导致sql用户
连接发生变化角色名称（应该是）但是表格
privs根本没有变化。
我在连接时读到了3个连接访问用途
Sqlserver（''如何使用Access项目和SQL的应用程序角色
Server 2000 Desktop Edition''）。也许这需要用
执行sp_setapprole后的奇怪行为。
是否有一些有这个问题的经验。并且希望一些
提示，因为我绝望地需要Int.Security +一个Mdb前端。
I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc.
But for a number of reasons I would like to use a normal MDB as
frontend with Dao3.6 as data access method. This works fine normally
when I use SqlServer as backend, but now when I want to use
Intergrated Security the necessary sp_setapprole won''t behave as
expected:
1) Excuting it via a passthrough query while using a DSN seems to
work, but suddenly the changes in tablepriviliges (because of
activating the role) can be gone. Seems like the mechanism is
unstable.
2) Using a DSN less connectionstring has the result that the sql user
for the connection changes in the rolename (as it should be) but table
privs don''t change at all.
I read about the ''3 connections Access uses'' when connecting to
Sqlserver (''How to use Application roles with Access projects and SQL
Server 2000 Desktop Edition''). Maybe this has got to do something with
the strange behaviour after executing sp_setapprole.
Is there some with experience with this problem. And hopelfully some
tips, because I desperatly need the Int.Security + an Mdb frontend.
推荐答案
你的问题正在经历的是由于b / b
ODBC的有限功能造成的。对于基本操作，ODBC是一种将Access mdb
连接到sql server后端的简便方法。但是一旦操作变得更加复杂 - 就像你的情况一样 - ODBC的局限性变得明显。解决方法是切换到ADO - 这就是ADO开发的原因 - 克服了ODBC的限制。
你可以通过ADODB执行sp_setapprole。命令对象很容易
（只需在Microsoft ActiveX的工具/参考中引用
DataObjects Library 2.5或更高版本）
Sub xyz（）
Dim cmd As New ADODB.Command
cmd.ActiveConnection =" Provider = SQLOLEDB;数据
Source = yourSrvr; Database = yourSvrDB; Trusted_Connect ion = Yes
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText =" sp_setapprole"
cmd.Execute
cmd.ActiveConnection.Close
结束子
Rich
***通过Developersdex发送 http://www.developersdex.com ***
The issue you are experiencing is caused by the limited capabilities of
ODBC. For basic operations ODBC is an easy way to connect an Access mdb
to a sql server backend. But once the operations get a little more
sophisticated - as in your case - the limitations of ODBC become
apparent. The workaround is to switch to ADO - this is why ADO was
developed - to overcome the limitations of ODBC.
you can execute sp_setapprole through an ADODB.Command object very
easily (just make a reference in Tools/References to Microsoft ActiveX
DataObjects Library 2.5 or higher)
Sub xyz()
Dim cmd As New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSrvr;Database=yourSvrDB;Trusted_Connect ion=Yes
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_setapprole"
cmd.Execute
cmd.ActiveConnection.Close
End Sub
Rich
*** Sent via Developersdex http://www.developersdex.com ***
在27日，19：13，Rich P< rpng ... @ aol.comwrote：
On 27 jun, 19:13, Rich P <rpng...@aol.comwrote:
您遇到的问题是由于
ODBC的功能有限造成的。 *对于基本操作，ODBC是一种将Access mdb
连接到sql server后端的简单方法。 *但是一旦操作变得更加复杂了 - 就像你的情况一样 - ODBC的局限性变得很明显。 *解决方法是切换到ADO - 这就是为什么ADO开发了* b $ b来克服ODBC的限制。
你可以通过一个执行sp_setapprole ADODB.Command对象很容易
（只需在Microsoft ActiveX的工具/参考中引用
DataObjects Library 2.5或更高版本）
Sub xyz（）
Dim cmd As New ADODB.Command
cmd.ActiveConnection =" Provider = SQLOLEDB;数据
Source = yourSrvr; Database = yourSvrDB; Trusted_Connect ion = Yes
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText =" sp_setapprole"
cmd.Execute
cmd.ActiveConnection.Close
结束子
Rich
***通过Developersdexhttp：//www.developersdex.com***
The issue you are experiencing is caused by the limited capabilities of
ODBC. *For basic operations ODBC is an easy way to connect an Access mdb
to a sql server backend. *But once the operations get a little more
sophisticated - as in your case - the limitations of ODBC become
apparent. *The workaround is to switch to ADO - this is why ADO was
developed - *to overcome the limitations of ODBC.
you can execute sp_setapprole through an ADODB.Command object very
easily (just make a reference in Tools/References to Microsoft ActiveX
DataObjects Library 2.5 or higher)
Sub xyz()
Dim cmd As New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSrvr;Database=yourSvrDB;Trusted_Connect ion=Yes
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_setapprole"
cmd.Execute
cmd.ActiveConnection.Close
End Sub
Rich
*** Sent via Developersdexhttp://www.developersdex.com***
$发送b
$ b Thx。但我真的很想坚持DAO申请
自己。我知道DAO不再是第一种技术了，但是我知道DAO
并且没有那么多编程工作，就像我以前在
$ b中所做的那样$ b过去。对于新的东西，我专注于Dotnet框架，它是'
Ado.Net。
如果您发送的ADO代码会产生影响，那将是多么好的事情
连接DAO / ODBC使用。这将是完美的，但正如大多数生活中完美的事情往往并非如此。无论如何我会测试它....
Thx. But I would realy like to stick to DAO for the application
itsself. I know DAO isn''t the nr 1 technology any more, but I know DAO
well and don''t do that much programming work as I used to do in the
past. For new stuff I concentrate on the Dotnet framework with it''s
Ado.Net.
What would be nice is if the ADO code you send would have its effect
on the connection DAO/ODBC uses. That would be perfect, but as most
things in life ''perfect'' isn''t often the case. I''ll test it anyway....
27月17日17:15，adjo< adgn ... @ gmail.comwrote：
On 27 jun, 17:15, adjo <adgn...@gmail.comwrote:
我正在开发一个带有Access2002前端的应用程序和Sql2005
后端。我必须使用集成安全性。我想阻止我的用户使用另一种方式来改变数据，而不是通过前端。
在我看来，这样做的机制是Sqlserver
sp_setapprole程序。在直接编程到
Sqlserver时工作正常，并且第一次看到Access数据项目似乎是通过调用sp_setapprole proc来实现它的工作原理。
但由于多种原因，我想使用普通的MDB作为数据访问方法使用Dao3.6作为
前端。当我使用SqlServer作为后端时，这通常工作正常
，但现在当我想使用
综合安全时，必要的sp_setapprole不会表现为
预期：
1）使用DSN时通过passthrough查询执行它似乎
工作，但突然发生了tablepriviliges的变化（因为
激活角色）可能会消失。似乎机制是
不稳定。
2）使用DSN less connectionstring导致sql用户
连接发生变化角色名称（应该是）但是表格
privs根本没有变化。
我在连接时读到了3个连接访问用途
Sqlserver（''如何使用Access项目和SQL的应用程序角色
Server 2000 Desktop Edition''）。也许这需要用
执行sp_setapprole后的奇怪行为。
是否有一些有这个问题的经验。并希望一些
提示，因为我绝望地需要Int.Security +一个Mdb前端。
I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc.
But for a number of reasons I would like to use a normal MDB as
frontend with Dao3.6 as data access method. This works fine normally
when I use SqlServer as backend, but now when I want to use
Intergrated Security the necessary sp_setapprole won''t behave as
expected:
1) Excuting it via a passthrough query while using a DSN seems to
work, but suddenly the changes in tablepriviliges (because of
activating the role) can be gone. Seems like the mechanism is
unstable.
2) Using a DSN less connectionstring has the result that the sql user
for the connection changes in the rolename (as it should be) but table
privs don''t change at all.
I read about the ''3 connections Access uses'' when connecting to
Sqlserver (''How to use Application roles with Access projects and SQL
Server 2000 Desktop Edition''). Maybe this has got to do something with
the strange behaviour after executing sp_setapprole.
Is there some with experience with this problem. And hopelfully some
tips, because I desperatly need the Int.Security + an Mdb frontend.
顺便问一下：还有另外一种方法可以通过
来解决获取数据的另一种方式，而不是应用程序的问题然后使用sp_setapprol
机制？欢迎90％的解决方案.....
By the way: is there another way to solve the ''get to the data via
another way than the app'' problem then using the sp_setapprol
mechanism? 90% solutions are welcome as well.....
这篇关于MsAccess to SqlServer：sp_setapprole问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
