4

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.

0

1 Answer 1

3

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.

4
  • 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 Aug 5, 2015 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, 2015 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. Aug 5, 2015 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, 2015 at 11:30

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