Category Archives: Orphan users Fixing

Find and FIX all the ORPHAN users in a Database


Description :

When you restore a db on a different machine, users are orphaned ie: there is no login id or password associated with the user (SID differs from machine to machine). This script finds and fixes all orphaned users in a restored database

How to use : Run the below script on the restored database to fix the Orphan Users.

 ————————————————————————————————————-

—  Script to generate the Actual Logins

SELECT

‘create login [‘

+ p.name +‘] ‘+

case

when p.type in(‘U’,‘G’)then‘from windows ‘elseend

+

‘with ‘

+

case

when p.type =‘S’then‘password = ‘+ master.sys.fn_varbintohexstr(l.password_hash)+‘ hashed, ‘

+

‘sid = ‘

+ master.sys.fn_varbintohexstr(l.sid)+‘, check_expiration = ‘

+

case

when l.is_policy_checked > 0 then‘ON, ‘else‘OFF, ‘end+‘check_policy = ‘+casewhen l.is_expiration_checked > 0 then‘ON, ‘else‘OFF, ‘end

+

case

when l.credential_id > 0 then‘credential = ‘+ c.name +‘, ‘elseend

else

end

+

‘default_database = ‘

+ p.default_database_name

+

case

whenlen(p.default_language_name)> 0 then‘, default_language = ‘+ p.default_language_name else

end

FROM

sys.server_principals p

LEFT

JOINsys.sql_logins l

ON

p.principal_id = l.principal_id

LEFT

JOINsys.credentials c

ON

l.credential_id = c.credential_id

WHERE

p.type in(‘S’,‘U’,‘G’

)

AND

p.name <>

‘sa’

 ————————————————————————————————————-

—  Auto Fix ORPHANED USERS

DECLARE

@UserName nvarchar(255)

DECLARE

orphanuser_cur cursorfor

SELECT

UserName =name

FROM

sysusers

WHERE

issqluser = 1 and(sidisnotnullandsid<> 0x0)andsuser_sname(sid)isnull

ORDER

BYname

–cannot translate sid to existing user=orphaned

OPEN

orphanuser_cur

FETCH

NEXTFROM orphanuser_cur INTO@UserName

WHILE

(@@fetch_status= 0)

BEGIN

PRINT

@UserName +‘ user name being resynced’

EXEC

sp_change_users_login‘Update_one’, @UserName,@UserName

FETCH

NEXTFROM orphanuser_cur INTO @UserName

END

CLOSE

orphanuser_cur

DEALLOCATE

orphanuser_cur

go

 ————————————————————————————————————-

%d bloggers like this: