February 7, 2009 at 5:27 am
Hi,
One of my Server went corrupted.The physical server itself got corrupted.I have only one user database there(our good luck).
Now we have installed SQLSERVER and Servicepacks.I need to restore the database with the old backup.Whether that restoration restores all the login that was created before???
How about my loss in this???
February 7, 2009 at 7:13 am
The logins aren't stored in the user's database. They are stored in the master database. The users databases store users that are mapped to the login. You'll have to create the logins in the master DB and them map the users in the restored database to the new logins. For windows logins there will be no problems. For SQL Server's login you'll need to use sp_change_users_login (you can find about it in BOL).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 7, 2009 at 7:22 am
I think a restore of your backup will restore all your users login included because 2000 when I restore a database and see orphaned users I just delete the restore and create a clean one with everything. Yes the logins are in the master but a restore also adds everything login included. Check the link below for the how by Microsoft.
http://support.microsoft.com/kb/314546/en-us
Kind regards,
Gift Peddie
February 7, 2009 at 8:17 am
A restore of a *user* database will not restore the logins to the server, only the users of that user database, whether sql2005 or sql2000. The database users will be there, but they will be orphaned. You need to use sp_help_revlogin stored proc, or create the logins fresh on the new server.
"Got no time for the jibba jabba!"
-B.A. Baracus
February 7, 2009 at 8:35 am
I have restored 2000 databases many times and it restore all the users, sometimes I get orphaned users I could edit the Master but it was a bank so I just create a new restore and the users are all there it may have changed with SP4 but SP3a and below it restores the users. That was how all Asp.net deployment transfer users to hosting companies before Microsoft created the wizard because it does not work all the time. And the link I posted included how to transfer the users here it is.
http://support.microsoft.com/kb/246133/
Kind regards,
Gift Peddie
February 7, 2009 at 8:40 am
Yes users will be transferred. However, a db user is different from the login that user is mapped to. Just distinguishing between the two.
"Got no time for the jibba jabba!"
-B.A. Baracus
February 7, 2009 at 8:49 am
You are talking about SQL Server Login and Database permissions and I am telling you in 2000 SP3a and below you could. However 2005 comes with many extra layers. I have had users with orphaned permissions I just delete the restore and create a new one.
Kind regards,
Gift Peddie
February 7, 2009 at 8:54 am
Ok, then I'll take your word for it 🙂
"Got no time for the jibba jabba!"
-B.A. Baracus
February 7, 2009 at 9:06 am
I am glad we had this conversation because I have been wondering why creating Backup Devices in the Master is needed in 2005 now I know why. I can tell you I have restored 2000 in my employers network for people I have not met and I only get back thank you or I cannot login which means orphaned user. I just delete the restore and create a new one.
Kind regards,
Gift Peddie
February 7, 2009 at 9:19 am
Same. I've only been a DBA for just over a year (came from being a programmer - mainframe at that, lol), so I'm always trying to understand more and more about SQL Server. SO glad I made the career change, incidentally.
And when restoring, you are restoring to a different instance/server?
"Got no time for the jibba jabba!"
-B.A. Baracus
February 7, 2009 at 9:25 am
We had 68 SQL Servers I just restore as needed based on the email and you should know 2000 did not have a separate security section with the database. The separate security section within the database is new to 2005
Kind regards,
Gift Peddie
February 7, 2009 at 9:44 am
Gift Peddie (2/7/2009)
you should know 2000 did not have a separate security section with the database. The separate security section within the database is new to 2005
Maybe I'm misunderstanding, so please do correct me if I'm wrong, but SQL 2000 still has a difference between users and logins, so there is a separate security "section" in SQL 2000. Just because it doesn't show in Enterprise Manager as a "Security" folder under each user database doesn't mean the separation isn't there. There is still the syslogins table in master vs. the sysusers table in each user db.
Again, maybe I'm just misunderstanding.
"Got no time for the jibba jabba!"
-B.A. Baracus
February 7, 2009 at 9:53 am
Yes you are misunderstanding me in 2000 you add the server login in security under management in Enterprise Manager and add the user in the database but not separate security section within the database as you have in 2005.
Server login is always separate because in RDBMS access to server is not access to database and access to database is not access to objects.
Kind regards,
Gift Peddie
February 7, 2009 at 10:02 am
Okay, but now we aren't even on the topic of the original post, so I don't want to hijack the thread. Bottom line of the original post as I understand it, is that if the original poster is recovering from a server crash, and in doing so they reinstall SQL 2000, and then restore the user database(s) from db backups, the users have been restored, but the logins have not. Whether that was the case or not in SP3a or prior I can't attest to. So thanks again for the conversation, but it seems we could go round and round. 🙂
"Got no time for the jibba jabba!"
-B.A. Baracus
February 7, 2009 at 10:16 am
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. You have been a DBA for a year I have been more than ten years and I am MCSE, MCDBA, MCITP BI and DBA, MCPD C# WEB and MCTS TFS. 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.
Have a nice day. 🙂
Kind regards,
Gift Peddie
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply