April 9, 2009 at 3:01 pm
i have to make a stored procedure where i have to fix the orphan users by giving database name as input parameter. I have tried but it doesent work. My code is mentioned below.Thanks in advance for the replies
Aashish
create proc usp_fixorphan
(@dbname varchar(20))
as
declare @username varchar(255),@sql nvarchar(200),@dbid int
declare db cursor for select name,dbid from master..sysdatabases
order by dbid desc
open db
fetch next from db into @dbname,@dbid
while(@@fetch_status=0)
begin
SET @sql = 'USE ' + @dbname
exec (@sql)
declare orphan cursor for
select name from sysusers
where sid is not null
AND sid <> 0x01
open orphan
fetch next from orphan into @username
while(@@fetch_status=0)
begin
if exists (select * from master..sysxlogins where name = @username)
begin
exec sp_change_users_login 'update_one',@username,@username
end
else
exec sp_change_users_login 'auto_fix', @username
fetch next from orphan into @username
end
close orphan
deallocate orphan
end
close db
deallocate db
go
April 10, 2009 at 6:58 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply