Sql-server – SQL Server script to delete accounts no longer in Active Directory

loginsSecuritysql serversql-server-2000

We have a SQL Server 2000 that will shortly be migrated to SQL Server 2005. It has years of Windows Authentication accounts created that no longer exist in Active Directory, which prevent the Copy Database Wizard from creating these accounts on the new server.

Is there a script or some automated way of deleting the accounts that no longer exist in our Active Directory?

EDIT: Just to be clear, the logins needing to be deleted are on SQL Server 2000, which does not support the DROP LOGIN command.

Separately, manually deleting the logins in SQL Server 2000 would (I think) be done with exec sp_droplogin 'loginname' but on mine, the login name cannot be found, whether I use 'domain\loginname' or 'loginname'

Just to add to the confusion, exec sp_revokelogin 'domain\loginname' does appear to work.

EDIT 2: Finally resolved the issue. Many of the logons that were problematic were programmatically added to the database, and while they worked in the sense that a user could connect, the username vs NT login name had a mismatch of domain-prefixed logons when SQL Server expected no domain, and vice versa.

To resolve this, I modified the sp_droplogin procedure to take out one of the checks that was erroring.

I'm accepting my own answer as it works in SQL Server 2000.

Best Answer

What I ended doing is listing the accounts with:

    exec sp_validatelogins

And running

    exec sp_dropuser loginname
    exec sp_droplogin loginname

on the results.