469,613 Members | 2,223 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

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.
Jul 20 '05 #1
1 3075
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.

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Jon Jacobs | last post: by
2 posts views Thread by si.downes | last post: by
5 posts views Thread by chow.justy | last post: by
5 posts views Thread by haganahtrainer | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.