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:
And running
on the results.