Блог

Я пытаюсь написать инструкцию sql для управления выбором заголовка отчета в нескольких базах данных по группам пользователей, которым назначен пользователь. Это делается для ограничения безопасности выбора базы данных при запуске отчета SSRS, чтобы пользователи могли выбирать только свою ветку или группу ветвей, к которым у них есть доступ. Пока я могу возвращать результаты группы для одного пользователя. Я пытаюсь получить список всех активных пользователей и групп AD, которые похожи на GRP-XP%. Вот мой сценарий, который пока работает только для одного имени пользователя. В конечном итоге эта таблица будет передана в PowerBI, поэтому мне понадобится имя пользователя, таблица групп пользователей для заполнения списка.

 Declare @username varchar(max) = 'ssmith'
DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)

SET @Query = '
    SELECT @Path = distinguishedName
    FROM OPENQUERY(CSAD, ''
        SELECT distinguishedName, SAMAccountName 
        FROM ''''LDAP://DC=Domain,DC=co, dc=uk''''
        WHERE 
            objectClass = ''''user'''' 
            AND sAMAccountName = '''''   Replace(@Username, 'domain', '')   '''''
    '')
'
EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT 

SET @Query = '
    SELECT
    Replace(Right(cn, Len(cn)-7), '' '', '' '')
    FROM OPENQUERY (CSAD, ''<LDAP://DC=Domain,DC=co,DC=uk>;(amp;(objectClass=group)(member:1.2.840.113556.1.4.1941:='   @Path  '));cn, adspath;subtree'')
    where CN like ''GRP-XP%'' 
    Order By cn'
Declare @table Table (Name varchar(100))
Insert into @table
EXEC SP_EXECUTESQL @Query  
select * from @table
  

Таким образом, результаты будут выглядеть следующим образом;

Спасибо

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

В основном он возвращает список всех пользователей и связанных групп, поэтому его должно быть достаточно легко изменить для ваших нужд.

 ALTER PROC [AD].[Get_AD_AllUsersWithGroups]
AS
DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)
DECLARE @distinguishedName nvarchar(256)
DECLARE @SAMAccountName nvarchar(256)

CREATE TABLE #users (distinguishedName nvarchar(1000), SAMAccountName nvarchar(100))
CREATE TABLE #results(SAMAccountName nvarchar(100), DistinguishedName nvarchar(1000), GroupName nvarchar(1000), ActiveDirectoryPath nvarchar(1000))

-- Get all the users from AD
SET @Query = '
   SELECT distinguishedName, SAMAccountName
   FROM OPENQUERY(ADSI, ''
       SELECT distinguishedName , SAMAccountName
       FROM ''''LDAP://DC=MyDomain,DC=local''''
       WHERE 
           objectClass = ''''user'''' 
   '')
'



INSERT INTO #users
EXEC SP_EXECUTESQL @Query

-- For each user in #users, get a list of groups they belong to
DECLARE cUsers CURSOR FOR
    SELECT distinguishedName, SAMAccountName from dbo.#users u 
        order by u.distinguishedName

OPEN cUsers

FETCH NEXT FROM cUsers
INTO @distinguishedName, @SAMAccountName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @distinguishedName = REPLACE(@distinguishedName, '''', '''''')
    SET @SAMAccountName = REPLACE(@SAMAccountName, '''', '''''')
    
    SET @Query = '
        INSERT INTO #results
        SELECT '''   @SAMAccountName   ''', '''   @distinguishedName   ''', cn as GroupName, AdsPath AS ActiveDirectoryPath
        FROM OPENQUERY (ADSI, ''<LDAP://DC=MyDomain,DC=local>;(amp;(objectClass=group)(member:1.2.840.113556.1.4.1941:=' 
         @distinguishedName  '));cn, adspath;subtree'')'

    EXEC SP_EXECUTESQL @Query  


    FETCH NEXT FROM cUsers
    INTO @distinguishedName, @SAMAccountName
END

CLOSE cUsers
DEALLOCATE cUsers

SELECT * FROM dbo.#results r

GO