Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Login and Windows Auth Expand / Collapse
Author
Message
Posted Saturday, April 3, 2010 2:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 5, 2010 8:31 AM
Points: 4, 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
Post #896169
Posted Saturday, April 3, 2010 2:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 11,194, Visits: 11,136
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896171
Posted Saturday, April 3, 2010 2:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 5, 2010 8:31 AM
Points: 4, 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
Post #896176
Posted Saturday, April 3, 2010 2:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 11,194, Visits: 11,136
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896179
Posted Saturday, April 3, 2010 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 5, 2010 8:31 AM
Points: 4, 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

Post #896278
Posted Saturday, April 3, 2010 10:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 11,194, Visits: 11,136
I think I see what you are asking for now.

See: xp_logininfo
and: sp_validatelogins




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896281
Posted Monday, April 5, 2010 12:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 5, 2010 8:31 AM
Points: 4, 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.
Post #896543
Posted Monday, April 5, 2010 1:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 11,194, Visits: 11,136
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896566
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse