Thursday, August 14, 2014

Capture the SQL User for Log Shipped Servers

When you restore a database to another SQL Server Instance the SQL Server User login will not match up because they will have different SIDs.
This code can be used to capture the create login script from the source server in a log shipping scenario or where you don't want to have to recreate the users on the individual databases.

SELECT'CREATE LOGIN ['+ loginname +']'
  ,' With PASSWORD ='
  , cast(password AS varbinary(256))
  ,' HASHED , SID = '
  , sid,', DEFAULT_DATABASE=['+ dbname +']'
  ,', DEFAULT_LANGUAGE = ['+ language +']'
  ,', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'/* modify as needed */
FROM syslogins
WHERE isntname =0 AND name IN('[username]', '[username2]')
ORDER BY loginname

Once you run this script you can copy it's results and run on the target server.
- Paul

No comments:

Post a Comment