There may be situations where DBA's get requests to drop a login. Right-click, drop login, done! But it leaves a lot of orphan users in the databases it had access to before dropping the login. In order to have a clean way of dropping the login, we need to drop the user from all the databases it exists in and then drop the login. Below is a simple script to do that.
EXEC master..sp_MSForeachdb 'USE [?] if exists (select 1 from sysusers where name=''domain\username'') drop user [domain\username]';
if exists(select 1 from master..syslogins where name='domain\username') drop login [domain\username];
It uses sp_msforeachdb to check and drop the user if it exists, finally drops the login if it exists. The same script can be used for dropping windows as well as sql logins..
EXEC master..sp_MSForeachdb 'USE [?] if exists (select 1 from sysusers where name=''domain\username'') drop user [domain\username]';
if exists(select 1 from master..syslogins where name='domain\username') drop login [domain\username];
It uses sp_msforeachdb to check and drop the user if it exists, finally drops the login if it exists. The same script can be used for dropping windows as well as sql logins..
No comments:
Post a Comment