- -------------------------------------------------------
- -- La vue
- -------------------------------------------------------
- CREATE VIEW dbo.ListActiveDirectoryUsers
- AS
-
- SELECT
- ADsPath AS ADsPath,
- title AS TITRE,
- givenName AS PRENOM,
- sn AS NOM,
- displayName,
- sAMAccountName AS LOGIN,
- telephoneNumber AS TELEPHONE,
- facsimileTelephoneNumber AS FAX,
- mobile AS MOBILE
-
- FROM OpenQuery(MONSERVEURLIEAD,
- 'SELECT ADsPath, title, displayName, sAMAccountName,
- givenName, telephoneNumber, facsimileTelephoneNumber, sn, mobile
- FROM ''LDAP://DC=ServerControler,DC=Domain,DC=net''
- where objectClass = ''User'' AND objectCategory = ''Person''')
-
- GO
-
- -------------------------------------------------------
- -- La procédure stockée
- -------------------------------------------------------
- CREATE PROCEDURE dbo.ListeUsersInGroup
- @GroupName as VARCHAR(500)
- AS
-
- DECLARE @PathAD AS VARCHAR(1000)
- DECLARE @SQL AS VARCHAR(5000)
-
- SELECT
- @PathAD = REPLACE(ADsPath, 'LDAP://', '')
- FROM
- dbo.ListActiveDirectoryGroups
- WHERE
- NomGroup = @GroupName
-
- SET @SQL = 'SELECT USR.* FROM OpenQuery(MONSERVEURLIEAD, '
- SET @SQL = @SQL +'''SELECT sAMAccountName, Adspath '
- SET @SQL = @SQL +'FROM ''''LDAP://DC=ServerControler,DC=Domain,DC=net'''' '
- SET @SQL = @SQL +'where memberOf = '''''+ @PathAD +''''''') AD '
- SET @SQL = @SQL +' INNER JOIN dbo.ListActiveDirectoryUsers USR '
- SET @SQL = @SQL +' ON AD.Adspath = USR.ADsPath'
-
- EXECUTE (@SQL)
-
- GO
-
- -------------------------------------------------------
- -- Utilisation de la procédure :
- -------------------------------------------------------
- EXEC dbo.ListeUsersInGroup 'GroupeNTAtester'
-------------------------------------------------------
-- La vue
-------------------------------------------------------
CREATE VIEW dbo.ListActiveDirectoryUsers
AS
SELECT
ADsPath AS ADsPath,
title AS TITRE,
givenName AS PRENOM,
sn AS NOM,
displayName,
sAMAccountName AS LOGIN,
telephoneNumber AS TELEPHONE,
facsimileTelephoneNumber AS FAX,
mobile AS MOBILE
FROM OpenQuery(MONSERVEURLIEAD,
'SELECT ADsPath, title, displayName, sAMAccountName,
givenName, telephoneNumber, facsimileTelephoneNumber, sn, mobile
FROM ''LDAP://DC=ServerControler,DC=Domain,DC=net''
where objectClass = ''User'' AND objectCategory = ''Person''')
GO
-------------------------------------------------------
-- La procédure stockée
-------------------------------------------------------
CREATE PROCEDURE dbo.ListeUsersInGroup
@GroupName as VARCHAR(500)
AS
DECLARE @PathAD AS VARCHAR(1000)
DECLARE @SQL AS VARCHAR(5000)
SELECT
@PathAD = REPLACE(ADsPath, 'LDAP://', '')
FROM
dbo.ListActiveDirectoryGroups
WHERE
NomGroup = @GroupName
SET @SQL = 'SELECT USR.* FROM OpenQuery(MONSERVEURLIEAD, '
SET @SQL = @SQL +'''SELECT sAMAccountName, Adspath '
SET @SQL = @SQL +'FROM ''''LDAP://DC=ServerControler,DC=Domain,DC=net'''' '
SET @SQL = @SQL +'where memberOf = '''''+ @PathAD +''''''') AD '
SET @SQL = @SQL +' INNER JOIN dbo.ListActiveDirectoryUsers USR '
SET @SQL = @SQL +' ON AD.Adspath = USR.ADsPath'
EXECUTE (@SQL)
GO
-------------------------------------------------------
-- Utilisation de la procédure :
-------------------------------------------------------
EXEC dbo.ListeUsersInGroup 'GroupeNTAtester'