SQL Server drop orphaned users

 use [master]

go

create proc dbo.sp_Drop_OrphanedUsers

as

begin

 set nocount on

 -- get orphaned users  

 declare @user varchar(max) 

 declare c_orphaned_user cursor for 

  select name

  from sys.database_principals 

  where type in ('G','S','U') 

  and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"

  and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') ) 

  and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')  open c_orphaned_user 

 fetch next from c_orphaned_user into @user

 while(@@FETCH_STATUS=0)

 begin

  -- alter schemas for user 

  declare @schema_name varchar(max) 

  declare c_schema cursor for 

   select name from  sys.schemas where USER_NAME(principal_id)=@user

  open c_schema 

  fetch next from c_schema into @schema_name

  while (@@FETCH_STATUS=0)

  begin

   declare @sql_schema varchar(max)

   select @sql_schema='ALTER AUTHORIZATION ON SCHEMA::['+@schema_name+ '] TO [dbo]'

   print @sql_schema

   exec(@sql_schema)

   fetch next from c_schema into @schema_name

  end

  close c_schema

  deallocate c_schema   

  

  -- alter roles for user 

  declare @dp_name varchar(max) 

  declare c_database_principal cursor for 

   select name from sys.database_principals

   where type='R' and user_name(owning_principal_id)=@user

  open c_database_principal

  fetch next from c_database_principal into @dp_name

  while (@@FETCH_STATUS=0)

  begin

   declare @sql_database_principal  varchar(max)

   select @sql_database_principal ='ALTER AUTHORIZATION ON ROLE::['+@dp_name+ '] TO [dbo]'

   print @sql_database_principal 

   exec(@sql_database_principal )

   fetch next from c_database_principal into @dp_name

  end

  close c_database_principal

  deallocate c_database_principal

    

  -- drop roles for user 

  declare @role_name varchar(max) 

  declare c_role cursor for 

   select dp.name--,USER_NAME(member_principal_id)

   from sys.database_role_members drm

   inner join sys.database_principals dp 

   on dp.principal_id= drm.role_principal_id

   where USER_NAME(member_principal_id)=@user 

  open c_role 

  fetch next from c_role into @role_name

  while (@@FETCH_STATUS=0)

  begin

   declare @sql_role varchar(max)

   select @sql_role='EXEC sp_droprolemember N'''+@role_name+''', N'''+@user+''''

   print @sql_role

   exec (@sql_role)

   fetch next from c_role into @role_name

  end

  close c_role

  deallocate c_role   

      

  -- drop user

  declare @sql_user varchar(max)

  set @sql_user='DROP USER ['+@user +']'

  print @sql_user

  exec (@sql_user)

  fetch next from c_orphaned_user into @user

 end

 close c_orphaned_user

 deallocate c_orphaned_user

 set nocount off

end

go

-- mark stored procedure as a system stored procedure

exec sys.sp_MS_marksystemobject sp_Drop_OrphanedUsers

go

--USE [MyTestDB]

--GO

--EXEC sp_Drop_OrphanedUsers