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