login probs after restore

sql2k sp3
About once a month Im asked to take a backup from
production and restore it in a development environment.
(these are in the same domain) After the restore, I can
see the users in the db, but those users cant see the db
from tools like Query Analyzer. I have to actually drop/
recreate the users from the db and then reset the perms to
get them going. Not a big deal for me, but there has got
to be a better way? Any ideas?
TIA, ChrisRHi,
Yes, the better way is to execute "sp_change_users_login" procedure from the
restored database.
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR|||Chris,
the SID in master.dbo.sysxlogins needs to map to the SID
in yourdb.dbo.sysusers and there is a sp to help you remap
them in a backup/restore scenario:
sp_change_users_login
Full details are in BOL, but this procedure will mean you
can retain the users and permissions.
HTH,
Paul Ibison|||Thanks Paul. Do you know what I use for the WINNT guys?
>--Original Message--
>Chris,
>the SID in master.dbo.sysxlogins needs to map to the SID
>in yourdb.dbo.sysusers and there is a sp to help you
remap
>them in a backup/restore scenario:
>sp_change_users_login
>Full details are in BOL, but this procedure will mean you
>can retain the users and permissions.
>HTH,
>Paul Ibison
>.
>|||Chris,
as far as I understand these should be OK as the SID won't
change as you're still in the same domain. You can use
sp_validatelogins to be sure.
Also, Rand from MS has previously posted up this link for
mapping both types of logins :
http://support.microsoft.com/default.aspx?kbid=298897
Regards,
Paul Ibison|||Also, search KB and read about sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR