Solved

Change Database Ownership from a restored database

Posted on 2013-11-01
4
258 Views
Last Modified: 2014-09-24
I have a MS SQL 2000 server that I took the backup databases from, and restored them to a new and differently named MS SQL 2008 R2 server.

The restore went fine, and I can browse the tables and run queries.

I am installing an ERP Database Server Software (Infor Syteline 8.03) in a linked multi-site environment.
When I got to link the databases, I get the following error.

"Failed to link the database as multi-site:
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not find server 'OLD SERVER NAME' in sys.servers. Verify thath the correct server name was specified. If necessary, execute the stored procedure SP_addlinkedserver to add the server to sys.servers.
[Microsoft][ODBC SQL Server Driver][SQL Server] The statement has been terminated."

I guess I am looking for a Query to change "OLD SERVER NAME" to "NEW SERVER NAME"

The stored procedure sp_addserver 'NEW SERVER NAME', 'local'; will not work because it says the server already exists.

I did not migrate the master database to this server. could that be the issue.
0
Comment
Question by:Moordoom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39617183
This will change it to SA but you can replace that with the owner you want:

exec sp_changedbowner 'sa'
ALTER AUTHORIZATION ON SCHEMA::OneShopSecureCart TO dbo;
GO
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39617191
For linked server I think you are out of luck with rename because I don't think you can rename linked servers, but you can create them correctly for your needs.

Before dropping existing linked server MAKE SURE you right click and script it the save that and keep safe!!
0
 

Author Comment

by:Moordoom
ID: 39617867
EXEC sp_changedbowner 'sa' does not change the server the driver is trying to look for.
I am looking for a way to change the server name in sys.servers
0
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 39617884
Your question title is:

<<Change Database Ownership from a restored database>>

and sp_changedbowner does exactly that.


In the details you ask indeed for a linked server name change and my comment Posted on 2013-11-01 at 11:33:37  ID: 39617191 is just the answer to that. You can't rename it, you can't update SQL System tables directly anymore.
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question