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.
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
- 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.
- 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
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.