CODEWITHSUNDEEP
sql-serverms-accessodbc
user9531726
user9531726

Reputation: 23

How do I copy permissions from one database to another?

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.

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?

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:

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.)

I would appreciate if someone can assist me in narrowing my search for a solution.

Upvotes: 0

Views: 1514

Answers (1)

squillman
squillman

Reputation: 13641

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.

Here are specific answers to your questions:

Are permissions from the ERP database affecting me in my Access Database

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?

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.

Do I need to Map the users over from the ERP database?

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.

Can I grant every user on the Server permission to access my tables using Windows Authentication?

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.

Upvotes: 1

Related Questions