Определяем логин пользователя по его SID средствами MS SQL

Здравствуйте. Будучи администратором БД (Microsoft Dynamics NAV), возникла задача выдавать/проверять наличие роли SQL db_datareader для некоторых работников. Но в таблице список пользователей хранился в виде SID windows: S-1-5-21-3879… и записи постоянно добавлялись. Другими словами необходимо было конвертировать из S-1-5-21-38… → aapetrov.

Серверов было около 70-ти и хотелось все сделать стандартными средствами (не использовать, например psgetsid.exe).

Как и положено начал с гугла — ничего полезного не нашел. Пришлось обратиться за помощью на многоуважаемый форум. Было предложено множество вариантов, за что огромное спасибо добрым людям, но увы, решение так и не было найдено.
«Если даже ребята с sql.ru не смогли подсказать — подумал я — значит все, это конец».


И вот на грани нервного срыва, проблему все-таки удалось разрешить.

Для начала немного теории. Разберем SID (S-1-5-21-3879291865-2298129343-1096376209-3741) по частям:

• S — говорит нам о том, что это именно SID;
• 1 — уровень контроля;
• 5 — полномочие идентификатора;
• 21 — первое подчиненное полномочие идентификатора;
• 3879291865, 2298129343 и 1096376209 — остальные подчиненные полномочия идентификатора, все вместе они обозначают домен или компьютер, который издал идентификатор SID;
• 3741 — относительный идентификатор.

     Более подробно о можно почитать тут.

Теперь сам скрипт. На мой взгляд, лучше всего создать функцию, а потом просто вызывать ее с параметром:

--Проверяем существует ли функция, если да то удаляем и создаем.

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

Осталось только вызвать функцию:

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

Вот, как оказалось просто. А саму идею подсказали на форуме. Спасибо.

P.S.: Огромное спасибо хаброюзерам: rachiu, Zorkus, FeNUMe, Atrax, AusTiN за поддержку и человеческое отношение к новичкам.