set nocount on
go
DECLARE @databasename as varchar(200)
declare @strSQL as nvarchar (4000)
DECLARE Curse CURSOR local fast_forward
FOR
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW')
OPEN Curse
FETCH next FROM Curse INTO @databasename
WHILE @@fetch_status = 0
BEGIN
SET @strSQL = 'USE '+ @databasename + '
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
IF exists(select * FROM master..syslogins WHERE [name] = @usrname)
select @command='' sp_change_users_login auto_fix, '' + @usrname
print @command
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs'
EXEC dbo.sp_executesql @strSQL
fetch next from Curse into @databasename
end
close Curse
deallocate Curse
