Programatically Backup and Restore User Logins Problem...

I'm sure your all aware of the bug in SQL 2000 where if you take a backup an
d
then do a restore on a different database server, the users accounts are not
added to the server's own security (login) section.
The user accounts are though added properly to the databases "users" list
however. And yes I do need to use SQL Server authentication, instead of
windows authentication.
Does anybody know how to get around this' I wish to do it programatically
without enterprise manager.Well I wouldnpt call it a bug, remember that the users, which are specific t
o
the database, map to logins, which are server-based... So whn you restore a
database on a different server than where the backup was made, there is no
way for the softwae to know which login on the other server the dataabse use
r
should be mapped to... If you're lucky enough that the server Login list is
identical on the other server including Login IDs, (pure coincidence) it
actually does fix things up properly, but this is rare.
So you need to use a system-level Stored proc called sp_adduser, this can be
called programatiicaly.
"David Dolheguy" wrote:

> I'm sure your all aware of the bug in SQL 2000 where if you take a backup
and
> then do a restore on a different database server, the users accounts are n
ot
> added to the server's own security (login) section.
> The user accounts are though added properly to the databases "users" list
> however. And yes I do need to use SQL Server authentication, instead of
> windows authentication.
> Does anybody know how to get around this' I wish to do it programaticall
y
> without enterprise manager.
>|||This is not a bug at all. Logins are at the Server level and Users are at
the DB level. When you backup and restore a db it knows nothing of the
server. These might help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL
Andrew J. Kelly SQL MVP
"David Dolheguy" <DavidDolheguy@.discussions.microsoft.com> wrote in message
news:5C877C2E-EE7D-4658-9E31-AF46F0060E18@.microsoft.com...
> I'm sure your all aware of the bug in SQL 2000 where if you take a backup
> and
> then do a restore on a different database server, the users accounts are
> not
> added to the server's own security (login) section.
> The user accounts are though added properly to the databases "users" list
> however. And yes I do need to use SQL Server authentication, instead of
> windows authentication.
> Does anybody know how to get around this' I wish to do it
> programatically
> without enterprise manager.
>