Linked server restirction
-
Hi friends,
Is there a way to restrict linked server access for a particular login in sql server 2008r2.
Thanks in advance.
-
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
That particular user should not fetch any data through linked server.
-
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
is it possible with some triggers
-
sundar329 (8/2/2013)
is it possible with some triggersWhy 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
no, there are no events for triggers to capture that affect linked server access.
sundar329 (8/2/2013)
is it possible with some triggersin 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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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.
-
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
if am adding a new login am getting the below error:
"ad hoc updates to system catalogs are not allowed"
-
-
There's also the code way of handling this:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkedServerName', @locallogin = N'MySQLLogin', @useself = N'False', @rmtuser = N'RemoteServerUserName', @rmtpassword = N'RemoteServerPassword'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServerName',@useself=N'True',@locallogin=N'MyADLogin',@rmtuser=NULL,@rmtpassword=NULL
-- remove the default login to be sure it's not present.
EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname = N'LinkedServerName', @locallogin = NULL
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply