Orphaned User

Question

Orphaned User

beejug1983

SSCertifiable

Points: 6491
More actions
August 26, 2010 at 12:11 am

#237234

Hi,
I have a database 'db1' in SQL 2000 with a user name 'ABC' with password 'ABC', there is an another server of SQL 2005 with database 'db2005' with user name 'ABC' with password 'ABC', I restore the 'db1' backup on SQL 2005. Now what will happen with orphaned user. a database user with same name in SQL 2005 is already exist.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

whenevr we restore any DB ,basically we lose the mapping between login and users which is done by column "sid" , So this columns values should be matched.

select * from sysusers

select name, * from sys.syslogins

So that should be mapped with sp_change_users_login 'auto_fix', 'username'

go

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

pankushmehta Ten Centuries Points: 1217 More actions · Answer 2

SQL users are mapped via SIDs which are varbinary. to have the same sid in 2 different servers for same login name is unlikely. Therefore, when you move the databases, the database level user SID does not match the SID in the server level.

To fix it, you can use many options.

alter user ABC with login = ABC

or

sp_change_users_login 'Update_One', 'RBeacon'

ananthbhaskar Right there with Babe Points: 783 More actions · Answer 3

since both user names and pwds are same,

if all the above things doesnt work, then simply drop the user from the newly created DB and recreate the user with the same name 'ABC' and associate with the login in that server.