Copy database AND users in automated way?

With 2005 (although the answer is probably the same for 2000):
I want to have BAK files copied to another server, the other server import
that copy of the database, and the other server be available for
testing/development purposes with current data. The problem is the user
records.
I know how to use TSQL to make a backup BAK file of a database. I know how
to restore that file onto another server (dev server) with TSQL. So far so
good. The problem is that the backup copy of the database imports in with
bad user records that don't really exist on the new server. Even if the
same user names exist, the record ID don't point at each other. As you may
know, you have to do an sp_dropuser on those "not aligned properly" records,
and then manually add back the users to the Development SQL Server, and then
add those users to the dev database copy. But I do this user stuff through
the GUI and not through TSQL and I need a way to have the BAK files copy
over and start working automatically. I could probably add the user to the
server through TSQL and add the user to a the database, with a specified
role, but 1) I don't know that TSQL (please tell me) and 2) I don't want
to have TSQL lying around on the server with user passwords in it.
Anyone know a good solution to this problem? Ideally I would like to run
some TSQL that will line up the record IDs for the users in the database
copy to users I place into the server one time only and that way I don't
have to enter passwords more than once.Take a look at sp_help_revlogin. It allows you to extract the credentials
from the source server and apply them to the target.
http://support.microsoft.com/kb/246133
--
-oj
"HK" <replywithingroup@.notreal.com> wrote in message
news:1EoVf.12036$w86.3511@.tornado.socal.rr.com...
> With 2005 (although the answer is probably the same for 2000):
> I want to have BAK files copied to another server, the other server import
> that copy of the database, and the other server be available for
> testing/development purposes with current data. The problem is the user
> records.
> I know how to use TSQL to make a backup BAK file of a database. I know
> how
> to restore that file onto another server (dev server) with TSQL. So far
> so
> good. The problem is that the backup copy of the database imports in
> with
> bad user records that don't really exist on the new server. Even if the
> same user names exist, the record ID don't point at each other. As you
> may
> know, you have to do an sp_dropuser on those "not aligned properly"
> records,
> and then manually add back the users to the Development SQL Server, and
> then
> add those users to the dev database copy. But I do this user stuff
> through
> the GUI and not through TSQL and I need a way to have the BAK files copy
> over and start working automatically. I could probably add the user to
> the
> server through TSQL and add the user to a the database, with a specified
> role, but 1) I don't know that TSQL (please tell me) and 2) I don't
> want
> to have TSQL lying around on the server with user passwords in it.
> Anyone know a good solution to this problem? Ideally I would like to run
> some TSQL that will line up the record IDs for the users in the database
> copy to users I place into the server one time only and that way I don't
> have to enter passwords more than once.
>