Hiding a linked server

Question

Hiding a linked server

ken.trock

SSCertifiable

Points: 5147
More actions
December 21, 2011 at 1:42 pm

#250253

Hello. I want to create a linked server in SQL 2005 to another 2005 machine but only want users in the db_owner role to be able to see/use it. Would this be done by using View Definition somehow?
Ken

Viewing 6 posts - 1 through 5 (of 5 total)

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

I guess it is not possible to hide the Linked servers.

I tried creating a sql login as a db_owner for couple of databases and when logged in as the new user, I could see the linked server.

But the details of the remote Server are not shown, all the properties like provider, Data Source were all blank (disabled)...

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Revenant SSC-Forever Points: 42467 More actions · Answer 2

"When you create a linked or remote server, SQL Server creates a default login mapping to the public server role. This means that by default, all logins can view all linked and remote servers. To restrict visibility to these servers, remove the default login mapping by executing sp_droplinkedsrvlogin and specifying NULL for the locallogin parameter."

http://msdn.microsoft.com/en-us/library/ms178530(v=sql.90).aspx

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

Then link the Server at runtime 😉

SELECT a.*

FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',

'SELECT GroupName, Name, DepartmentID

FROM AdventureWorks2008R2.HumanResources.Department

ORDER BY GroupName, Name') AS a;

For More: http://msdn.microsoft.com/en-us/library/ms190312.aspx

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

OR

SELECT *

FROM OPENDATASOURCE('SQLNCLI',

'Data Source=London\Payroll;Integrated Security=SSPI')

.AdventureWorks2008R2.HumanResources.Employee

http://msdn.microsoft.com/en-us/library/ms179856.aspx

ken.trock SSCertifiable Points: 5147 More actions · Answer 5

Revenant (12/21/2011)
"When you create a linked or remote server, SQL Server creates a default login mapping to the public server role. This means that by default, all logins can view all linked and remote servers. To restrict visibility to these servers, remove the default login mapping by executing sp_droplinkedsrvlogin and specifying NULL for the locallogin parameter."
http://msdn.microsoft.com/en-us/library/ms178530(v=sql.90).aspx

Thank you. I've seen that verbiage around but didn't put 2 and 2 together.

Was a little harder than I thought as I've always relied on the default local login, which this statement of course removes. Adding a login via sp_addlinkedsvrlogin fixes it.

Ken