Search This Blog

SQL Script to find the members of a AD user group

The following script lists all the users on all the Active Directory groups using the extended stored procedure xp_LoginInfo;

DECLARE @LoginName sysname
DECLARE @sql NVARCHAR (2000)

BEGIN
   DECLARE cur_Loginfetch CURSOR FOR
SELECT [name]
FROM master.sys.server_principals
WHERE TYPE = 'G'
AND [name] NOT LIKE 'NT SERVICE%'
   OPEN cur_Loginfetch
   FETCH NEXT FROM cur_Loginfetch INTO @LoginName
   WHILE @@FETCH_STATUS = 0
       BEGIN
         bEgin try
           EXEC xp_logininfo @LoginName , 'members'
         END TRY
         BEGIN CATCH
            Print ERROR_MESSAGE()
         END CATCH
           FETCH NEXT FROM cur_Loginfetch INTO @LoginName
       END
   CLOSE cur_Loginfetch
   DEALLOCATE cur_Loginfetch
   RETURN
END

No comments: