April 28, 2004 at 1:57 pm
Hi this is probably elementary and I am showing off my ignorance but I am tangled up in logins vs users issues.
I have backed up a database on one server, and restored it on another. Within the database on the new server the database shows "myUser" in the users section of the database. this user has dbo access and owns several tables.
In the logins section of EM there is also "myUser" the default database is set to the database in question but under the "database access" tab for this login the database is not selected. Whenever I try to select the database it tells me that the user already exists. So what????
Can someone give me the big picture difference between "logins" and "users" I would think they are the same darn thing but apparently not.
I went back to redo the backup to see if I had used an incorrect option but there was not options concerning users/logins.
Thanks for any guidence
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
April 28, 2004 at 2:01 pm
well this spells it out clearly:
http://www.akadia.com/services/sqlsrv_logins_and_users.html
I suppose if I restore the master first then the actual databases that might work.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
April 28, 2004 at 6:36 pm
It sounds like you are describing orphaned users. Have a look in BOL. You do not need to restore the master database.
The article you provided a link to, also describes how to resolve orphaned users. It does not mention anything about backing up and restoring Master.
Good luck
Angela
November 25, 2008 at 9:33 am
use this script to fix your orphaned users. It helped me so many times.
EXEC sp_change_users_login ‘Auto_Fix’, ‘UserName‘
December 4, 2008 at 1:38 pm
Logins give access to the server, and aren't included in the backup. Users are mapped to logins, and have rights/permissions in the database, and are included in the backup.
The FIRST time you restore a backup on a new instance, you need to move the logins and then sync them up with the database users. Each time you restore the database again (if this is a QA or dev type situation), the users should then map over if the login SIDs are the same. (sp_help_revlogin from this site can help ensure this).
If you are doing this in a DR situation, you either need to include a restore of master, or have logins scripted and ready to restore on this new server (or create them manually).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply