Thank you all for your replys.
I will use this one:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[DropOrphanUserAccounts]
as
BEGIN
set nocount on
create table #UserAccountsToDelete
(
ID int identity,
DB varchar(100),
[User] varchar(100)
)
DECLARE @DatabaseName nvarchar(100)
DECLARE my_DBs CURSOR FAST_FORWARD FOR
select [name] from master.sys.databases
WHERE [NAME] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ADVENTUREWORKS')
OPEN my_DBs
FETCH NEXT FROM my_DBs INTO @databasename
WHILE @@FETCH_STATUS = 0
Begin
insert #UserAccountsToDelete
exec('select '''+@databasename+''',sdp.name from '+@DatabaseName+'.sys.database_principals sdp
left join sys.server_principals ssp on sdp.sid = ssp.sid
where ssp.sid is null and sdp.type in (''S'',''U'',''G'')
and sdp.name not in (''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''BROKER_USER'', ''dbo'')')
FETCH NEXT FROM my_DBs INTO @databasename
END
CLOSE my_DBs
DEALLOCATE my_DBs
declare @contador int
declare @ciclo as int
declare @bd varchar(100)
declare @User varchar(100)
set @ciclo =1
set @contador =(select max(id) from #UserAccountsToDelete)
WHILE(@ciclo < = @contador )
begin
set @bd = (select db from #UserAccountsToDelete where id =@ciclo)
set @User = (select from #UserAccountsToDelete where id =@ciclo)
begin try
exec ('use '+@bd+ ' drop schema ['+@user+']')
end try
begin catch
end catch
exec ('use '+@bd+ ' drop user ['+@user+']')
set @ciclo =@ciclo +1
end
drop table #UserAccountsToDelete
END