FIX THE ORPHAN USERS OF DATABASE
Orphan Users are those users whose login exist in the SQL Server but mapping would have lost due to refresh of database.
When ever refresh of database is performed or restore of database on another SQL Server Instance chances of Orphan users arise.
Below Query will fix all the Orphan users at one go.
set nocount on
declare @Action varchar(10) -- '' (default) / UPDATE
set @Action=''
declare @ret int,
@cfixesupdate int, -- count of fixes by update
@loginsid varbinary(85),
@txtloginsid varchar(256),
@name sysname,
@binpwd varbinary(256),
@txtpwd sysname,
@tmpstr varchar (256),
@status int
-- SET INITIAL VALUES --
select @cfixesupdate = 0
-- CHECK PERMISSIONS --
if not is_member('db_owner') = 1
begin
raiserror(15247,-1,-1)
return
end
-- PREPARE CURSOR FOR SYSUSERS TABLE IN CURRENT DATABASE --
DECLARE login_curs CURSOR FOR
select [name], sid, [password] from sysusers
where issqluser = 1
and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
and [name] <> ('dbo')
order by [name]
OPEN login_curs
FETCH NEXT FROM login_curs INTO @name, @loginsid, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN
END
-- Create a temp table to hold any database login scripts that need to be generated
-- and manually run by the user.
if exists (select * from tempdb..sysobjects where name like '#sqlstmts%') drop table #sqlstmts
create table #sqlstmts
(
sqlid int,
sqlname varchar(50),
sqlstmt varchar(1000)
)
-- PROCESS ALL USERS IN CURSOR --
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @cfixesupdate = @cfixesupdate + 1 -- Increment the number of successfully repaired users.
-- NOTE: SYSLOGINS is a view for the SYSXLOGINS table.
set @ret = null -- init return value
select @ret = 1 from master.dbo.syslogins where
loginname = @name -- match login name
and isntname = 0 -- cannot use nt logins
--print 'User: ' + cast(@name as varchar(25)) + ' -- @RET: ' + cast(@ret as varchar(10))
if @ret is not null -- database login exists
begin
-- REPAIR THE USER LOGIN (SID) --
if upper(@Action) = 'UPDATE'
begin -- Attempt to repair the user login.
exec @ret = sp_change_users_login 'update_one', @name, @name
if @ret = 0
print @name + ': repaired '
else
print @name + ': failed '
end -- if UPDATE...
else -- Only generate the EXEC statements. Users must execute these statements manually.
begin
--print 'exec sp_change_users_login ''update_one'', ''' + @name + ''', ''' + @name + ''''
set @tmpstr = 'exec sp_change_users_login ''update_one'', ''' + @name + ''', ''' + @name + ''''
insert into #sqlstmts values (0, @name, @tmpstr)
end -- else
end -- If @ret is not null...
NextUser: -- Get the next user
FETCH NEXT FROM login_curs INTO @name, @loginsid, @binpwd
end
end
CLOSE login_curs
DEALLOCATE login_curs
print ''
print '---------------------------------------'
print 'Total users: ' + cast(@cfixesupdate as varchar(8))
print '---------------------------------------'
-- Dump the SQL statements created by this script.
select sqlid as 'Seq', sqlname as 'User', sqlstmt as 'Run Me' from #sqlstmts order by sqlid, sqlname
drop table #sqlstmts
GO
We have to run the above query in the database where restore or refresh is performed.
The above script will generate a query in 'Run Me' Column
Copy all consolidated script at once and execute in the database to fix the Orphan users
Thanks :)
