April 3, 2010 at 2:16 am
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
April 3, 2010 at 2:28 am
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
April 3, 2010 at 2:36 am
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
April 3, 2010 at 2:45 am
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
April 3, 2010 at 10:15 am
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
April 3, 2010 at 10:56 am
I think I see what you are asking for now.
See: xp_logininfo
and: sp_validatelogins
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 12:16 am
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.
April 5, 2010 at 1:13 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply