Hy every one

I am using Micrsoft Sql desktop engine installed by an application "epihandy". The databse is created by the application so i have no idea about the rules and policies regarding the access. I have a main server A and a Backup server B where i need to use the database, while not interfering the main server. I have use the following commands to backup and restore the data over the destination server. Prior to this, i have also checked the password for "sa" being same on both server.
I used the script from here and it seemed to work great.

Scheduling Backups for SQL Server 2005 Express

I used the following to backup the database:
1. in command window, type: osql -S "(local)\EPIHANDY" -d "master" -E
2. Set the epihandy0906 database in single user mode, enter the following + enter
3. alter database epihandy0906 set SINGLE_USER
4. GO
5. Then enter the following and hit enter:
6. RESTORE DATABASE epihandy0906 FROM DISK = ' c:\epibackup\restore.bckp' WITH REPLACE
7. GO
8. Then enter the following and hit enter:
9. alter database epihandy0906 set MULTI_USER
10. GO

Eventually the restoration process was successful. The issue rose when I try to access the database through the epihandy interface. there is a table in the database 'dbo.epiusers' which contains users, ids passwords etc which the application uses to access the database. All the user information from the “dbo.epiuser” is retrieved as expected but upon inserting the password, “LOGIN FAILURE” occurs.
I also envisaged over the “LOGIN AND PASSWORD TRANSFER” while moving the database. I went through an article provided by Microsoft( How to transfer logins and passwords between instances of SQL Server ) and proceeded step by step. I added sp_help_revlogin procedure in the master database over the original server. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Output is as under:

DECLARE @pwd sysname
-- Login: BUILTIN\Administrators
EXEC master..sp_grantlogin 'BUILTIN\Administrators'
-- Login: distributor_admin
SET @pwd = CONVERT (varbinary(256), XYZ)
EXEC master..sp_addlogin 'distributor_admin', @pwd, @sid = ABC, @encryptopt = 'skip_encryption'

I then created the same login at the destination server. It did not work either. I also matched the SID’S of all existing logins in the “sysxlogins” table in MASTER as well as “sysusers” table in epihandy0906.

One thing that I unexpectedly found out on the live server was that the “sa” login was having two SID’S. I believe SID’S are supposed to be unique. I made the following query.

use master
go
SELECT sid FROM dbo.sysxlogins WHERE name = 'sa'
Following were the results:
1. 0x01
2. 0xFFBC9E1A55F9934EA4AFDEAE8AF9771C
use master
go
SELECT sid FROM dbo.sysxlogins WHERE name = 'distributor_admin'
1. 0xFFBC9E1A55F9934EA4AFDEAE8AF9771C
2. 0xFFBC9E1A55F9934EA4AFDEAE8AF9771C
3. NULL
use master
go
SELECT name FROM dbo.sysxlogins
1. sa
2. BUILTIN\Administrators
3. distributor_admin
4. NULL
5. distributor_admin
6. sa
7. distributor_admin

There are two entries for the “sa” login. The only database user “dbo” seems to be correctly matched with the “sa” account along with the SID. i guess the uses in the tabled 'dbo.epiusers' are mapped back to the database sysuser table and sysuser is in turn mapped over sysxlogin table in the master database.

Is the linkage between the NT login and the user information changed? I am using windows authentication.
I had also user sp_change_users_login and sp_helplogins as well. There were no orphaned users found. It seems that the SIDS are matched properly but still no access to the database.
I will appreciate any one can help me to go around it as I have tried many alternatives.

2 differents master..sysxlogins system tables and restoring one database from a server to an other... is a mess.

Try to keep your both sysxlogins tables similar, or recreate, using a script, your logins/users in the backup site.

What you have are two different sets of logins. The application logs into the database engine, probably as a local admin (seeing as it is SQL Express). Run the command sp_who2 in a query window to see who is logged in.

The second set of logins appears to be application specific. I am guessing that the passwords in the EPIUSER table are encrypted or hashed somehow. What do you get for the following query: