linked server

Question

linked server

bryan van ritter

SSCrazy

Points: 2270
More actions
April 16, 2010 at 4:53 am

#140388

Hi,
I've defined a linked server for a user. now i don't want the user to alter the linked server, but only let him see the tables and views of the catalog within this linked server. is there a way to do this.
gr,
bryan

Viewing 10 posts - 1 through 9 (of 9 total)

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

created a user having only read permission in SOURCE SERVER.

select security tab under linked server properties in TARGET SERVER and use that user in BE MADE USING THIS SECURITY CONTEXT.

bryan van ritter SSCrazy Points: 2270 More actions · Answer 2

Hi,

solved the problem by just giving the user the right to a system view.

bryan

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

Could you please share the system view name you granted permission on?

MJ

bryan van ritter SSCrazy Points: 2270 More actions · Answer 4

Of course here it is:

use master

grant execute on xp_prop_oledb_provider to [USERNAME]

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Thanks Bryan. You could have granted 'view definition permission' to that user under that database for the same too.

bryan van ritter SSCrazy Points: 2270 More actions · Answer 6

bryan van ritter

SSCrazy

Points: 2270

April 28, 2010 at 6:42 am

#1158840

thanks for the suggestion.

jol1185 SSC Enthusiast Points: 189 More actions · Answer 7

Hi Bryan and Manu,

I would like to know what will be the effect in granting execute permission to xp_prop_oledb_provider to a user in the database.

Currently, I'm encountering an issue with a linked server that has been migrated to SQL 2005 having an error about "Execute permission denied on object 'xp_prop_oledb_provider ', database 'mssqlsystemresource', schema 'sys'."

I was just wondering if we are not granting the user other permission other than what there linked server should be doing..

Thanks!

bryan van ritter SSCrazy Points: 2270 More actions · Answer 8

Hi,

Before i gave the user this permission i've tested several issues regarding the linked server. Like dropping, renaming etc. This all was not possible, so therefore decided that this was for us the proper way.

grtz,

Bryan

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 9

MANU-J.

SSC-Dedicated

Points: 31126

May 20, 2010 at 11:29 am

#1170359

I am on the same page as Bryan.