|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
restore database users-logins mapping losti 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/download/sqlsvr2000/utility/5.0/win98me/en-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.aspx?scid=kb;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.aspx?scid=kb;en-us;Q168001 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274188 Show quote "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. > > > > > > 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? Show quote "Jasper Smith" <jasper_smi***@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/download/sqlsvr2000/utility/5.0/win98me/en-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.aspx?scid=kb;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.aspx?scid=kb;en-us;Q168001 > PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete > http://support.microsoft.com/default.aspx?scid=kb;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. >> >> >> >> >> >> > > 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  |
|||||||||||||||||||||||
Other interesting topics