restore from different server - user (sid) does not match restored db - fix with sp_changeobjectowner

[Jol]

This is more of a fyi than a question.

After restoring a live db backup to our development server there was
an identically named login Melb02 on both the db server and in the
restored db backup.
However if you log in as Melb02 on the server you cannot access any
objects owned by Melb02 on the restored db, without putting
Melb02.<object_name> in front. This would break our app so we needed
a workarround.
The mssql docs say that first check if the login is the owner, then
dbo then deny access - something to that effect. So this was
confusing.

workarround
I think what happened was that the db restored from the live remote
server had a different security id (sid) to the development server
although both users had the same name: Melb02. Thus if you log in as
Melb02 you can't get access to Melb02 objects on the restored db
because of the different sid.

we wrote a cursor that went though everything in sysobjects belonging
to Melb02 and did a : sp_changeobjectowner to a new user login that
our app now happily uses.
Jol.

[John Bell]

Hi

Read http://support.microsoft.com/default...;en-us;Q314546
regarding moving databases. If you are not moving the login then you will
need to resolve the orphaned users.

John

"Jol" <jo********@spcm.com.au> wrote in message
news:b2*************************@posting.google.co m...

This is more of a fyi than a question.

After restoring a live db backup to our development server there was
an identically named login Melb02 on both the db server and in the
restored db backup.
However if you log in as Melb02 on the server you cannot access any
objects owned by Melb02 on the restored db, without putting
Melb02.<object_name> in front. This would break our app so we needed
a workarround.
The mssql docs say that first check if the login is the owner, then
dbo then deny access - something to that effect. So this was
confusing.

workarround
I think what happened was that the db restored from the live remote
server had a different security id (sid) to the development server
although both users had the same name: Melb02. Thus if you log in as
Melb02 you can't get access to Melb02 objects on the restored db
because of the different sid.

we wrote a cursor that went though everything in sysobjects belonging
to Melb02 and did a : sp_changeobjectowner to a new user login that
our app now happily uses.
Jol.