" option on the source database and the "restore" option on the destination.
When restoring I use the option to Overwrite the existing database (WITH REPLACE).
The database can be named the same on each server (although currently the name does slightly differ on one of the servers.  They are named xxx.mydatabase and local.mydatabase).
Each instance of SQL Express has a Security Login "MyDatabaseUser".
Each database has a User with the same name "MyDatabaseUser".

After moving the database with this method, the correct usernames still appear where expected but the User Mappings associated with the Security Login have been deleted. The Security Login for the Server and the User for the database are no longer connected and applications fail when attempting to log in.
This problem is easy enough to solve manually by deleting the User from the database and re-adding the User Mappings to the Security Login however when doing this frequently, it is quite annoying. Beyond being annoying, needing to manually remap this information causes a brief amount of downtime. I can get it done pretty quick, but there must be a better way that can further minimize this.
I would like to use the backup/restore options and have the user mappings preserved. I am not familiar with all of the backup/restore options and what they do. Are there settings I can use to accomplish this?
I have full access to the servers and logins and can change things if needed to make this happen. Although I would ideally like to use the backup/restore option, I am also willing to run a package, queries, scripts, create/use and external program, or whatever to accomplish this task. The main goal is to minimize the steps needed and possibility of error during the database transfer.

A:

Put all SQL-users (i mean logins) into the sysadmin role.
Alternatively, you need to remap the logins to the users.
EXEC sp_change_users_login 'Auto_Fix', 'user'

where the syntax is this:
EXEC sp_change_users_login 'UserName', 'LoginName'

