Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cshong
Starting Member
8 Posts |
 Posted - 2010-03-23 : 10:27:15
|
| I had installed Microsoft SQL Server 2005 Express on both my Desktop and Laptop computer.I am developing an software (school project) which will let users log in to the SQL server to do some tasks.For the SQL Server installed in my home Desktop computer, I had created a lot of user accounts (login names) and passwords with different roles.When I am in school or any other place with my laptop, due to the internet connection issues, it is impossible for my software to connect to my home desktop computer to access database.Therefore, I must backup the databases and USER ACCOUNTS (LOGIN NAMES) AND PASSWORDS stored in my home desktop computer's SQL Server, then restore it to the SQL Server which was installed in my laptop.Is this possible? If yes, how? |
|
|
DaleTurley
Yak Posting Veteran
76 Posts |
Posted - 2010-03-23 : 12:11:14
|
| If you script the server logins (passwords won't get scripted) and restore the database on the new server / desktop, the SID of the server logins should marry up with the SID of the database users. |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-23 : 12:45:29
|
You need to understand the difference between users and logins. Logins are stored at the server level, and users at the database level. A login handles authentication, while a user handles authorization to individual databases. A user is simply a way of specifying which a logins can access a specific database.As Logins are stored at the server level, they are not included in the backup of your database. Users, which are stored at the database level, are. What you can do is:- Create the same Server logins on both servers. - Create users in your database based on those logins- Backup the database and restore it to the other server- Run the following script for each database userEXEC sp_change_users_login 'Update_One', 'UserName', 'LoginName' Replace UserName and LoginName with the ones from your databaseThis script will link the database user with the server login.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-23 : 19:09:19
|
quote:
Originally posted by tkizer
It is not necessary to run sp_change_users_login if you copy the sid to the remote server.
And how does one do that?There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Backup database, including user name and password