Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server with restricted access Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 3:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
Hi,

I created one SQL login with read-only permission to select the particular "VIEW" only. My question is how can i use this login into linked server 2008R2.

OR

How can i enable linked server with restricted access. I want to select one particular 'VIEW' from source database.

Its very urgent. Please help me.

Pradeep.



Pradeep
Post #1471484
Posted Tuesday, July 9, 2013 3:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 2,482, Visits: 3,025
Just create the linked server connection and specify the user credentials in the code or in the "Security" page (when using the GUI).

EXEC master.dbo.sp_addlinkedserver @server = N'{FQDN_instancename}', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'{FQDN_instancename}',@useself=N'False',@locallogin=NULL
,@rmtuser=N'{username}',@rmtpassword='########'



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1471489
Posted Tuesday, July 9, 2013 3:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
Thanks for your quick response.

I am very new about this topic.

I cant able to view SQL objects after connecting the linked server. Remote login credentials should be in remote server or local server also?



Pradeep
Post #1471503
Posted Tuesday, July 9, 2013 3:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 2,482, Visits: 3,025
You need access on both the source and the target.
You need access on the source to get to your starting point of the linked server. You need access on the target to access the objects on the other end of the linked server. The credentials for the target are defined in the linked server. The access on the source are the credantials to login to the instance. These credentials doesn't have to match the credentials in the linked server.

1. A linked server from Server X to Server Y is defined using credentials of User B
2. User A has access on Server X and is able to use the linked server
3. User A opens a connection to the target Server Y through the linked server
4. User A can access objects on Server Y using the credentials of User B


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1471509
Posted Tuesday, July 9, 2013 6:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
Thanks...

Pradeep
Post #1471567
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse