I have two databases on the same SQL server. One for an ERP database that I am able to read tables and one I created in MS Access. A store procedure on the MSAccess database is updating some tables from the ERP DB. The MS Access DB from here on is independently running.
我在同一个SQL服务器上有两个数据库。一个用于ERP数据库,我能够读取表格和一个我在MS Access中创建的表格。 MSAccess数据库上的存储过程正在从ERP DB更新一些表。此处的MS Access DB独立运行。
When I installed on other computers, the Access Database is giving me a connection error when I am logged in as another user than myself. - Are permissions from the ERP database affecting me in my Access Database? Should I worry about them? - Is Windows Authentication enough to make the connection happen? - Do I need to Map the users over from the ERP database?? if so, how do I do it? - Can I grant every user on the Server permission to access my tables using Windows Authentication? do I need to go down this road? How do I do it?
当我在其他计算机上安装时,当我以另一个用户身份登录时,Access数据库给我一个连接错误。 - 来自ERP数据库的权限是否影响了我的Access数据库?我应该担心吗? - Windows身份验证是否足以使连接发生? - 我是否需要从ERP数据库中映射用户?如果是的话,我该怎么做? - 我是否可以授予服务器上的每个用户使用Windows身份验证访问我的表的权限?我需要走这条路吗?我该怎么做?
I am just not too sure what to do from here on. Here is some more information about the issue.
我不太确定从这里开始做什么。以下是有关该问题的更多信息。
-In my database I have a ODBC stringconnection that looks like this:
- 在我的数据库中,我有一个ODBC字符串连接,如下所示:
Driver={SQL Server Native Client 11.0};Server=SERVER;Database=DATABASENAME;Trusted_Connection=yes;
-I am packaging an exe file using SSESETUP which is awesome. -I am installing on the new computer and able to launch when I am logged on as myself however, I am getting a connection error when I am logged in as another user on the same computer. (Note: I am not able to debug from here on and see the vba because I am running under Access runtime 2013 only.)
- 我正在使用SSESETUP打包一个exe文件,非常棒。 - 我正在新计算机上安装,并且当我以自己的身份登录时能够启动但是,当我以同一台计算机上的另一个用户身份登录时,我收到连接错误。 (注意:我无法从此处进行调试并查看vba,因为我只在Access runtime 2013下运行。)
I would appreciate if someone can assist me in narrowing my search for a solution.
如果有人能帮助我缩小寻找解决方案的范围,我将不胜感激。
1 个解决方案
#1
1
Bottom line, it sounds like you need to set up database permissions on your SQL Server for each Windows account that will be using your Access db.
最重要的是,您需要为将要使用Access数据库的每个Windows帐户在SQL Server上设置数据库权限。
Here are specific answers to your questions:
以下是您的问题的具体答案:
Are permissions from the ERP database affecting me in my Access Database
来自ERP数据库的权限是否影响了我的Access数据库
Yes, of course they are. Regardless of how you connect to the database the permissions will always be in effect.
是的,他们当然是。无论您如何连接到数据库,权限始终有效。
Should I worry about them?
我应该担心吗?
Yes, of course you should. Otherwise you won't be able to work with the data.
是的,你当然应该。否则您将无法使用数据。
Is Windows Authentication enough to make the connection happen?
Windows身份验证是否足以使连接发生?
To make the connection, yes. SQL Server supports Windows authentication in both security modes (Windows only and mixed mode). But, this is just the connection. You also need to take the database permissions into consideration. Your Windows login needs to be granted the appropriate permissions within the specific database in order to do anything.
为了建立联系,是的。 SQL Server支持两种安全模式下的Windows身份验证(仅限Windows和混合模式)。但是,这只是连接。您还需要考虑数据库权限。您的Windows登录需要在特定数据库中被授予适当的权限才能执行任何操作。
Do I need to Map the users over from the ERP database?
我是否需要从ERP数据库映射用户?
Map to what? Access? Based on the connection string in your question you're using Windows authentication to connect Access to SQL Server, so Access will connect to SQL Server under the security context of whoever is running Access. So the SQL Server permissions will need to be configured for each user who will be running your Access db.
映射到什么?访问?根据您的问题中的连接字符串,您使用Windows身份验证连接Access到SQL Server,因此Access将在运行Access的任何人的安全上下文下连接到SQL Server。因此,需要为将运行Access数据库的每个用户配置SQL Server权限。
Can I grant every user on the Server permission to access my tables using Windows Authentication?
我是否可以授予服务器上的每个用户使用Windows身份验证访问我的表的权限?
Access to the SQL Server tables? Sure, you could. But you should limit it to only the necessary permissions. Granting access for everyone is likely overkill and not best practice.
访问SQL Server表?当然,你可以。但是你应该将它限制为只有必要的权限。授予每个人访问权限可能是过度杀伤而非最佳做法。
#1
1
Bottom line, it sounds like you need to set up database permissions on your SQL Server for each Windows account that will be using your Access db.
最重要的是,您需要为将要使用Access数据库的每个Windows帐户在SQL Server上设置数据库权限。
Here are specific answers to your questions:
以下是您的问题的具体答案:
Are permissions from the ERP database affecting me in my Access Database
来自ERP数据库的权限是否影响了我的Access数据库
Yes, of course they are. Regardless of how you connect to the database the permissions will always be in effect.
是的,他们当然是。无论您如何连接到数据库,权限始终有效。
Should I worry about them?
我应该担心吗?
Yes, of course you should. Otherwise you won't be able to work with the data.
是的,你当然应该。否则您将无法使用数据。
Is Windows Authentication enough to make the connection happen?
Windows身份验证是否足以使连接发生?
To make the connection, yes. SQL Server supports Windows authentication in both security modes (Windows only and mixed mode). But, this is just the connection. You also need to take the database permissions into consideration. Your Windows login needs to be granted the appropriate permissions within the specific database in order to do anything.
为了建立联系,是的。 SQL Server支持两种安全模式下的Windows身份验证(仅限Windows和混合模式)。但是,这只是连接。您还需要考虑数据库权限。您的Windows登录需要在特定数据库中被授予适当的权限才能执行任何操作。
Do I need to Map the users over from the ERP database?
我是否需要从ERP数据库映射用户?
Map to what? Access? Based on the connection string in your question you're using Windows authentication to connect Access to SQL Server, so Access will connect to SQL Server under the security context of whoever is running Access. So the SQL Server permissions will need to be configured for each user who will be running your Access db.
映射到什么?访问?根据您的问题中的连接字符串,您使用Windows身份验证连接Access到SQL Server,因此Access将在运行Access的任何人的安全上下文下连接到SQL Server。因此,需要为将运行Access数据库的每个用户配置SQL Server权限。
Can I grant every user on the Server permission to access my tables using Windows Authentication?
我是否可以授予服务器上的每个用户使用Windows身份验证访问我的表的权限?
Access to the SQL Server tables? Sure, you could. But you should limit it to only the necessary permissions. Granting access for everyone is likely overkill and not best practice.
访问SQL Server表?当然,你可以。但是你应该将它限制为只有必要的权限。授予每个人访问权限可能是过度杀伤而非最佳做法。