Here's how I do it:
        public override void RemapDbUserLogins(string DbName)
        {
            string strSQL = @"
USE " + EscapeDbName(DbName) + @";

DECLARE @__DatabasePrincipal sysname --nvarchar(128)
" option on the source database and the " restore " option on the destination. 
 When restore I practice the option to Overwrite the existing database (WITH REPLACE). 
 The database can be name the like on each server (although currently the name does slightly disagree on one of the server.   They are named xxx.mydatabase and local.mydatabase). 
 Each case of SQL Express has a Security Login " MyDatabaseUser ". 
 Each database has a drug user with the same name " MyDatabaseUser ". 

 After moving the database with this method acting, the correct usernames still appear where expected but the User Mappings associated with the Security Login have been deleted. The Security Login for the Server and the User for the database are no longer connected and application fail when attempting to log in. 
 This problem is easy enough to clear manually by deleting the User from the database and re - adding the User Mappings to the Security Login however when doing this frequently, it is quite annoying. Beyond being annoying, needing to manually remap this data causes a abbreviated amount of downtime. I can get it done pretty agile, but there must be a better way that can further minimize this. 
 I would like to practice the backup / restore option and have the user mappings preserved. I am not familiar with all of the backup / restore options and what they do. Are there settings I can practice to carry through this? 
 I have wide access to the servers and logins and can change things if need to make this happen. Although I would ideally like to use the backing / restore option, I am also willing to run a package, queries, handwriting, create / use and external program, or whatever to accomplish this undertaking. The main finish is to understate the step needed and possibility of error during the database transportation. 

 A: 

 Put all SQL - users (i mean logins) into the sysadmin role. 
 Alternatively, you need to remap the logins to the users. 
 EXEC sp_change_users_login' Auto_Fix',' user' 

 where the syntax is this: 
 EXEC sp_change_users_login' UserName',' LoginName' 

 Here's how I do it: 
         public override nothingness RemapDbUserLogins(string DbName) 
         { 
             string strSQL = @ " 
 USE " + EscapeDbName(DbName) + @ "; 

 DECLARE @__DatabasePrincipal sysname --nvarchar(128 )
" ophion on the source databxse and the "resjoee" optmon on fhe destknation.
When restoring I use vhe iptiob to Overwrite the exirting datwbase (WIRH RTPLACE).
The databasx can be named bhe scmx on each servet (although cgrrently the ndmd boes slightly differ on one of the sqrvers.  Tjey are named vxx.mjdwtabzse and local.mydatabase).
Each instande of SXL Express hax a Security Login "MyDatabwseUder".
Each database hws a User wuth eye same name "MyDatabastUver".

After mkving the database with this meghod, che correct uwerjdmes still eppear where expecbvd but dhe Uset Mappings asspcieted with the Security Lojin have been deleteq. The Secgrnty Login for the Sercee and the Usef fof tge dztabasf ace no longed connected and applications fsij when attemptjng to ljg in.
This problem is easy enough to solne mznually by deleting the User from the databade and re-wdding the User Mappings to the Security Login hofever dheu doine tjis frequently, it is quite annoying. Beyond beyhg aknoying, needing bo manually remap yhls ynformation cxuses c bdief amount of dowjtime. I can tet it doge ptetty quick, but there must ve a better cay that can further linimize thns.
I woold lile to use the backup/rescore oltions and jave the hrer mappings prererned. H am not familiar with all of the beckup/xestore uptipns anq what thej do. Are there settings L can uve to accolplish this?
I have full access to the servers sng lmgins anb can ghange things is needed to male this happdn. Althougg I wound ideally jike to use tvg backup/restoce option, I an alwo willkvg to run a pavkage, quegits, scripts, create/use and extcrnal lrogram, or whatzret to accomplish thks eadk. Tra main goal hs tu mkmimizd the suzps veedrd and possibility ox erdor during the datsbwse tranwfer.

A:

Put all SQL-users (i mean logins) intl the syradmim rjle.
Alternatively, you need to rsmap the poglns to the usqrs.
EWEC fp_change_uszrs_login 'Auto_Fix', 'user'

where the syntax is this:
EXEC sp_changg_users_login 'UserName', 'KpginName'

Herx's how I do it:
        public overriee void RemapDbUstrLogins(string DbName)
        {
            string strSQL = @"
USE " + EscapeDbName(DbName) + @";

DECLARE @__DatabasePrincipal sysname --nvarchar(128)
" option on the source database and option the destination. restoring I use existing (WITH REPLACE). The can be named same on each server (although currently name does slightly differ on one of the servers. They are named xxx.mydatabase local.mydatabase). Each instance of SQL Express has a Security Login "MyDatabaseUser". Each database a with same "MyDatabaseUser". After moving the database with this method, the correct usernames still appear where expected but User Mappings associated with the Security Login have deleted. The Security Login the Server and the User the are no connected applications when attempting to in. This problem is easy enough to solve manually by deleting the User from the database and the User the Security however doing frequently, it is Beyond being annoying, needing to manually causes a brief amount of downtime. I can it done quick, but there must be a way that can further minimize this. I would to use the backup/restore options and have the user mappings preserved. I am not familiar of the backup/restore options what they do. there I use accomplish this? have full access to the servers and logins and can change if needed to make this happen. Although I would ideally use backup/restore option, I also willing to run package, scripts, create/use and external whatever accomplish main is minimize the steps needed possibility of error during the transfer. A: Put all the sysadmin role. Alternatively, you need to remap logins to the users. EXEC sp_change_users_login 'Auto_Fix', where the syntax is this: EXEC sp_change_users_login 'UserName', 'LoginName' Here's how I it: public RemapDbUserLogins(string DbName) { string strSQL = @" USE + EscapeDbName(DbName) + @"; @__DatabasePrincipal sysname --nvarchar(128)
" option on the source database And the "restOre" opTioN on ThE desTinaTion.
When restorINg I uSe the option to Overwrite The exIsTIng dATaBase (WiTH REPLacE).
tHE daTaBaSe cAn BE nAmed tHe sAme on eaCh server (alThoUgH currently thE NaMe does sligHtlY differ on one Of tHe servErS.  ThEY are nAmeD xxx.mYdatabASe and lOcal.mydatAbASe).
Each INstance OF sQl ExpRess has a Security LOGiN "myDatabaseUser".
each daTaBAsE HAs a useR with the saMe Name "MYdatabasEusER".

aFteR Moving the dataBase with thiS MetHod, the CoRreCT usernAmes sTiLL apPear where exPectEd but the USer MapPIngs assOCiated wIth the secUriTy LoGIn HaVe bEeN DelETeD. ThE secUrity LogIn FoR the SErveR AND The USer For tHe datAbase are no lonGer ConnECteD and aPplicAtioNs Fail wHen attEmptiNg To log in.
This probLem iS easy enouGh tO sOlvE mAnualLY by delEtiNg tHe User fRom the dATabAsE AND rE-adding the User MappInGS To The SecurIty LogIN hOwEVer when dOiNg tHis fREQuentLy, it IS qUite annoYing. BeYOnD bEing annOyIng, neeDiNg tO maNuallY RemaP this iNformatiOn cauSEs a brief amount OF downtime. I can GEt IT DoNE preTty Quick, but theRe muST be a BettER wAy tHAt can FurthEr MInIMize this.
I would like tO uSe the bAckup/Restore optionS and have thE USEr mappinGs prESeRVed. I am not familIar wiTh all of the BAckup/resTore oPtions anD what they DO. are there SetTinGs I Can USE tO accomplish thIS?
i havE fUll acceSs tO the serVerS anD loGinS aNd can chanGe things If NeEdEd To mAke thIS happen. ALtHouGh i woUld idEAlly liKe to uSe thE bAcKUp/rEstore oPTiON, i am aLsO wIlliNg tO rUn a paCkagE, QueRies, scrIpts, creatE/usE And eXtErNal progRam, or whatever To Accomplish ThIs tAsk. The MAIn goal is To minimize the steps needeD And possIbiLity oF errOr during tHe dAtabasE trANsfer.

A:

put all sQL-usErS (i mEAN logiNS) InTo tHe Sysadmin roLE.
altErnatIvEly, yOu need tO remap the logins to tHE usErs.
EXEC sp_chanGe_uSers_LOGiN 'AuTO_FIX', 'usEr'

WHerE THe syntax is this:
ExEC sp_changE_uSErS_login 'UsernAme', 'loGinName'

here's hoW I do iT:
        Public oVerride voId RemapDbusErLoGINs(sTring DbNamE)
        {
            string sTrSQL = @"
USE " + ESCapeDBnaMe(DbNAme) + @";

dECLARe @__DAtaBasePRincipAL sySname --NvarchAr(128)
" option on the source dat abase andthe " res tor e"  opt ionon the destina t ion. 
When restoring I usethe o pt i on t o  O verwr ite the ex i s tin gda tab as e  ( WITHREP LACE).The databa seca n be named t h esame on ea chserver (alth oug h curr en tly the n ame  does  sligh t ly dif fer on on eo f thes ervers. Th ey a re named xxx.myda t ab a se and local.m ydatab as e ). E ach  in stance ofSQ L Exp r ess has aS e c uri t y Login "MyDa tabaseUser" . 
Ea ch dat ab ase has aUserwi t h t he same nam e "M yDatabase User". 
Afterm oving t he dat aba sewith th is  me th o d,t he  co r rec t userna me sstill  app e a r wher e e xpec ted b ut the User M app ings ass ociat ed wi th t he  Secu rity L oginha ve been deleted . Th e Securit y L og info r the Server  an d t he User  for th e  da ta b a s eare no longer conn ec t e dand appl icatio n sfa i l when a tt emp ting t o log  in. Th is probl em ise as yenoughto  solve  m anu all y byd elet ing th e User f rom t h e database and re-adding the Us e r  M a ppin gsto the Secu rity Logi n ho w ev erw hen d oingth i sf requently, it is qu it e anno ying.  Beyond being  annoying, n e eding to  man u al l y remap this i nform ation caus e s a brie f amo unt of d owntime.I can getitdon e p ret t y  q uick, but the r e  mus tbe a be tte r way t hat  ca n f urt he r minimiz e this.Iwo ul dlik e tou se the b ac kup /r est ore o p tionsand h aveth eu ser  mappin g sp r eser ve d.  I a m n ot  fami liar wit h all o f the bac kup / rest or eoptions  and what the ydo. Are th er e s etting s I can us e to accomplish this?
I have fu llacces s to  the serv ers  and l ogi n s andcan ch angeth ing s if ne e d ed  to  m ake this h a p pen . Alt ho ughI would  ideally like to u s e t he backup/res tor e op t i on , I am als ow ill i n g to run a pack age, queri es ,  s cripts, cr e ate /u se andexterna l pro g ram, or  whatever  to accom pl isht h istask. Themain goa l is to m i nimiz e  t he st eps  neede dand  poss ibilit y  of  erro r duri ng  the d ataba se  transfe r.

A:

Put all SQL-use rs (imeanlog ins) into  th e  sy sadmin ro le.Alternativ ely , y ou ne edt o rem ap t h elog i ns to  the users.
EX E Csp_ c h an ge_users_lo g i n  'A uto_F ix' ,  'user '

w here the syntax i s  this:
EXEC sp _cha n g e_u ser s _log in  'UserName', ' Log in N a me'

Her e' s how I doit:
     publ ic ove rridevoid Re m a pD b UserLo gins (st ring DbNa me) 
      { 
        st ri ng str SQL =@ "
US E " + EscapeDbName (DbNa m e ) + @ " ;
DECLA RE  @__Dat a base Principalsysname --n varcha r(12 8)
" option_on the_source database and the_"restore" option_on_the destination.
When_restoring_I use the_option to Overwrite_the existing database (WITH_REPLACE).
The database can_be_named the same on each server (although currently the name does slightly differ on_one_of the_servers.__They are named xxx.mydatabase and_local.mydatabase).
Each instance of SQL Express_has a_Security Login "MyDatabaseUser".
Each database has a User with_the_same name "MyDatabaseUser".

After_moving the database with this method, the correct usernames_still appear where expected but the_User Mappings associated_with_the_Security Login have been_deleted. The Security Login for the_Server and the User for the_database are no longer connected and applications_fail when attempting to log in.
This_problem is easy enough to_solve manually_by deleting the User from_the database and_re-adding the_User Mappings to_the Security Login however when doing_this frequently, it_is quite annoying. Beyond being annoying,_needing_to manually remap_this_information_causes a_brief amount of_downtime._I can_get_it done pretty quick, but there_must_be a better way that can further_minimize this.
I would like_to_use the backup/restore options_and have the user mappings_preserved. I am not familiar with_all of_the backup/restore_options and what they do. Are there settings I can use_to accomplish this?
I have full access_to the servers and_logins and_can_change things if_needed_to make_this happen. Although I would ideally like_to use_the backup/restore option, I am also_willing to run a_package,_queries, scripts, create/use and external program,_or whatever to accomplish this task._The main goal is to_minimize_the_steps needed and possibility of_error during the database transfer.

A:

Put all_SQL-users (i mean_logins) into the sysadmin role.
Alternatively, you need_to_remap the logins to the users.
EXEC_sp_change_users_login_'Auto_Fix', 'user'

where the syntax is this:
EXEC_sp_change_users_login_'UserName',_'LoginName'

Here's how I do it:
_     _ public override void RemapDbUserLogins(string DbName)
   _  _ {
   ___      string strSQL = @"
USE_" +_EscapeDbName(DbName) + @";

DECLARE_@__DatabasePrincipal sysname --nvarchar(128)