We define user login by its SID using MS SQL

Hello. Being a DBA (Microsoft Dynamics NAV), there was a task to issue / check the presence of the SQL role db_datareader for some employees. But in the table the list of users was stored in the form of SID windows: S-1-5-21-3879 ... and records were constantly added. In other words, it was necessary to convert from S-1-5-21-38 ... → aapetrov.

There were about 70 servers and I wanted to do everything with standard tools (do not use, for example, psgetsid.exe ).

As it should be, I started from Google - I did not find anything useful. I had to ask for help at a highly respected forum . Many options were offered, for which many thanks to good people, but alas, the solution was never found.
"Even if the guys from sql.ru could not tell, I thought, that means everything, this is the end."
')

And on the verge of a nervous breakdown, the problem was still solved.

First, a little theory. Let's analyze the SID (S-1-5-21-3879291865-2298129343-1096376209-3741) in parts:

• S - tells us that this is the SID;
• 1 - level of control;
• 5 - identifier authority;
• 21 - the first subordinate identifier authority;
• 3879291865, 2298129343 and 1096376209 - the remaining subordinate powers of the identifier, collectively, they denote the domain or computer that issued the SID;
• 3741 is a relative identifier.

More information about can be read here .

Now the script itself. In my opinion, it is best to create a function, and then just call it with a parameter:

-- , .

IF OBJECT_ID (N'dbo.StringSIDToLogin', N'FN') IS NOT NULL
DROP FUNCTION dbo.StringSIDToLogin
GO
CREATE FUNCTION dbo.StringSIDToLogin (@MYSID AS VARCHAR(255))
RETURNS VARCHAR(300)
AS
BEGIN

--

DECLARE @A AS BIGINT ,@B AS BIGINT ,@C AS BIGINT ,@D AS BIGINT
SET @MYSID = REVERSE(@MYSID)
SET @D = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)
SET @C = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)
SET @B = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)
SET @A = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))
declare @sid_sql VARBINARY(100)
DECLARE @StrLogin VARCHAR(100)
set @sid_sql= 0x010500000000000515000000
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@A)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@B)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@C)))
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@D)))

-- MS SQL

set @StrLogin=SUSER_SNAME(@sid_sql)
set @StrLogin=REPLACE (@StrLogin,'dom\','')
RETURN (@StrLogin)
END
GO

It remains only to call the function:

SELECT dbo.StringSIDToLogin('S-1-5-21-1106671424-631848431-2339101832-7032') AS [Login]

That turned out to be simple. And the idea itself was suggested on the forum . Thank.

PS: Many thanks to habrowsers: rachiu, Zorkus, FeNUMe, Atrax, AusTiN for their support and human attitude towards beginners.