SQL Server Login and Windows Auth

Question

SQL Server Login and Windows Auth

isakhnov

SSC-Addicted

Points: 440
More actions
April 3, 2010 at 2:16 am

#139726

SQL Server instance is setup with windows auth, login is created matching to one of the domain groups.
How ca I retrieve the SID of the login?
SUSER_SID retrieves my windows account SID and I can get to the complete list of SIDs via
SELECT principal_id, sid, name, type, usage FROM sys.login_token but I do not see a way to retrieve a SID for the login itself.
Thanks,
Igor

Viewing 8 posts - 1 through 7 (of 7 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

isakhnov (4/3/2010)
How can I retrieve the SID of the login?
...
SUSER_SID retrieves my windows account SID
...
I do not see a way to retrieve a SID for the login itself.

SUSER_SID ('login') retrieves the SID for the login, as you say.

Not sure I understand your question - can you clarify?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

isakhnov SSC-Addicted Points: 440 More actions · Answer 2

SUSER_SID returns the SID of Windows domain user. I'm looking for a SID of the login created in the SQL Server that matches my domain group.

E.g.

Windows domain

user -> test\userA

domain group -> test\groupB

test\userA is a member of test\groupB

SQL Server

login -> test\groupB

SUSER_SID returns the SID of test\userA and I'm looking at ways to retrieve the SIF matching the specified on a server login (test\groupB)

Thanks,

Igor

Paul White SSC Guru Points: 150467 More actions · Answer 3

isakhnov (4/3/2010)
SUSER_SID returns the SID of test\userA and I'm looking at ways to retrieve the SIF matching the specified on a server login (test\groupB)

From Books Online: "'login' is the login name of the user. login is sysname. login, which is optional, can be a Microsoft SQL Server login or Microsoft Windows user or group. If login is not specified, information about the current security context is returned."

What does the following give you?

SELECT SUSER_ID(N'test\userA');

SELECT SUSER_ID(N'test\groupB');

SELECT sid

FROM sys.server_principals

WHERE name IN (N'test\userA', N'test\groupB');

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

isakhnov SSC-Addicted Points: 440 More actions · Answer 4

See inline in code section

Paul White NZ (4/3/2010)
isakhnov (4/3/2010)
SUSER_SID returns the SID of test\userA and I'm looking at ways to retrieve the SIF matching the specified on a server login (test\groupB)
From Books Online: "'login' is the login name of the user. login is sysname. login, which is optional, can be a Microsoft SQL Server login or Microsoft Windows user or group. If login is not specified, information about the current security context is returned."
What does the following give you?
SELECT SUSER_ID(N'test\userA'); -> sid of the windows user
SELECT SUSER_ID(N'test\groupB'); -> sid of the windows group
SELECT sid
FROM sys.server_principals
WHERE name IN (N'test\userA', N'test\groupB'); -> sid of the windows group only

Paul White SSC Guru Points: 150467 More actions · Answer 5

I think I see what you are asking for now.

See: xp_logininfo

and: sp_validatelogins

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

isakhnov SSC-Addicted Points: 440 More actions · Answer 6

I see. I can use xp_logininfo to get to the login used for the session, thanks!

Do you know how bad is the performance? Original implementation was retrieving this info from the sys.login_token but we had to drop it due to the terrible performance.

Paul White SSC Guru Points: 150467 More actions · Answer 7

isakhnov (4/5/2010)
I see. I can use xp_logininfo to get to the login used for the session, thanks!
Do you know how bad is the performance? Original implementation was retrieving this info from the sys.login_token but we had to drop it due to the terrible performance.

Same mechanism as far as I know. Not really my area, but my guess is that SQL Server has to contact Active Directory/ a domain controller via a call to Windows to populate sys.login_token.

sys.login_token and xp_logininfo are only really designed for diagnostics and trouble-shooting. If you need to check membership of a Windows group in a production application, use the IS_MEMBER built-in function.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi