Delete All Database User Accounts for a Given Server Login

/*===================================================================================================
2013/07/15 hakim.ali@SQLzen.com

SQL Server 2005 and higher.

Script to delete (drop) all database level user accounts for a given server login from all 
databases on a database server. This will drop users even if they have a different name from 
the server login, so long as the two are associated. This will not drop users from a database 
where there are schemas/roles owned by the user.

** USE ONLY IF YOU ARE AN EXPERIENCED DBA FAMILIAR WITH SERVER LOGINS, DATABASE USERS, ROLES, 
SCHEMAS ETC. USE AT YOUR OWN RISK. BACK UP ALL DATABASES BEFORE RUNNING. **
=====================================================================================================*/use [master]
go

--------------------------------------------------------------------------------------
-- Set the login name here for which you want to delete all database user accounts.
declare @LoginName nvarchar(200); set @LoginName = 'LOGIN_NAME_HERE'
--------------------------------------------------------------------------------------

declare @counter int
declare @sql nvarchar(1000)
declare @dbname nvarchar(200)

-- To allow for repeated running of this script in one session (for separate logins).
begin try drop table #DBUsers end try begin catch end catch

----------------------------------------------------------
-- Temp table to hold database user names for the login.
----------------------------------------------------------
create table #DBUsers
(IDint identity(1,1)
,LoginNamevarchar(200)
,DBvarchar(200)
,UserNamevarchar(200)
,Deletedbit
)

-- Add all user databases.
insert into #DBUsers
(LoginName
,DB
,Deleted
)
select@LoginName
,name
,1
fromsys.databases
wherename not in ('master','tempdb','model','msdb')
andis_read_only = 0
and[state] = 0 -- online
order byname

----------------------------------------------------------
-- Add database level users (if they exist) for the login.
----------------------------------------------------------
set @counter = (select min(ID) from #DBUsers)

while exists (select 1 from #DBUsers where ID >= @counter)
begin
set @dbname = (select db from #DBUsers where ID = @counter)
set @sql = '
updatetemp
settemp.UserName = users.name
fromsys.server_principalsas logins
inner join[' + @dbname + '].sys.database_principalsas users
on users.sid = logins.sid
and logins.name = ''' + @LoginName + '''
inner join#DBUsersas temp
on temp.DB = ''' + @dbname + ''''

exec sp_executesql @sql

set @counter = @counter + 1
end

-- Don't need databases where a login-corresponding user was not found.
delete#DBUsers
whereUserName is null

----------------------------------------------------------
-- Now drop the users.
----------------------------------------------------------
set @counter = (select min(ID) from #DBUsers)

while exists (select 1 from #DBUsers where ID >= @counter)
begin
select@sql = 'use [' + DB + ']; drop user [' + UserName + ']'
from#DBUsers
whereID = @counter

--select @sql
begin try exec sp_executesql @sql end try begin catch end catch
set @counter = @counter + 1
end

----------------------------------------------------------
-- Report on which users were/were not dropped.
----------------------------------------------------------
set @counter = (select min(ID) from #DBUsers)

while exists (select 1 from #DBUsers where ID >= @counter)
begin
set @dbname = (select db from #DBUsers where ID = @counter)
set @sql = '
updatetemp
settemp.Deleted = 0
fromsys.server_principalsas logins
inner join[' + @dbname + '].sys.database_principalsas users
on users.sid = logins.sid
and logins.name = ''' + @LoginName + '''
inner join#DBUsersas temp
on temp.DB = ''' + @dbname + ''''

exec sp_executesql @sql

set @counter = @counter + 1
end

-- This shows the users that were/were not dropped, and the database they belong to.
if exists (select 1 from #DBUsers)
begin
selectLoginName
,[Database]= DB
,UserName= UserName
,Deleted= case Deleted when 1 then 'Yes' else 'No !!!!!!' end
from#DBUsers
order byDB
end
else
begin
select [No Users Found] = 'No database-level users found on any database for the login "' + @LoginName + '".'
end

/*===================================================================================================
Not automatically dropping the login. If there are database level users that were not dropped, 
dropping the login will create orphaned users. Enable at your discretion.
=====================================================================================================*//*
set @sql = 'drop login [' + @LoginName + ']'
exec sp_executesql @sql
*/