473,395 Members | 1,521 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

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 3212
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Rene | last post by:
Hi, While restoring the log we receive a message after about 30 minutes: Processed 1152109 pages for database 'restest', file 'sales_Log' on file 1 Server: Msg 9004, Leverl 21, State 20, Line...
1
by: Hai-Chu Hsu | last post by:
Hi, I want to restore my databases from the old SQL Server installation into a new installation of SQL Server. My new installation of SQL Server has different data path from the old...
2
by: SD | last post by:
Hi, Quick question, I have about 20 users in my local server and database. We are looking to restore that database to a new server in a new network and still be able to retain the database...
0
by: xo55ox | last post by:
Hi, I have been trying to set up an automated restore process from prod to backup server. First, I schedule the full database backup nightly, transfer the backup file and restore it to the...
13
by: | last post by:
Hi I am trying to copy a 20 GB database from server 1 to 2. I took a full backup, copied to server2 and restored ( ready for use) using EM. After the message restore completed, I see the...
3
by: Jon Jacobs | last post by:
I attempt to back up a database on one server and restore it on my local machine. This is what the query text looks like: restore database model from Disk='c:\JQJ\mydump\model.bak' with...
2
by: si.downes | last post by:
Using SQL Server 2000 SP3 I'm developing a data warehouse where data will be archived off to a filegroup, this filegroup backed up and the tables in this filegroup truncated to free up space on...
5
by: chow.justy | last post by:
Dear all, I'm a new beginner of DB2. I face 2 question during restore the data. I have 2 DB2 servers on my company. Server A is running on V7.2 and Server B is running on v8.2 (Enterprise...
5
by: haganahtrainer | last post by:
I have a database backup file that was created from an AIX 64 bit instance - when I ry to restore this I get a message SQL2570N, A database cannot be restored on a platform that does not match the...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.