如何获得ms-access以其他用户身份连接到ms-sql? [英] How do I get ms-access to connect to ms-sql as a different user?

问题描述

我如何以不同于其Active Directory ID的用户身份获得通过ms-access连接(通过ODBC)到ms-sql数据库的权限?

How do I get ms-access to connect (through ODBC) to an ms-sql database as a different user than their Active Directory ID?

我不想在ODBC连接中指定一个帐户，而是想在ms-access端将其隐藏起来，以使其对用户隐藏.在ODBC连接中执行此操作会使我回到我要避免的原始情况.

I don't want to specify an account in the ODBC connection, I want to do it on the ms-access side to hide it from my users. Doing it in the ODBC connection would put me right back in to the original situation I'm trying to avoid.

是的，这与先前的问题有关: http://www.stackoverflow.com/questions/50164/

Yes, this relates to a previous question: http://www.stackoverflow.com/questions/50164/

推荐答案

我认为，如果您使用

如果需要，请使用Windows身份验证将ODBC DSN保留在用户的计算机上.向您的用户授予对您的数据库的只读访问权限. (如果他们创建了一个新的mdb文件并链接了表，则他们将只能读取数据.)

If you need to, keep your ODBC DSN's on your users' machines using windows authentication. Give your users read-only access to your database. (If they create a new mdb file and link the tables they'll only be able to read the data.)

创建一个对您的数据库具有读/写权限的SQL登录名.

Create a SQL Login which has read/write permission to your database.

编写一个VBA例程，该例程在链接表上循环并重置连接以使用SQL登录，但请务必使用"DSN-Less"语法.

Write a VBA routine which loops over your linked tables and resets the connection to use you SQL Login but be sure to use the "DSN-Less" syntax.

"ODBC;Driver={SQL Native Client};" &
       "Server=MyServerName;" & _
       "Database=myDatabaseName;" & _
       "Uid=myUsername;" & _
       "Pwd=myPassword"

将此例程作为启动代码的一部分.

Call this routine as part of your startup code.

有关此方法的一些注意事项:

• 一旦您从读/写"更改为只读"，然后尝试返回到读/写"而不关闭并重新打开数据库(mde/mdb)文件，则访问连接信息似乎出现问题.如果您可以在启动时将其更改为读/写"，而在会话期间不进行更改，则此解决方案应该可以使用.

• Access seems to have an issue with the connection info once you change from Read/Write to Read Only and try going back to Read/Write without closing and re-opening the database (mde/mdb) file. If you can change this once at startup to Read/Write and not change it during the session this solution should work.

通过使用DSN-更少的连接，您就可以在代码中向用户隐藏凭据(假设您为他们提供了mde文件，就可以了).通常，对连接字符串进行硬编码不是一个好主意，但是由于您要处理内部应用程序，因此您可以采用这种方法.

By using a DSN - Less connection you are able to hide the credentials from the user in code (assuming you're giving them an mde file you should be ok). Normally hard-coding connection strings isn't a good idea, but since you're dealing with an in-house app you should be ok with this approach.

这篇关于如何获得ms-access以其他用户身份连接到ms-sql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！