SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Login and Windows Auth


SQL Server Login and Windows Auth

Author
Message
isakhnov
isakhnov
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 6
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
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35498 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
isakhnov
isakhnov
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 6
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
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35498 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
isakhnov
isakhnov
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 6
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
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35498 Visits: 11361
I think I see what you are asking for now.

See: xp_logininfo
and: sp_validatelogins



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
isakhnov
isakhnov
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 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
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35498 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search