restore database users-logins mapping lost

i have a DB with DB users that are mapped to server logins
after backing the databasr and restoring on other server (with the same
logins defined)
the logins are not mapped to the sql user
wanting to do this from the security folder in enterprise manager, pressing
the login
in database access tab checking the required checkbox for the newly restored
database
(and the DB user correct name mapping appears)
pressing OK button gives the error Error 21002: [SQL-DMO]User 'appUser'
already exists.
what is the best way to achive the mapping from the login to the user
without deleteing anything and re-defining database permissions for the
user?
thanks.
My preference is to use sp_SidMap. You can get it here
http://download.microsoft.com/downlo...us/Mapsids.exe
The accompanying KB article is here
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default...;en-us;Q240872
Some other KB articles that may be useful are
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default...;en-us;Q168001
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default...;en-us;Q274188
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"z f" <nospam@.nospam.com> wrote in message
news:eJ2jd$LWGHA.4484@.TK2MSFTNGP02.phx.gbl...
>i have a DB with DB users that are mapped to server logins
> after backing the databasr and restoring on other server (with the same
> logins defined)
> the logins are not mapped to the sql user
> wanting to do this from the security folder in enterprise manager,
> pressing the login
> in database access tab checking the required checkbox for the newly
> restored database
> (and the DB user correct name mapping appears)
> pressing OK button gives the error Error 21002: [SQL-DMO]User 'appUser'
> already exists.
> what is the best way to achive the mapping from the login to the user
> without deleteing anything and re-defining database permissions for the
> user?
> thanks.
>
>
>
|||http://www.databasejournal.com/featu...le.php/2228611
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
|||Sorry meant to type a little description as well =P First link is for
migrating users from one server to another, It will create a script of
the accounts on the old server that you can run on the new server which
will create the correct sids etc.. so when you roll over a database
from the server1 to server2, u wont have orphaned users in the db on
server2 that dont have physical sql logins associated with them. That
should fix your problem. Make sure if youve added new users server2 to
remove them before running the script on server2 after u have it
created on server1 from the first link above
|||thanks
it is a killer script for me
i found something simpler:
sp_change_users_login (built in with SQL Server)
where did it hide?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23hf6DFMWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> My preference is to use sp_SidMap. You can get it here
> http://download.microsoft.com/downlo...us/Mapsids.exe
> The accompanying KB article is here
> INF: How to Resolve Permission Issues When a Database is Moved Between SQL
> Servers
> http://support.microsoft.com/default...;en-us;Q240872
> Some other KB articles that may be useful are
> PRB: User Logon and/or Permission Errors After Restoring Dump
> http://support.microsoft.com/default...;en-us;Q168001
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
> http://support.microsoft.com/default...;en-us;Q274188
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "z f" <nospam@.nospam.com> wrote in message
> news:eJ2jd$LWGHA.4484@.TK2MSFTNGP02.phx.gbl...
>