SQL Server Performance

User archival - Active Directory vs SQL Logins

Discussion in 'SQL Server 2008 General DBA Questions' started by darkangelBDF, Nov 16, 2009.

  1. darkangelBDF Member

    Hi there all
    I am wondering if someone could help me or perhaps point me in the right direction. My manager gave me a script where it compares Windows logins in SQL with entries in AD to see if there are any users in SQL that are not in AD anymore. These logins are then removed from SQL. The problem is after a while his script bombs out and throws a memory exception error. I was wondering if there is any other way to write the script that would ensure successful execution? I post the script below:
    DECLARE@Samaccount
    varchar(100),
    @name varchar(100),
    @SQlstring varchar(8000);DROP
    TABLE #usersCREATE
    TABLE #users
    (
    UserName varchar(100)
    )DECLARE
    usercursor CURSOR FORSELECT
    RTRIM(SUBSTRING(Name,11,99)) AS
    samaccountname,
    name
    FROM
    master.dbo.syslogins
    WHERE
    isntname = 1 AND
    name LIKE 'DomainName%' AND
    isNTGroup = 0 OPEN
    usercursorFETCH NEXT FROM usercursor
    INTO @Samaccount, @name WHILE
    @@FETCH_STATUS = 0
    BEGIN
    SELECT
    @SQlString = 'SELECT * FROM OPENQUERY(ADSI, ''SELECT SAMAccountName FROM ''''LDAP://DC=<DCName>, DC=<DC>'''' WHERE SAMAccountName = ''''' + @Samaccount +''''''' )'
    -- Select @SQlstring
    EXEC (@sqlstring)IF @@rowcount = 0
    INSERT INTO #users VALUES (@Name)
    --Select 'Drop Login [' + @name + ']'
    FETCH NEXT FROM usercursor
    INTO @Samaccount, @name
    ENDCLOSE
    usercursor
    DEALLOCATE usercursorSELECT
    '
    Drop Login [' + Username + ']'
    FROM #users
  2. satya Moderator

  3. darkangelBDF Member

    Thank you so much for your response, I'll definitely follow this up.
  4. satya Moderator

    Welcome, but you haven't answered my queries up there.
  5. darkangelBDF Member

    I'm not sure I understand your question correctly. I have sufficient permissions to run this as I tested it with 100% success on our Development server. But when I run it in production, after about 222 entries into the temp table, it fails.

Share This Page