July 28, 2010 at 10:57 am
I was called up to a programmers client today and they have multiple linked SQL servers set up from the main database server.
Main database server is SQL 2005 and they are linking SQL 2000 servers. When I look at the linked servers from my PC in SSMS I see the catalogs underneath and therefore can see the tables of the linked servers. But the programmer can no longer see the catalogs, but used to see them.
The programmer used to have them and now he doesn't!
What would cause the catalogs not to show up in the client SSMS?
He says he didn't make any new SP installs on the client etc...
Google was not much help.
July 28, 2010 at 11:13 am
Sounds like his permissions to the metadata on the server got yanked....?
July 28, 2010 at 1:18 pm
Thanks for the reply. What permissions should I be looking for? They have db_owner access to the database on the linked server.
Do they need explicit read permissions to master?
July 29, 2010 at 8:34 am
Is the problem that he can't see them in the GUI or that he can't use them or both?
July 29, 2010 at 8:47 am
Can't see them in the user GUI. I can see them as administrator on my gui and on the server.
strangely, if the user goes to another PC, using SSMS 2008 he see them using the same credentials.
There is something in the client GUI stopping from viewing and running a query against the linked server.
I asked if they installed any software (like SSMS 2008) over SSMS 2005, but they said no.
July 29, 2010 at 8:49 am
You dont need permissions to see the linked server but you need permissions on the destination.
So may your dev's have had the destination rights scrubed?
🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
July 29, 2010 at 9:00 am
Dana Bibeault (7/29/2010)
Can't see them in the user GUI. I can see them as administrator on my gui and on the server.strangely, if the user goes to another PC, using SSMS 2008 he see them using the same credentials.
Same instance too?
That definitely smacks of being a PC related problem rather than SQL related. However, has he tried the same credentials / instance on a different SQL 2005 box?
Have him do that. If he can still see them on another box and via SQL 2k5, I'm leaning toward something in his windows profile being wonky or something running in the background that's interfering...
Check his Event Viewer (system and application) to see if anything was installed on his box recently. It doesn't have to be SQL related to cause problems with SQL server. Also, an uninstall could have caused a problem too.
Other options include 1) Reboot his PC to clear out all the cache (just for kicks and giggles, you understand), and 2) Reinstall the client tools on his box. These are the usual last resort methods, but who knows, the re-install might fix a corrupted .dll or something.
July 29, 2010 at 9:18 am
Thanks for the replys, I have left a message on programmers VM to see if problem is still on his PC. (other PC's worked fine connecting using 2008 and 2005 SSMS.)
It's PC related, and it's like looking for a needle in a hay stack!
August 19, 2011 at 2:49 am
I came across the same problem. I installed SP3 for SQL 2005 on my PC and it fixed the issue:-)
July 26, 2012 at 12:56 pm
I had this problem with one database not showing. I fixed it by noticing that although the username I was using in the linked server existed in the missing database, it was not associated with the same username in the server for the database. I had to run the usual query to associate an orphaned username with a login name in that database:
sp_change_users_login 'UPDATE_ONE', 'username', 'loginname', 'optionalpassword'
This usually happens when one restores the database from a different server.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply