create proc usp_remove_orphan_users as 

-- Written by: Gregory A. Larsen  
-- Script to modify database owner, and remove all users that 
-- are not mapped to logins.

set nocount on

-- Section 1: Create temporary table to hold databases to process 

-- drop table if it already exists
if (select object_id('tempdb..##dbnames')) is not null
  drop table ##dbnames

-- Create table to hold databases to process
create table ##dbnames (dbname varchar(128))

-- Section 2: Determine what databases have orphan users
exec master.dbo.sp_MSforeachdb 'insert into ##dbnames select ''?'' from master..syslogins l right join ?..sysusers u
on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1 
and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' and u.name <> ''system_function_schema'') 
having count(*) > 0'

-- Section 3: Create local variables needed
declare @CNT int
declare @name char(128)
declare @sid  varbinary(85)
declare @cmd nchar(4000)
declare @c int
declare @hexnum char(100)
declare @db varchar(100) 

-- Section 5: Process through each database and remove orphan users
select @cnt=count(*) from ##DBNAMES
While @CNT > 0
begin

-- get the name of the top database
  select top 1 @db=dbname from ##DBNAMES

-- delete top database 
  delete from ##DBNAMES where dbname = @db

-- Build and execute command to determine if DBO is not mapped to login
  set @cmd = 'select @cnt = count(*) from master..syslogins l right join ' + 
             rtrim(@db) + '..sysusers u on l.sid = u.sid' + 
             ' where l.sid is null and u.name = ''DBO'''
  exec sp_executesql @cmd,N'@cnt int out',@cnt out

-- if DB is not mapped to login that exists map DBO to SA
  if @cnt = 1
  begin
    print 'exec ' + @db + '..sp_changedbowner ''SA'''  
    -- exec sp_changedbowner 'SA'
  end -- if @cnt = 1


-- drop table if it already exists
if (select object_id('tempdb..##orphans')) is not null
  drop table ##orphans

-- Create table to hold orphan users
create table ##orphans (orphan varchar(128))

-- Build and execute command to get list of all orphan users (Windows and SQL Server)
-- for current database being processed
   set @cmd = 'insert into ##orphans select u.name from master..syslogins l right join ' + 
              rtrim(@db) + '..sysusers u on l.sid = u.sid ' + 
              'where l.sid is null and issqlrole <> 1 and isapprole <> 1 ' +  
              'and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' ' +  
              'and u.name <> ''system_function_schema'')'
   exec (@cmd)


-- Are there orphans
  select @cnt = count(*) from ##orphans
  
  WHILE @cnt > 0 
  BEGIN
  
-- get top orphan
  select top 1 @name= orphan from ##orphans

-- delete top orphan
  delete from ##orphans where orphan = @name

-- Build command to drop user from database.
    set @cmd = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + ''''
    print @cmd
    --exec (@cmd)

   
-- are there orphans left
    select @cnt = count(*) from ##orphans  
  end --  WHILE @cnt > 0


-- are the still databases to process
select @cnt=count(*) from ##dbnames

end -- while @cnt > 0

-- Remove temporary tables
drop table ##dbnames, ##orphans