Solved

SQL server : How to re-link the authority of a restored DB with those IDs on the server?

Posted on 2013-01-28
3
313 Views
Last Modified: 2013-01-29
Hi,

We are now planning to upgrade one of our old system which use Windows 2000 and SQL 7 to Windows server 2003 (this ols system can only support up to Windows server 2003) and MS SQL server 2008.  We have backup the whole DB into a bak file and transfer to the new server.

The new server is just installed with the Windows server 2003 together with all the windows patches.  And the SQL server 2008 is also a blank SQL server.  We created a blank DB with the same name to that of the DB that contains in the bak file and restore.

After the restoration completed (complete restore with original DB authorities).  We found that the master DB don't have the corresponding IDs (since we can't restore the master DB as well), therefore, we try to create all the IDs one by one manually.  Yet, even though the ID is really not appear in the master DB, but when we try to create, it prompts out that the ID already exist and can't create!!!

I found the restored DB is still marked with the ID that we are going to create but those IDs are actually not exist in the master DB.  

May I know how to resolve this?  
1) Any short-cut to re-generate those IDs without the need to create manually?
2) Any easy way to establish the same authority settings in the new DB server (both in master DB and the DB that need to restore)

Kindly please help.

Stanley
0
Comment
Question by:StanleyLMW
3 Comments
 
LVL 4

Assisted Solution

by:mcmahon_s
mcmahon_s earned 250 total points
ID: 38826458
Take a look at this link, that should solve your problem http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 38828614
1) YES.

When you create the SQL logins on the new server, specify the same SID as they had on the old server.  Then SQL can automatically sync them every time you restore a db, and you won't have to go thru the orphan clean up on every restore.

A) on the new server, drop the existing SQL logins that came from the old server
B) on the old server, generate the commands to add those SQL logins, as needed, to the new server
C) run the gen'd script on the new server
D) re-sync already restored dbs if necessary (SQL might not re-sync a db user that is already there when the corresponding login is created)


--B) sample T-SQL
--will need to fill in the password and respective SID, as I can't
--remember how to convert the sid to proper varchar in SQL 2000

-- create commands to run in SQL 2008
SELECT
    'CREATE LOGIN [' + l.name + '] WITH PASSWORD = ''password_goes_here'', ' +
    'SID = <copy_existing_sid_here>'
FROM master.dbo.syslogins l
WHERE
    isntname = 0
0
 

Author Closing Comment

by:StanleyLMW
ID: 38830117
Thx. ^^
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
Migration of AD user accounts from Server 2003 to 2012R2 5 76
In or Between 2 44
create insert script based on records in a table 4 16
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now