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 12»»

Linked server restirction Expand / Collapse
Author
Message
Posted Friday, August 2, 2013 7:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, October 18, 2014 10:34 PM
Points: 27, Visits: 114
Hi friends,

Is there a way to restrict linked server access for a particular login in sql server 2008r2.

Thanks in advance.

Post #1480403
Posted Friday, August 2, 2013 7:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
sundar329 (8/2/2013)
Hi friends,

Is there a way to restrict linked server access for a particular login in sql server 2008r2.

Thanks in advance.



What do you mean by restrict the access?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480408
Posted Friday, August 2, 2013 7:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, October 18, 2014 10:34 PM
Points: 27, Visits: 114
That particular user should not fetch any data through linked server.
Post #1480413
Posted Friday, August 2, 2013 8:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
sundar329 (8/2/2013)
hi friends,

Is there a way to restrict linked server access for a particular login in sql server 2008r2.

Thanks in advance.


yes.

on this screen of the linked server properties, simply make sure the first radio button "not be made" is selected.
additionally, everyone who DOES have permissions needs to be mapped/added int eh grid pictured here as well.


when set up like that, the user will not have access/gets an error when trying to use it.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480415
Posted Friday, August 2, 2013 8:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, October 18, 2014 10:34 PM
Points: 27, Visits: 114
is it possible with some triggers
Post #1480437
Posted Friday, August 2, 2013 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
sundar329 (8/2/2013)
is it possible with some triggers


Why do you want triggers for this? What objects would you put the triggers on? If you want to restrict access by user the approach Lowell showed you is the best way to do it.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480440
Posted Friday, August 2, 2013 9:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
no, there are no events for triggers to capture that affect linked server access.

sundar329 (8/2/2013)
is it possible with some triggers


in the screenshot above, if they are not mapped, they have no access; nothing else will work, and nothings going to be easier.


maybe you are leaving out some important information?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480462
Posted Friday, August 2, 2013 10:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, October 18, 2014 10:34 PM
Points: 27, Visits: 114
We have already created linked server now we cannot re-create the linked server. Is there any other possibilities to restrict linked server access for particular login alone.
Post #1480498
Posted Friday, August 2, 2013 10:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
sundar329 (8/2/2013)
We have already created linked server now we cannot re-create the linked server. Is there any other possibilities to restrict linked server access for particular login alone.


No need to recreate it, just change the properties.

Server Objects -> Linked Servers -> Right click -> Properties.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480509
Posted Wednesday, August 7, 2013 2:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, October 18, 2014 10:34 PM
Points: 27, Visits: 114
if am adding a new login am getting the below error:

"ad hoc updates to system catalogs are not allowed"
Post #1481703
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse