2

This question already has an answer here:

Where does the logins users are stored in the SQL server “in which data base “ In some softwares that requires the admin to create users/password for his users such as ArcGIS server the logins information are stored in a separate database so that the admin takes periodically backups just in case if the server was down to avoid losing the usernames and passwords. In the SQL server 2012 how can I manage to back up my logins since the moved the database form one machine to another preserves the users but the users cannot connect to the SQL server due to the loss of logins.

marked as duplicate by dezso, Paul White sql-server Aug 5 '15 at 10:26

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

Where does the logins users are stored in the SQL server “in which data base “

First please note logins and users are both different thing. You need a valid login for a user to make changes into database. User is anyone authorized to perform ceratin actions on database while login is credentials they use to Log into the SQL Server.

In the SQL server 2012 how can I manage to back up my logins since the moved the database form one machine to another

There are two authentications

  1. Windows authetication

The credentials for which are not stored in SQl Server database and managed by windows/AD. There would be entry for windows autheticated logins in master database with respective SID but password would be with Active directory.

  1. SQL Server authetication.

For 2nd we have password stored in hash format you can see it from sys.sql_logins. The information about SQl server logins are stored in master database and each login has SID repective to it. Only SA login has same SID no matter what server it is. That is why when you move database by backup restore mechanism users are moved not logins and you finally have to create logins(if already not there) and map it to users. This is generally called as troubleshooting orpahned users

You can use sys.syslogins and sys.server_principal to gain more information about logins. Note that sys.syslogins is a legacy view.

If you also want to move your logins you have to take backup of master database and restore it on destination server. And this would be possible only if you have same version and SP on destination machine.

  • Thanks Shanky, After copying the Master database from the source machine and replacing it with the master database in the destination machine I have noticed that the logins was transferred correctly to the destination machine, but the old databases that was attached to the destination SQL server doesn’t work and shows a message next to them (Recovery Pending) I guess that replacing the original Master.mdf with the backup made this issue since we lost some configurations for the current databases. How to migrate the logins only form the source master.mdf to the destination Master.mdf Thanks – Ahmad saleh Aug 5 '15 at 10:19
  • Did you restored master database of attached it ? If you want to migrate logins please see this support article. support.microsoft.com/en-us/kb/918992#/en-us/kb/918992 – Shanky Aug 5 '15 at 10:25
  • Thanks a again Shanky, I think the easiest method to migrate them and clever enough to be backed up regardless what happened to the source machine (my data should be stored in an external hard drive in the case of software or hardware loss), I believe the easiest way it do this is by moving the master database. – Ahmad saleh Aug 5 '15 at 11:21
  • Yes but that should be done in beginning, first restore master database and then move all databases using backup restore mechanism. – Shanky Aug 5 '15 at 11:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.