MS SQL Server
  1. MS SQL Server
  3. TSQL: Comment get la list des groupes auxquels un user appartient dans Active Directory
Intereting Posts
J'ai besoin de la rangée supérieure du même user RegistrationId Using SQL Query (j'ai besoin de la requête de sélection de SQL) Comment désactiver Filestream sur SQL Server 2008 R2 pour activer la mise en miroir? Tables IOT vs Heap dans Oracle Impossible de se connecter à ms sql server 2005 en mode d'authentification Windows à l'aide de jdbc Comment puis-je créer une procédure stockée qui appelle sp_refreshview pour chaque vue de la database? SQL Challenge / Puzzle: Donné une trace de stack – Comment find l'élément supérieur à chaque moment? Erreur lors de la suppression d'une entité personnalisée dans Crm 2011 SQL: vue dynamic avec des noms de colonne basés sur les valeurs de colonne dans la table source Code de security pour l'injection SQL avec mysql_ * Copier la table d'un server vers un autre Comment faire 0 dans toutes les colonnes dans une table spécifique où la valeur est nulle en utilisant MSSQL Comment "créer une table en tant que (select * de)" SQL Server Progressive / Composé Soustraction? Utilisation de HASHBYTES sur de grands sets XML Règles différentes pour traiter les valeurs dupliquées dans le tableau

TSQL: Comment get la list des groupes auxquels un user appartient dans Active Directory

J'ai deux requêtes qui récupèrent tous les groupes et tous les users d'un domaine, Mydomain 

 --; Get all groups in domain MyDomain select * from OpenQuery(ADSI, ' SELECT samaccountname,mail,sn,name, cn, objectCategory FROM ''LDAP://Mydomain/CN=users,DC=Mydomain,DC=com'' WHERE objectCategory=''group'' ORDER BY cn ') --; Get all users in domain MyDomain select * from OpenQuery(ADSI,' SELECT objectCategory, cn, sn, mail, name, department,samaccountname FROM ''LDAP://Mydomaindomain/CN=users,DC=Mydomain,DC=com'' WHERE objectCategory=''user'' ORDER BY cn ') -- where samaccountname='mylogin'

Ce que je voudrais savoir, c'est

Comment extraire une list de tous les groupes de MyDomain auxquels un user appartient?

