All

I’m stuck with a stored procedure. The target is very simple: give all members of a given Active Directory unless the account is set to inactive or the member is part of the group “Service Accounts”.  I also tried to generate code with CoPilot and ChatGPT but these AI-tools are neither able to retrieve “working code”.

CREATE PROCEDURE GetActiveAccountsFromGroup
@GroupName NVARCHAR(256)
AS
BEGIN
— Declare a table to store the results
DECLARE @Results TABLE (
AccountName NVARCHAR(256)
);

— Query to get members of the specified group
INSERT INTO @Results (AccountName)
SELECT sAMAccountName
FROM OPENQUERY(ADSI, ‘
SELECT sAMAccountName
FROM ”LDAP://DC=yourdomain,DC=com”
WHERE objectClass = ”user”
AND memberOf = ”CN=’ + @GroupName + ‘,OU=Groups,DC=yourdomain,DC=com”
AND userAccountControl = 512 — Active accounts
‘);

— Remove accounts that are also in the “Service Accounts” group
DELETE FROM @Results
WHERE AccountName IN (
SELECT sAMAccountName
FROM OPENQUERY(ADSI, ‘
SELECT sAMAccountName
FROM ”LDAP://DC=yourdomain,DC=com”
WHERE objectClass = ”user”
AND memberOf = ”CN=Service Accounts,OU=Groups,DC=yourdomain,DC=com”
‘)
);

— Return the results
SELECT * FROM @Results;
END;

​AllI’m stuck with a stored procedure. The target is very simple: give all members of a given Active Directory unless the account is set to inactive or the member is part of the group “Service Accounts”.  I also tried to generate code with CoPilot and ChatGPT but these AI-tools are neither able to retrieve “working code”.CREATE PROCEDURE GetActiveAccountsFromGroup
@GroupName NVARCHAR(256)
AS
BEGIN
— Declare a table to store the results
DECLARE @Results TABLE (
AccountName NVARCHAR(256)
);

— Query to get members of the specified group
INSERT INTO @Results (AccountName)
SELECT sAMAccountName
FROM OPENQUERY(ADSI, ‘
SELECT sAMAccountName
FROM ”LDAP://DC=yourdomain,DC=com”
WHERE objectClass = ”user”
AND memberOf = ”CN=’ + @GroupName + ‘,OU=Groups,DC=yourdomain,DC=com”
AND userAccountControl = 512 — Active accounts
‘);

— Remove accounts that are also in the “Service Accounts” group
DELETE FROM @Results
WHERE AccountName IN (
SELECT sAMAccountName
FROM OPENQUERY(ADSI, ‘
SELECT sAMAccountName
FROM ”LDAP://DC=yourdomain,DC=com”
WHERE objectClass = ”user”
AND memberOf = ”CN=Service Accounts,OU=Groups,DC=yourdomain,DC=com”
‘)
);

— Return the results
SELECT * FROM @Results;
END;   Read More

​