Linked Servers

Question

Linked Servers

ramana3327

SSCoach

Points: 19700
More actions
October 15, 2014 at 3:26 pm

#397510

Hi,
When I see linked servers under the server objects node from Server A, there is a linked server created to Server B.
Under that Linked ServerB, I saw server type SQL server and under security they selected Be made using the login's current security context.
What does it mean?
I am thinking that
Any login connecting from Server A, to Server B should connect with same rights he has in Server A
He doesn't need to have existing login on Server B.
Is this right?

Viewing 12 posts - 1 through 11 (of 11 total)

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

happycat59 One Orange Chip Points: 29387 More actions · Answer 1

ramana3327 (10/15/2014)
Hi,
When I see linked servers under the server objects node from Server A, there is a linked server created to Server B.
Under that Linked ServerB, I saw server type SQL server and under security they selected Be made using the login's current security context.
What does it mean?
I am thinking that
Any login connecting from Server A, to Server B should connect with same rights he has in Server A
He doesn't need to have existing login on Server B.
Is this right?

No that isn't what will happen. Users on server A who run queries on server B using the linked server will do so using the credentials they used when connecting to Server A. If those credentials don't exist on server b, then the query will not run (no different to attempting to connecting directly to server B using those credentials).

As for what rights the user would have on server B, that is completely dependent on permissions defined on server B and has absolutely nothing to do with any permissions on server A.

ramana3327 SSCoach Points: 19700 More actions · Answer 2

so if we want to use this option

"Be made using the login's current security context"

The same login has to exists on both servers, server A and server B

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Yes and no. If the same login exists on both servers, that'll work. But also, if you're a member of GroupA and GroupB, and GroupA has access to ServerA and GroupB has access to ServerB, that will also work.

John

ramana3327 SSCoach Points: 19700 More actions · Answer 4

Is it possible that to find a particular login belongs to which group?

I can see a login created with windows authentication in server A. I am not sure is it included in any group or not in serverA.

Server A has linked server with B.

But I didn't see the same login exists on server B.

so how to find that the login belongs to a group or not in B and as A?

Here serverA means Sql server, is installed on machine A and Sql server B is installed on machine B.

It is able to select the data from the linked server but not able to update. They are saying it has update statement will also works?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

EXEC xp_logininfo 'DomainName\GroupName', 'members'

John

ramana3327 SSCoach Points: 19700 More actions · Answer 6

It is throwing error with the msg invalid parameter

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Time for you to use your favourite search engine to investigate, then. It's a fully documented stored procedure.

John

ramana3327 SSCoach Points: 19700 More actions · Answer 8

Hi John,

Thanks for the help.

How to find that group.

We have a login name proddba. So I enter

exec xp_loginfo 'domainname\proddba', 'all'

Is that group is different? I am thinking that is windows group .

I go to the administrative tasks and under the group names I select one of that group and I enter this name in that extended sp but I didn't see any result. Please let me know, how to find that windows groups

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

I see - you want to find out which groups a particular user is a member of, rather than which users are members of a particular group? That's a bit more difficult, although if you have access to the AD administration tools, it should be fairly easy. The stored procedure will only give information about accounts (users and groups) that have access to SQL Server.

John

ramana3327 SSCoach Points: 19700 More actions · Answer 10

Thanks John.

My intention is ServerA login on machine A is accessing data from Server B on machine B.

I know they created linked server for Server A and Server B but this particular login doesn't exists under the logins of Server B.

I want to find how that login is accessing data and what kind of permissions it got through linked server. Is it impersonating some other login in server B?

I hope you understand you can help me with this

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

Have a look at the properties of the linked server, and go the the Security tab. Are there any logins listed in the box at the top? Which of the radio buttons at the bottom is selected?

John