У администратора БД (Microsoft Dynamics NAV), возникла задача выдавать/проверять наличие роли SQL db_datareader для некоторых работников. Но в таблице список пользователей хранился в виде SID windows: S-1-5-21-3879… и записи постоянно добавлялись, т.е. необходимо было конвертировать из S-1-5-21-38… → aapetrov.
Серверов было около 70-ти и хотелось все сделать стандартными средствами. Как и положено начал с гугла — ничего полезного не нашел. Обратился за помощью на форум 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]
Как оказалось всё просто. Саму идею подсказали на форуме.
Powershell for Windows 