Dear Eric,

I have an existing 6.5 server using mixed security. NT Authorization has never been used, only SQL Security. I want to switch to using NT Auth on all my client PC's. I have a dozen or so MS Access DB's on my network for my users that link via ODBC to SQL tables. The ODBC links send the sa name and password to connect (not very secure).

Here's what I've done so far: *Using "NT User Manager For Domains" I added a test user and placed the test user into a test global group.

*Using SQL Security Manager, granted privilege to the test group, checked the boxes "Add login id's for group members", and "Add users to database", then chose the appropriate database. Later, I went in via "account detail" and added 2 more SQL DB's. The "account detail" screen shows "Databases currently defined in": DB1, DB2, DB3 (for example)

*In SQL Enterprise Manager, I looked at the test user's login id information. It shows that he has permission in the 3 above listed DB's and one of those 3 has the default box checked, his username indicated in the "User" box and his group's name in the "Group" box.

*In SQL Ent. Mgr., went to each of the 3 databases, accessed "permissions" and gave select permission on all tables to the user's group.

*Using a product called Speed Ferret, I am able to search/replace strings of text in an Access DB. Searching the ODBC links, it found all the instances of the sa name and password and removed them.

*Using "ODBC Data Source" on a client PC, I configured the System DSN to use "Windows NT Authentication using the network login ID".

*Then I signed in as the test user.

HERE'S THE PROBLEM: AS THE TEST USER, I AM ABLE TO USE THE ALREADY CREATED MS ACCESS DB'S THAT ARE EITHER ON THE NETWORK OR LOCAL. HOWEVER, IF I CREATE A BLANK NEW ACCESS DATABASE AND ATTEMPT TO CREATE AN ODBC CONNECTION TO ONE OF THE SQL DATABASE TABLES THAT I HAVE PERMISSION TO, I DON'T GET A CHOICE OF WHICH DATABASE TO CONNECT TO. RATHER, I AM AUTOMATICALLY CONNECTED TO DB1 (FOR EXAMPLE) AND IT'S TABLES.

MY QUESTION: IF HAVE PERMISSION TO 3 DATABASES, WHY DON'T I GET TO CHOOSE THE DATABASE I WANT AND THE TABLE I WANT TO LINK TO?

-- Anonymous, February 04, 2000

Answers

I believe that for any given data source that is defined in the ODBC administrator on a given PC that you will always be connected to the default database for that data source. You can, however, define a different data source for each of your databases and simply choose a different default database in each. You should be able to choose among your different data sources from within Access.

-- Anonymous, February 07, 2000