делюсь скриптом - список пользователей с AD

[sanya_123]

Скрипт копирует всех пользователей домена erc в некую табличку Navision.
Зачем всё это? для запуска SSRS нам потребовался нормальный справочник SID- имя, т к nav хранит данные по SID в отрыве от имени пользователя.
Вычислять можно, но это шаманство.
забавно, что очень много на эту тему писано именно коллегами, которые занимаются NAV. MVP Stryk например отметился.

ключевые особенности

1. нужно зарегить linked сервер ADSI для доступа к БД
2. синтаксис LDAP отличается извращенностью, например не возвращается более 1000 записей. отсюда - перебор по первой букве пользователя
3. SID (атрибут objectSid )возвращается из AD в шестнадцатиричном формате, а в navision хранится в формате S-1-5-21-.....
то есть что-то куда-то нужно перекодировать. что и было сделано (HEX --> S-1-5-21-....)

дополнительная литература/откуда скопипастил:

--хороший справочник по разным возможным критериям. если скажем нужно вернуть не всех пользователей, а всех убитых/активных/всех компов, а не пользователей и т д
http://social.technet.microsoft.com/...dap-ru-ru.aspx

--хабра рулит. Обо всём сразу. Помогла например понять почему все глючило, если не ограничивать количество записей. и что делать.
http://habrahabr.ru/post/168699/

-=====
-- линкуем 'Active Directory Services'

/****** Object: LinkedServer [ADSI] Script Date: 04/08/2015 16:44:58 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'mydomain\myuser',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

-- делаем табличку для возврата результата. (на самом деле она делалась в Navision)
USE [Etalon_2]
GO

/****** Object: Table [dbo].[Windows all SID to Login] Script Date: 04/08/2015 16:50:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Windows all SID to Login](
[timestamp] [timestamp] NOT NULL,
[Key] [int] IDENTITY(1,1) NOT NULL,
[SID] [varchar](119) NOT NULL,
[ID] [varchar](132) NOT NULL,
[Name] [varchar](250) NOT NULL,
CONSTRAINT [Windows all SID to Login$0] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
) ON [Data Filegroup 1]

GO

SET ANSI_PADDING OFF
GO




-=====
-- основной скрипт

set nocount on

DECLARE @cmdstr varchar(4000)
DECLARE @nAsciiValue smallint
DECLARE @sChar char(1)


delete from ercmskdb10.[etalon_2].[dbo].[Windows all SID to Login]

SELECT @nAsciiValue = 65

WHILE @nAsciiValue < 91
BEGIN

--формируем хвост запроса
SELECT @sChar= CHAR(@nAsciiValue)
EXEC master..xp_sprintf @cmdstr OUTPUT, ' FROM OPENQUERY( ADSI, ''SELECT cn, sAMAccountName, objectSid FROM ''''LDAP://erc.rgs.ru/ DC=erc,DC=rgs,DC=ru'''' WHERE
objectClass=''''person'''' AND SAMAccountName = ''''%s*'''''' )', @sChar

--добавляем хвост к голове
SELECT @cmdstr =
'SELECT cn, sAMAccountName,

''S-1-5-21-''
+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),''0x''+sys.fn_varbintohexsubstring(0,cast(objectSid as varbinary(39)),13,4),1)) as varbinary(4)) as bigint) as varchar(10))
+''-''+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),''0x''+sys.fn_varbintohexsubstring(0,cast(objectSid as varbinary(39)),17,4),1)) as varbinary(4)) as bigint) as varchar(10))
+''-''+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),''0x''+sys.fn_varbintohexsubstring(0,cast(objectSid as varbinary(39)),21,4),1)) as varbinary(4)) as bigint) as varchar(10))
+''-''+CAST(CAST(CAST(REVERSE(CONVERT(binary(4),''0x''+sys.fn_varbintohexsubstring(0,cast(objectSid as varbinary(39)),25,4),1)) as varbinary(4)) as bigint) as varchar(10)) ' + @cmdstr



INSERT ercmskdb10.[etalon_2].[dbo].[Windows all SID to Login](Name,ID,[SID]) EXEC( @cmdstr )
--PRINT @cmdstr
SELECT @nAsciiValue = @nAsciiValue + 1
END