hahahaha that is amusing, I did choose a rather silly name!
Here is the content for you:
The code is pretty straight forward when you think about it. Obviously the first thing to do is to add a linked server if you haven’t already. This doesn’t require any changes to run
EXEC sp_Addlinkedserver @server= N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource' |
Then you can set up a really quite straight forward cursor to load the data into a table. The reason we need the cursor is we can only query one AD group at a time.
--create procedure test.ad_qlik_groups_Test as
IF OBJECT_ID('tempdb.dbo.#tblqlikgroups', 'U') IS NOT NULL DROP TABLE #tblqlikgroups;
IF OBJECT_ID('tempdb.dbo.#test', 'U') IS NOT NULL DROP TABLE #test;
IF OBJECT_ID('dbo.active_directory_qlikview_users_and_groups', 'U') IS NOT NULL TRUNCATE TABLE active_directory_qlikview_users_and_groups;
--get list of AD groups for Qlik
SELECT
REPLACE(ADsPath,'LDAP://','') AS groupPath ,
LEFT(REPLACE(ADsPath,'LDAP://CN=',''), charindex(',', REPLACE(ADsPath,'LDAP://CN=','')) - 1) AS groupname
INTO #tblqlikgroups
FROM OpenQuery (
ADSI,
'SELECT displayname,
samAccountName,
whenCreated ,
ADsPath
FROM ''LDAP://DC=yourdomain,DC=co,dc=uk''
WHERE objectClass = ''Group''
') AS tblADSI
WHERE samaccountname LIKE'%Qlik%' --this is where we tell the system what groups we want to call back
-- declare the variable to pass to the cursor
DECLARE @grouppath VARCHAR(MAX), @groupname VARCHAR (MAX)
-- declare the cursor and tell it what we want to use for our loop
DECLARE active_directory_users_and_groups cursor FOR
SELECT grouppath, groupname
FROM #tblqlikgroups
--open the cursor and find the first row
OPEN active_directory_users_and_groups
fetch NEXT FROM active_directory_users_and_groups INTO @grouppath, @groupname
--now just loop through until there are no more items to fetch from the table
while @@FETCH_STATUS = 0
BEGIN
DECLARE @q VARCHAR(MAX)
SET @q='INSERT into active_directory_qlikview_users_and_groups
SELECT DISTINCT '''+@groupname+''' as groupName, displayname, samAccountName,lastLogon,mail
FROM OpenQuery (
ADSI,
''SELECT displayName,
samAccountName,
lastLogon ,
whenCreated,
mail
FROM ''''LDAP://DC=yourdomain,DC=co,dc=uk''''
WHERE objectClass = ''''Person'''' and memberOf='''''+@grouppath+'''''
'') AS tblADSI '
--print (@q) --use this to test the code, comment out when executing
EXEC(@q)
fetch NEXT FROM active_directory_users_and_groups INTO @grouppath, @groupname
END
--close and deallocate the cursor
close active_directory_users_and_groups
deallocate active_directory_users_and_groups
. Anyway I'll go home and check in my laptop. 
