To Drop All Orphan Users or Delete All Orphan U

--Drop all orphaned users from custom SP 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

----------------------------------------------------------------------------------------------------
--To delete orphaned users from given DB (DB_Name)
USE [databasename]
GO
EXEC sp_Drop_OrphanedUsers;
go
------------------------------------------------------------------------------------------------------
--To delete orphaned users from all databases
USE [master]
GO
EXEC sp_msforeachdb 'USE [?]; EXEC sp_Drop_OrphanedUsers'
go