May 6, 2010 at 7:45 am
I have SQL Server 2008 on my machine and can create a linked server to our 2005 instance. I can only see 2 of the databases on that instance through the linked server, but that's fine as those are the only two I have permission on. If I use the Management Studio to connect directly to that instance, I can see all the databases and access the two I have permissions on.
We have SQL Server 2008 newly installed on a development server. If I try to create a linked server to the 2005 instance referenced above, I can create the linked server, but can only see the System Databases through the link. This holds true whether I try to do this from a connection on my machine or if I remote desktop into the development server and make the connection there. If someone else makes the linked server there and can see all the databases through it, when I use the same linked server I can only see the System Databases. I have sysadmin rights on the development server.
This is the case whether I make the connection as Server Type = SQL Server or Server Type = OLE DB Provider for SQL Server.
In short, it works as expected from my machine, but not from the development machine.
I've tried searching for a solution to this, but haven't found anything. I was hoping someone here might be able to help.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 6, 2010 at 8:05 am
Are you using the same security/credentials when you create the linked server in the two environments?
If you are, then try to run a select query on your dev server to query a table in your remote 2005 DBs which you have permissions on, and see if you could effectively 'see' the DB with queries.
If someone else makes the linked server there and can see all the databases through it, when I use the same linked server I can only see the System Databases.
Do they create the linked server with the same credentials as you do?
I have sysadmin rights on the development server.
This should not affect what you could see on the remote 2005 instance.
May 6, 2010 at 8:19 am
Are you using the same security/credentials when you create the linked server in the two environments?
Yes
If you are, then try to run a select query on your dev server to query a table in your remote 2005 DBs which you have permissions on, and see if you could effectively 'see' the DB with queries
I did that. I could not see the databases. That's how I found out there was a problem in the first place. : -)
Do they create the linked server with the same credentials as you do?
They create the linked server with their own credentials.
Since I made the post, one of the other users created a new instance of the linked server on the machine and I can see and query the database from the Dev server, but not my local machine. I refreshed my local machine and was able to see the databases. I then tried to query them and got a new error:
The SCHEMA LOCK permission was denied on the object
I then refreshed the linked server again and the tables disappeared.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply