Problem with Linked Server in SQL 2005

Question

Problem with Linked Server in SQL 2005

k.barghafshan

SSC Journeyman

Points: 94
More actions
April 23, 2008 at 4:53 am

#121824

When i add a linked server between two SQL 2005, the objects can not be seen in the catalogs, not only with the permitted user even with the SA user.
When I add SA in linked server, the object could be seen (Only SA can view them).
I can`t find a way that the catalogs could be seen by permitted users.
Is it normal or there is some way to view them?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Normal is when a Linked Server login has permissions, then you can see the tables. I can not see the tables in Management Studio when linking to Oracle or Lotus Notes but I can query OK.

Use a Windows group and add all users to the group

or use a selection "Be Made Using Login's Current Security Context"

in this case either a group or all individual users have to have logins on BOTH servers and log in to SQL Server using their Windows authentication.

Regards,Yelena Varsha

k.barghafshan SSC Journeyman Points: 94 More actions · Answer 2

Thanks for the reply.

I have used the selection "Be Made Using Login's Current Security Context", but it has no use.

i`ve found the way that a permitted user could view the list of objects under "Catalogs".

you must add the login of permitted user in the master database, and garnt it the "Execute" permissin to "sys.xp_prop_oledb_provider" explicitly.

Regards

mssqlsrv SSCrazy Points: 2375 More actions · Answer 3

I have the same issue. Need to give exec permission on sys.Xp_prop_oledb_provider. Is it dangerous to grant permissions ???