Very Computer

by **AGDA** » Fri, 15 Jan 1999 04:00:00

I am moving the backup files of several databases in a sql server to a
different physical box. I have a problem with matching the login id's in SQL
server with the usernames in the each database after the restore.
Can I use sp_change_users_login... When I use it I get the following error:
"Msg 15290, Level 16, State 1
Aborting this procedure. The Action 'AUTO_FIX' is incompatible with the other
parm values ('' , '')."
The bottom line is how do I match the sql login Id's(I recreate these in the
new box using the scripts) and the database usernames after I restore the
database backups in a different physical sql box.

Thank you

by **Steve Robinso** » Fri, 15 Jan 1999 04:00:00

AGDAR,

If you use the script below it will produce a script which will drop the
users out of the restored database and readd them. Cut the script out of
the results window into another SQL session and run it.

This will fix the problem where the id's don't match

YOu will need to pass the DB Name you wish to fix and the SQL group you wish
to add these people to.

set nocount on

select "exec "+dbname+"..sp_dropuser '"+name+"'"+char(10)+"exec

from master..syslogins

and name not in ("sa","probe","dbo","repl_subscriber")
go

I hope the above helps

Steve Robinson
SQLServer MVP

>I am moving the backup files of several databases in a sql server to a
>different physical box. I have a problem with matching the login id's in
SQL
>server with the usernames in the each database after the restore.
>Can I use sp_change_users_login... When I use it I get the following error:
>"Msg 15290, Level 16, State 1
>Aborting this procedure. The Action 'AUTO_FIX' is incompatible with the
other
>parm values ('' , '')."
>The bottom line is how do I match the sql login Id's(I recreate these in
the
>new box using the scripts) and the database usernames after I restore the
>database backups in a different physical sql box.

>Thank you

by **AGDA** » Fri, 15 Jan 1999 04:00:00

Thank you... I 'll try this..

Very Computer

Do I use sp_change_users_login... (need to match id's with usernames)

Do I use sp_change_users_login... (need to match id's with usernames)

Do I use sp_change_users_login... (need to match id's with usernames)

Do I use sp_change_users_login... (need to match id's with usernames)