if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoneUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LoneUser]
GO

CREATE PROCEDURE [LoneUser]
--INPUT
@DBName             nvarchar(50),
@UserName           nvarchar(50)
AS
    Exec sp_configure 'allow updates','1'
    RECONFIGURE WITH OVERRIDE 
    
    Declare @ExecStr nvarchar(4000)
   
    Select @ExecStr = ' Declare @b varbinary(85) '
                    + ' Use Master'
                    + ' Select @b = sid From syslogins Where Name = ''' + @UserName + ''''
                    + ' Use ' + @DBName
                    + ' Update sysusers Set sid = @b Where name = ''' + @UserName + ''''

    --Print @ExecStr
    Exec(@ExecStr)

    Exec sp_configure 'allow updates','0'
    RECONFIGURE WITH OVERRIDE

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fix_all_orphan_user]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[fix_all_orphan_user]
GO

create procedure fix_all_orphan_user
/***************************************************************************************/
-- 目    的: 生成当前服务器下所有的库内需执行修复孤立用户的脚本 (调用了子过程LoneUser)
--
-- Written By:  余枫  maggiefengyu@tom.com --- 08/29/2006
/***************************************************************************************/
as
Declare 
        @intCountProperties 		int,
        @intCounter 			int,
        @db_name 			varchar(80),
        @sql_commands			varchar(200),
        @sqlstr				varchar(500)
begin
        	
-- 生成所有数据库的临时表

	create table #all_database (
	id int identity(1,1),
	db_name sysname)
	
	IF @@ERROR<>0 RETURN @@ERROR

-- 把所有数据库的信息插入	
	insert into #all_database (db_name) select name from master.dbo.sysdatabases

-- 生成存放fix全部孤立用户的SQL的临时表	
	create table #tmp_fix_commands (
	id int identity(1,1),
	sql_commands varchar(200))

	IF @@ERROR<>0 RETURN @@ERROR
	
-- 找到临时表的记录数
	select 	@intCountProperties = Count(*),@intCounter = 1
	from #all_database

--select 	@intCountProperties

	IF @@ERROR<>0 RETURN @@ERROR	

-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
	select 	@db_name = convert(varchar(50),db_name)
		from #all_database where Id = @intCounter
	
       set @sqlstr='insert into #tmp_fix_commands(sql_commands) select ''exec LoneUser  '+@db_name+', ''+name+'''''+
	' from '+@db_name+'.dbo.sysusers where issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null'

 --     select @sqlstr

	exec(@sqlstr)
        		
-- 循环指针下移
	set @intCounter = @intCounter + 1
end
-- 循环结束

-- 把生成的修复所有孤立用户的SQL语句显示出来
	select * from #tmp_fix_commands

	IF @@ERROR<>0 RETURN @@ERROR

-- 执行修复孤立用户的SQL语句	 
-- 找到临时表的记录数
	select 	@intCountProperties = Count(*),@intCounter = 1
	from #tmp_fix_commands

--select 	@intCountProperties

	IF @@ERROR<>0 RETURN @@ERROR	

-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
	select 	@sql_commands = sql_commands
		from #tmp_fix_commands where Id = @intCounter
	
 --     select @sql_commands

	exec(@sql_commands)
        		
-- 循环指针下移
	set @intCounter = @intCounter + 1
end
-- 循环结束	
	
end