How to restore the logins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to restore the logins

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-01 : 15:40:31

Gurus
I have lost my c drive.although my .mdf and .ldf are safe and i will restore the databases but how will i restore my logins?Any Idea?
My sql server will b reinstalled.Please help
Regards
Nitin

kpsreenath
Yak Posting Veteran

52 Posts

Posted - 2006-05-01 : 16:12:30

did you try to rebuild the master database from yesterdays backup
The following link might help you out
http://www.phptr.com/articles/article.asp?p=27566&rl=1

Thanks
Sree

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-01 : 16:33:50

If you do not have a backup for the master database, then you will need to build the users using the UserDb.dbo.sysusers table. You will have lost the passwords for them if they were SQL accounts. You will also have lost any server level roles granted to the users.

If you have the MDF and LDF for the master database, then you may still be able to use it. Save them to a different directory. Then install SQL Server using the same paths as the first installation. Then stop MSSQLSERVER service. Copy your saved files into the DATA directory of the new installation. Then start the MSSQLSERVER service. This method assumes that your MDFs and LDFs are okay, which they may not be. You can only guarantee that they are okay if the service was stopped at the time that you saved them out or if the databases had been detached. So IOW, you might be screwed anyway.

Tara Kizer
aka tduggan

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-01 : 16:56:58

Tara and sree
what if i have the backup of master with me?
How do i proceed?
Nitin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-01 : 17:02:36

Then you can restore it once you've re-installed SQL Server. You can check out how to restore the master database in SQL Server Books Online.

Tara Kizer
aka tduggan

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-01 : 18:08:21

after installing sql server ,Master will be there already.
1)will my restore overwrite it
2)will my restore be like nornal restore ?
Regards

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-01 : 18:09:26

You need to follow the directions in SQL Server Books Online for how to restore the master database. It is not done in the same way as the other databases.

Tara Kizer
aka tduggan

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-01 : 18:47:29

Tara
i have the login list with me.Can i just attach my user databses and use "sp_change_users_login" to synch the logins.
Regards

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-01 : 18:53:37

You will need to create the logins first (via Security\Logins).

Tara Kizer
aka tduggan

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-01 : 19:15:28

yeah yeah...that i will do.
and after that i will be able to use "sp_change_users_login" to synch the logins?
Regards

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-01 : 19:16:56

Yes.

Tara Kizer
aka tduggan

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-01 : 19:28:58

Thanks.just got the access of the server from my network guy...will let you know in some time.
thanks for the help

darinh
Yak Posting Veteran

58 Posts

Posted - 2006-05-01 : 21:15:33

If you have a lot of logins to fix, then I recommend this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources