restoring user logins

[NAMARA]

When I restore an sql server database after formatting the computer and re installation of SQL server,the Usernames are existing in the Users folder of Sql server enterprise Manager, but these users can not log on untill when I try to recreate each of them and even then, i constantly get the message User already exists, at that time the user is added to the logins Folder and the password will have been lost I have to create it again,Is there a way of retoring logins as well? with their passwords so that I dont bother recreating them?

 

[pjw001]

I'm not sure whether this is what you want but try looking up "sp_change_users_login 'report'".

If I have to move a database from one machine to another, both of which have the same logins, this usually sorts it out.

Good Luck.

 

[SQLBill]

Look into sp_helprevlogin. It's a set of two stored procs. You run sp_helprevlogin and you'll get a list of all the logins on the server along with their encrypted passwords. You can run that after you've gotten everything back together. Then you will most likely have 'orphaned' logins. That's where the login exists on the server and in the database, but SQL Server hasn't connected the two. Run the command pjw001 provided and that gives you a list of orphaned logins....then all you have to do is fix them. See the BOL to get the syntax for fixing orphaned logins.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875