User Mapping script

• krypto69

  SSChampion

  Points: 13604

  More actions

  February 22, 2011 at 9:16 am

  #232436

  Hi,

  I have to drop and re-create a DB nightly.

  When I do that my user loses it's mapping (read_only) to that DB.

  Is there a way to create a script (that I can schedule as a job) that will recreate the read only mapping to that Db?

• krypto69

  SSChampion

  Points: 13604

  More actions

  February 22, 2011 at 10:01 am

  #1289940

  got it myself thanks anyway...

  CREATE LOGIN [UserA] WITH PASSWORD=N'UserA',

  DEFAULT_DATABASE=[OrphanedUsers],

  CHECK_EXPIRATION=OFF,

  CHECK_POLICY=OFF

  GO

• krypto69

  SSChampion

  Points: 13604

  More actions

  February 22, 2011 at 10:09 am

  #1289944

  oh wait that's not gonna work that user already exists....

• homebrew01

  SSC Guru

  Points: 55549

  More actions

  February 22, 2011 at 10:51 am

  #1289974

  I assume you're adding the user back manually every day ? Take that script and put it into a new job, then schedule to run at the appropriate time after the database is created.

  SQL ServerAgent -> Jobs -> <right click> NewJob

• krypto69

  SSChampion

  Points: 13604

  More actions

  February 22, 2011 at 11:53 am

  #1290015

  I'm creating only the DB mapping, the user is still there.

  the mapping to the dbis what goes away

• Judy Why

  Hall of Fame

  Points: 3656

  More actions

  February 22, 2011 at 11:58 am

  #1290020

  if the user exists in the DB which you just restored,

  you can schedule a job to run this after you restore DB

  EXEC sp_change_users_login 'update_one', @username, @username