Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sthornton
Starting Member
2 Posts |
 Posted - 2003-04-08 : 20:29:43
|
| Hi,I have added a Linked Server to an Access 97 database. I have not done this before and have ran into a few problems. Network permissions aside (they really gave me trouble!) I have managed to add the database to different servers fine, but with varying results.The SQL Servers run under the same account with the correct permissions to the location of the database.Using domain Authority (as a member of the sysadmin role), I added the linked server to my department's test server. I set up the Security to connect with the password "Admin" without a password. The Access database does not have a system database.Under domain authority (as mentioned above) I cannot see the tables within the database. When I edit my SQL Server registration properties to connect as the system administrator "sa" account, I can see the tables.I must also execute a query against the linked server logged into Query Analyser as "sa".My question is this: How do I set up the security that: under domain authority (for my account and others) I can 1: See the tables in the server and 2. Run queries against the server without using the sa account?? |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-04-09 : 16:17:07
|
| I was able to use the following script to setup an MS Access 97 database as a linked server and query the database. I was able to query the Access DB under any SQL Server login. I am running SQL 2000 with SP3 on Windows 2000 SP2.sp_addlinkedserver 'AccessTest', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'c:\db1.mdb' go sp_addlinkedsrvlogin 'AccessTest', 'false', 'sa', 'Admin', NULL go select * from AccessTest...table1Jeff BanschbachConsultant, MCDBA |
 |
|
|
|
|
|
|
|
Linked Server: Ms Access 97