Export data from Microsoft SQL Server

Question

I have an application that uses a database Microsoft SQL Server running on PC1.

I set up that application on another PC (PC2):

1. I installed the application on PC2
2. The application creates the database A on PC2 (the application works)
3. I make a backup of the database A on both PCs
4. I restored the backup of database A from PC1 to PC2.
5. The application is not working. Can not connect.
6. I restored PC2's backup of the database A back to PC2 (the application works)

The only thing I think would solve this is exporting the data into an SQL file, but I don't know how.

I am using Microsoft SQL Express 2005.

Answer 1

I suspect that your problem is orphaned users. Basically, your users and logins may be named the same, but they have different sids on the different instances.

Here's a script to show the problem (lifted from the link):

USE MASTER GO SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins WHERE [name] = 'YourAppUser' GO USE YourAppDB GO SELECT name DataBaseID,SID as DatabaseSID FROM sysusers WHERE [name] = 'YourAppUser' GO 

Or, you can just fix it. There's a built-in procedure for that. Restore the database from the first PC onto the second PC and run:

USE YourAppDB GO exec sp_change_users_login @action='update_one',@usernamepatter='YourAppUser',@loginname='YourAppUser' GO 

I once had to run this on a large number of databases, so I have, lying around:

execute sp_MSforeachdb 'USE [?]; exec sp_change_users_login @action=''update_one'',@usernamepattern=''YourAppUser'',@loginname=''YourAppUser''' 

(This uses the undocumented sp_MSforeachdb procedure to run that command on every database.)