After running the logins-report script (below) before an external audit, I wanted to ‘lock’ or ‘disable’ unused accounts for a month – just to confirm that they really were unused before deleting them 🙂
Unfortunatly SQL 2000 doesn’t have a facility to lock/disable sql accounts, so I changed the passwords to a known value. This looses the original p/w forever, but gives us more options than mearly deleting the accounts.
option-a inform the user of the new password.
option-b create a new account with the configuration of the ‘old’ account.
Windows logins (on the other hand) ARE lockable in SQL 2000. You use
exec sp_denylogin ‘domainuser’
and
exec sp_grantlogin ‘domainuser’
to unlock it (if required).
BTW: You need to use SQL 2000 Enterprise Manager to see locked Windows accounts, not SQL 2005/8 Management Studio.
UPDATE: watch-out for the situation where you disable a users individual account (as above), then make him a member of a group for access. Remember ‘Deny’ overrules ‘grant’.
Database Consultant 