Здравствуйте. Будучи администратором БД (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) по частям:
Теперь сам скрипт. На мой взгляд, лучше всего создать функцию, а потом просто вызывать ее с параметром:
Осталось только вызвать функцию:
Вот, как оказалось просто. А саму идею подсказали на форуме. Спасибо.
P.S.: Огромное спасибо хаброюзерам: rachiu, Zorkus, FeNUMe, Atrax, AusTiN за поддержку и человеческое отношение к новичкам.
Серверов было около 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 за поддержку и человеческое отношение к новичкам.