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.