TSQL or whatever to restore a database and get lost of the userproblem

[Diepenheim]

Hello everbody,

With help of some helpful swynk writers I made a TSQL job/agent script
for restoring a database that came from a diff server.
Like this.
RESTORE DATABASE ..... (name database)
FROM ....... (name device)
WITH FILE = 1, NOUNLOAD , REPLACE

And works fine
But now this....
On both servers are the users the same. Same userid and password.
I restored the databases but the users seems to be allready existing and can't
be added to the databases.
So I must do a lot of actions before the restored databases can be used.
Who can help me out with this problems.
I want to transfer editted data on one server to another server and process
it there.
Thanx all
Casper.

[rogerjh]

The problem is that the users on the restored database(destination server) already exist in the sysusers table. These user names in sysusers cannot map to the logins in the syslogins table in the master database on the destination server. Prior to this, create the logins on the restored database server (destination server) prior to performing the following:

1) From the restored database (logged in as a system administrator, i.e. sa or equivalent), run this script to allow modifications to the sysusers table:

sp_configure 'allow updates', 1

2) Delete the user names from the sysusers table:

Delete from sysusers where name = <username>

3) From the source server, create the scripts to re-create the users on the restored database; this will re-create the users in the sysusers table on the restored database, which will map the users to the logins on the destination server.

I realize this isn&#39;t the best explanation (might be a little tricky), but I consistently run into this problem, and these are the steps I follow.

------------
Diepenheim at 3/19/2002 10:33:31 AM

Hello everbody,

With help of some helpful swynk writers I made a TSQL job/agent script
for restoring a database that came from a diff server.
Like this.
RESTORE DATABASE ..... (name database)
FROM ....... (name device)
WITH FILE = 1, NOUNLOAD , REPLACE

And works fine
But now this....
On both servers are the users the same. Same userid and password.
I restored the databases but the users seems to be allready existing and can&#39;t
be added to the databases.
So I must do a lot of actions before the restored databases can be used.
Who can help me out with this problems.
I want to transfer editted data on one server to another server and process
it there.
Thanx all
Casper.

[dirk]

all the scripts described can be found on this site (sql scripts)


------------
rogerjh at 3/21/2002 9:39:31 AM

The problem is that the users on the restored database(destination server) already exist in the sysusers table. These user names in sysusers cannot map to the logins in the syslogins table in the master database on the destination server. Prior to this, create the logins on the restored database server (destination server) prior to performing the following:

1) From the restored database (logged in as a system administrator, i.e. sa or equivalent), run this script to allow modifications to the sysusers table:

sp_configure &#39;allow updates&#39;, 1

2) Delete the user names from the sysusers table:

Delete from sysusers where name = <username>

3) From the source server, create the scripts to re-create the users on the restored database; this will re-create the users in the sysusers table on the restored database, which will map the users to the logins on the destination server.

I realize this isn&#39;t the best explanation (might be a little tricky), but I consistently run into this problem, and these are the steps I follow.

------------
Diepenheim at 3/19/2002 10:33:31 AM

Hello everbody,

With help of some helpful swynk writers I made a TSQL job/agent script
for restoring a database that came from a diff server.
Like this.
RESTORE DATABASE ..... (name database)
FROM ....... (name device)
WITH FILE = 1, NOUNLOAD , REPLACE

And works fine
But now this....
On both servers are the users the same. Same userid and password.
I restored the databases but the users seems to be allready existing and can&#39;t
be added to the databases.
So I must do a lot of actions before the restored databases can be used.
Who can help me out with this problems.
I want to transfer editted data on one server to another server and process
it there.
Thanx all
Casper.