February 7, 2009 at 10:32 am
Gift Peddie (2/7/2009)
The user have not restored and yes you have hijacked the thread because I have given the user a support article which included the login transfer article which I have also posted separately.
However the user was asking if the logins were restored when a user database is restored which they are not. Login transfer script aside, if there's no backup of master, the logins are not in the user database. The database users are. That's why, when you restore, you restore user database, you can get orphaned users, because the logins are missing.
If the logins were backed up as part of the user database, it would not be possible to have orphaned users. An orphaned user is one that no longer has an associated login. If the logins were restored with the user database, then there would be no possibility of a user with no associated login.
I am MCSE, MCDBA, MCITP BI and DBA, MCPD C# WEB and MCTS TFS.
A long list of three and four letter acronyms doesn't automatically mean that you're right and he's wrong.
It is time to let the OP use the links I posted which developers have used to transfer user databases to hosting companies before Microsoft created a wizard.
How is a login transfer script going to help if there's no backup of the master database and no source to script from?
sudarram: If you don't have a backup of master, you're going to have to recreate the logins. If you do have a backup of master, then you will need to restore that on the new server The method is very different from restoring a user database, s check Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2009 at 10:39 am
However the user was asking if the logins were restored when a user database is restored which they are not. Login transfer script aside, if there's no backup of master, the logins are not in the user database
My not asking for the Master backup and restore was an oversite because I have been talking to this other user I wanted to tell the user to restore the Master.
And in 2000 have had orphaned user while restoring and while creating new accounts because the account was created in the Master but not in the database.
Kind regards,
Gift Peddie
February 7, 2009 at 10:46 am
Gift Peddie (2/7/2009)
And in 2000 have had orphaned user while restoring and while creating new accounts because the account was created in the Master but not in the database
That's not an orphaned user. You're talking about a login that has no associated database users. An orphaned user is the other way around. A database user that has no associated login.
Caused by transfering a user database (via backup/restore or detach/attach) to a server where a login with the same SID does not exist.
Books Online:
A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2009 at 10:51 am
I am actually talking about both. The orphaned user were covered in the article I posted in my first reply so adding the restore of the Master was all that was missing and yes it is the most important.
This was from my first reply to this thread.
Step 3: How to resolve orphaned users
http://support.microsoft.com/kb/314546/en-us
Kind regards,
Gift Peddie
February 9, 2009 at 7:05 am
It seems that everyone is talking about a similar thing here. Let's try to remain professional and respectful as we debate things. I had a few complaints that this was getting a bit heated.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply