/******************************************************************************
*                                                                             *
*    Procedure:    FixLogins.sql                                              *
*    Author:       Grant Scott                                                *
*    Date:         17 March 1999                                              *
*    Description:  This script is to be used to reattach users to their       *
*                  correct logins which is especially useful after restoring  *
*                  a DB from one server to another one. It reads through the  *
*                  SysUsers table and retrieves the users/groups of those     *
*                  that do match logins in the SysLogins table within Master. *
*                  It will then attempt to drop these users from the DB and   *
*                  then add them again.                                       *
*                  This script will not try to drop users which own objects   *
*                  within the DB nor those users which are attached to        *
*                  logins which own objects. To fix these users 2 scripts     *
*                  should be produced, the first to drop all the non-sa       *
*                  owned objects and a second to recreate them. The relevant  *
*                  data should then be saved through BCPing it or through use *
*                  of temp tables, the drop script run, this script run, the  *
*                  recreation script run, and the data BCPed or inserted      *
*                  back into the objects. Given time I will change the script *
*                  to perform these steps as well                             *
*    E-mail:       gruntus@ozemail.com.au                                     *
*                                                                             *
******************************************************************************/
 

	Declare
	@suid smallint,
	@name char(30),
	@grpname char(30),
	@sqlstmt char(50)
	
	Create Table #Logins
		(name char(30),
		grpname char(30))

	Declare CurLogins cursor for (select
		a.suid, a.name, b.name
		from 	SysUsers a, SysUsers b, Master..SysLogins c
		where 	a.suid <> 1
		and	a.suid = c.suid
		and 	a.name <> c.name
		and	a.gid = b.uid
		and	a.uid not in 	(select distinct uid 
					from 	SysObjects)
		and	a.name not in 	(select distinct f.name
					from 	SysUsers d, SysObjects e, Master..SysLogins f
					where	d.uid = e.uid
					and	d.suid = f.suid))
		for read only
	
	Declare CurAddLogins cursor for (select * from #Logins)

	Open CurLogins
	Fetch next from CurLogins into 
		@suid,
		@name,
		@grpname

	While (@@Fetch_status = 0)
	begin

		select 'Dropping User - ', @name

		exec sp_dropuser @name

		if @@Error = 0
			Insert into #Logins values(@name, @grpname)

		Fetch next from CurLogins into 
			@suid,
			@name,
			@grpname

	end

	Close CurLogins
	Deallocate CurLogins

	Open CurAddLogins
	Fetch next from CurAddLogins into
		@name,
		@grpname

	While (@@Fetch_status = 0)
	begin

		select 'Adding User - ', @name

		If exists (select * from Master..SysLogins where name = @name)
			exec sp_adduser @name, @name, @grpname
	
		Fetch next from CurAddLogins into
			@name,
			@grpname

	end

	Close CurAddLogins
	Deallocate CurAddLogins

GO