Monday, August 13, 2012

Create Login in Logshipping (esp. for Read-only mode DB) or Mirroring DB same as in Primary


Get the login SID info from the primary DB using sp_help_revlogin. Then run the create user with the SID info in the Secondary.

sp_help_revlogin 'loginname

Example output is
CREATE LOGIN [loginname]

WITH PASSWORD = 0x0 HASHED, SID = 0x89C64364B6A88A4A944B3FFB29D42B53,

DEFAULT_DATABASE = [default_db], CHECK_POLICY = ON/OFF, CHECK_EXPIRATION = ON/OFF'

  If the created login has access to other DB's then use sp_change_users_login to fix those orphaned users.

Refer to transfer logins between different SQL servers for more options to move it.

http://support.microsoft.com/kb/246133