[MISE À JOUR] J'ai été en mesure d'get le résultat inverse
Compte tenu du nom du groupe, récupérez tous les users 

 select * from OpenQuery(ADSI, 'SELECT objectCategory, cn, sn, mail, name, department FROM ''LDAP://Mydomain/CN=users,DC=wl-domain,DC=com'' WHERE MemberOf=''cn=_____GROUPNAME_____,CN=users,DC=Mydomain,DC=com'' ORDER BY cn' )

    Je pense que c'est l'une des limitations de l'interface AD ​​basée sur T-SQL – vous ne pouvez pas récupérer des attributes à valeurs multiples, par exemple des attributes (comme memberOf pour l'user) qui ont plus d'une valeur.

    Vous pouvez récupérer des attributes à valeur unique comme "sn" (nom de famille = nom) ou "givenName" et "mail" et ainsi de suite, mais l'interface SQL n'est pas capable de gérer des attributes comme "memberOf" avec plusieurs valeurs assignées pour eux.

    Donc, je crains que vous deviez aller d'une autre façon pour ce problème – par exemple find et remplir l'appartenance au groupe dans le code managé (séparément en dehors de SQL Server, ou éventuellement en tant qu'assemblage CLR dans SQL Server).

    UPDATE: voir ici (Support MSDN) pour une explication de la limitation du fournisseur OPENQUERY AD:

    Limites
    Le process d'utilisation de l'instruction OPENQUERY pour extraire des informations d'un server LDAP souffre de certaines limitations. Les limitations peuvent être contournées dans certains cas, mais dans d'autres, le design de l'application doit être modifié. Une application externe ou un object COM qui utilise ADSI pour récupérer les informations à partir du server LDAP, puis générer une table dans SQL en utilisant ADO ou d'autres methods d'access aux données est une autre méthode viable.

    La première limitation est que les propriétés à valeurs multiples ne peuvent pas être renvoyées dans le jeu de résultats à SQL Server. ADSI lit les informations de schéma du server LDAP qui définit la structure et la syntaxe des classs et des attributes utilisés par le server. Si l'atsortingbut demandé au server LDAP est défini dans le schéma comme étant multi-valué, il ne peut pas être renvoyé dans une instruction OPENQUERY.

    Procédure stockée ci-dessous, exécutez en utilisant l'exemple:

    Get_ADGroups_ForUser 'Beau.Holland' –AccountName

    Remarque: remplacez LDAP: // DC = Domain, DC = local avec votre propre domaine. 

     CREATE PROCEDURE dbo.Get_ADGroups_ForUser ( @Username NVARCHAR(256) ) AS BEGIN DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024) -- Find the fully qualified CN eg: CN=Beau Holland,OU=Users,OU=Australia,OU=NSO,OU=Company,DC=Domain,DC=local -- replace "LDAP://DC=Domain,DC=local" with your own domain SET @Query = ' SELECT @Path = distinguishedName FROM OPENQUERY(ADSI, '' SELECT distinguishedName FROM ''''LDAP://DC=Domain,DC=local'''' WHERE objectClass = ''''user'''' AND sAMAccountName = ''''' + @Username + ''''' '') ' EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT -- get all groups for a user -- replace "LDAP://DC=Domain,DC=local" with your own domain SET @Query = ' SELECT cn,AdsPath FROM OPENQUERY (ADSI, ''<LDAP://DC=Domain,DC=local>;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=' + @Path +'));cn, adspath;subtree'')' EXEC SP_EXECUTESQL @Query END GO

    Vous pouvez y parvenir en récupérant tous les groupes qui contiennent l'user dans leur atsortingbut de membre, ou mieux le path LDAP de l'user (distinguishedName). Voici une procédure simple faisant ce travail. 

     CREATE PROCEDURE dbo.GetLdapUserGroups ( @LdapUsername NVARCHAR(256) ) AS BEGIN DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024) SET @Query = ' SELECT @Path = distinguishedName FROM OPENQUERY(ADSI, '' SELECT distinguishedName FROM ''''LDAP://DC=domain,DC=com'''' WHERE objectClass = ''''user'''' AND sAMAccountName = ''''' + @LdapUsername + ''''' '') ' EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT SET @Query = ' SELECT name AS LdapGroup FROM OPENQUERY(ADSI,'' SELECT name FROM ''''LDAP://DC=domain,DC=com'''' WHERE objectClass=''''group'''' AND member=''''' + @Path + ''''' '') ORDER BY name ' EXEC SP_EXECUTESQL @Query END

    – Hilbert

    En fait, refind la list de tous les groupes auxquels un user appartient n'est pas aussi simple et facile qu'il n'y paraît. Autant que je sache, ni PowerShell ni d'autres scripts ne peuvent fournir des résultats complètement précis, même en récupérant l'atsortingbut tokenGroups, car pour faire cette détermination, il faut aussi considérer l'appartenance aux groupes embeddeds, qui sont spécifiques au domaine.

    Il y a un fil très utile sur ActiveDirSec.org que je pense que vous pourriez find utile – Comment énumérer la list de tous les groupes de security de domaine Active Directory auxquels un user appartient?

    Dans mon expérience, j'ai appris que ce n'est pas aussi facile que cela semble, et à less que vous ayez un moyen de vérifier la sortie à coup sûr, il n'y a pas moyen de savoir si votre script donne les bons résultats.

    Le Microsoft Technet Script Center est une excellente ressource pour les scripts

    http://technet.microsoft.com/en-us/scriptcenter/default.aspx

    Voici un script qui prétend donner exactement ce que vous voulez:

    http://gallery.technet.microsoft.com/ScriptCenter/en-us/ab5400e2-489a-4738-9b85-508bcb5b75f8