Map between SQL Server SIDs and Windows SIDs


By:   |   Updated: 2014-10-13   |   Comments (4)   |   Related: More > Security

Problem

I occasionally see people troubleshooting some problem in Windows, where the only information they have is the Windows SID, in the form S-1-5-21-601521654-1178811285-3323947944-1000. Trying to map that to the SID belonging to a login or group in SQL Server is difficult, because they are in a different form, for example: 0x010500000000000515000000F67DDA23953B4346A86B1FC6E8030000. You certainly cannot eyeball that and identify a match, and even manual conversion between these two values is not exactly straightforward.

Solution

Based almost entirely on some work done long ago in a forum thread here, I thought that it would make a lot of sense to have a view over sys.server_principals - perhaps in your own utility database - which also exposed the Windows version of the SID. This would make it far easier to troubleshoot issues like this, even if they don't come up very often.

The forum thread above uses two scalar functions to map back and forth, but I am a big stickler for inline, table-valued functions. The benefit, in simplistic terms, is the way inline functions are optimized as part of the plan for the query, unlike scalar functions, which are called per row and optimized separately. Not that this is a situation where you need screaming performance, but it's a good behavior to follow in general.

And we actually only need one function here, because we will always have the SQL version of the SID in sys.server_principals. In order to create the function, though, first we will need a numbers table (this allows us to treat segments of the Windows SID string as a set instead of parsing them manually in an explicit loop):

CREATE TABLE dbo.TinyNumbers(Number TINYINT PRIMARY KEY);

INSERT dbo.TinyNumbers(Number) 
  SELECT TOP (256) ROW_NUMBER() OVER (ORDER BY number)-1 
  FROM master.dbo.spt_values;

We could generate a sequence of numbers in the function, too, without having some table of numbers to draw from, but that would either prevent us from marking the function as SCHEMABINDING (if we were to use a system view like spt_values instead), or lead to much more verbose looping or CTE-type code to generate the sequence without the assistance of any auxiliary objects. Here is the function:

CREATE FUNCTION dbo.GetWindowsSID
(
  @sid VARBINARY(85)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    SELECT ADsid = STUFF((SELECT '-' + part FROM 
    (
      SELECT Number = -1, part = 'S-' 
        + CONVERT(VARCHAR(30),CONVERT(TINYINT,CONVERT(VARBINARY(30),LEFT(@sid,1)))) 
        + '-' 
        + CONVERT(VARCHAR(30),CONVERT(INT,CONVERT(VARBINARY(30),SUBSTRING(@sid,3,6))))
      UNION ALL
      SELECT TOP ((LEN(@sid)-5)/4) Number, 
     part = CONVERT(VARCHAR(30),CONVERT(BIGINT,CONVERT(VARBINARY(30), 
  REVERSE(CONVERT(VARBINARY(30),SUBSTRING(@sid,9+Number*4,4)))))) 
      FROM dbo.TinyNumbers ORDER BY Number
    ) AS x ORDER BY Number
    FOR XML PATH(''), TYPE).value(N'.[1]','nvarchar(max)'),1,1,'')
  );
GO

Then we can simply create a view that pulls certain columns from sys.server_principals, and passes the SID to the function via CROSS APPLY:

CREATE VIEW dbo.server_principal_sids
AS
  SELECT sp.name, sp.[sid], ad.ADsid, sp.type_desc
    FROM sys.server_principals AS sp
    CROSS APPLY dbo.GetWindowsSID(sp.[sid]) AS ad
    WHERE [type] IN ('U','G') 
    AND LEN([sid]) % 4 = 0;

Now I can simply say:

SELECT name,[sid],ADSid,type_desc FROM dbo.server_principal_sids;

And I will get results like this:

server_principal_sids

Disclaimer: The function does a very good job at parsing Windows-based SIDs because they all happen to map to a very particular pattern (S-1-5-... see KB #163846 for details). For things like SQL logins, server roles and certificate-mapped logins, the pattern can be different, and the function does not account for those differences, leading to errors or bogus values. This is why the filters are in place in the view, so please use this function with caution if you are trying to generate Windows-looking SIDs from anything other than Windows logins - you will likely need to make adjustments for that to work.

Next Steps


Last Updated: 2014-10-13


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, April 10, 2020 - 7:13:03 PM - MARC TOWERSAP Back To Top

I won't claim credit for this, it's the latest post in the website you referenced above.  Thank lukewaw.

launch a admin powershell session

run get-localuser | select name,sid

find the username and sid you are trying to find. SID would have a format like S-#-#-##-######## etc etc), and copy it

In SSMS, run select SUSER_SNAME(SID_BINARY('<paste in sid>'))

You should now see the username that matches the SID, and you don't have to worry about the funkiness of the SQL Server hex sid.


Tuesday, October 24, 2017 - 9:21:25 AM - S.E. Back To Top

Ummm...it seems I cannot spare to learn how to cope with XML in TSQL... :)

Very nice and elegant! Thanks!


Tuesday, March 21, 2017 - 11:12:40 AM - Akash Back To Top

Thanks Aaron,

Saved me a lot of effort!


Monday, October 27, 2014 - 8:30:26 AM - Junior Galv„o - MVP Back To Top

Hi Aaoron,

Great article, good job.



download


Recommended Reading

Enabling xp_cmdshell in SQL Server

Encrypting passwords for use with Python and SQL Server

Understanding SQL Server fixed database roles

How to configure SSL encryption in SQL Server

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role





get free sql tips
agree to terms


Learn more about SQL Server tools