Thursday, April 23, 2009

Solution to Log Shipping and Problem SQL Users

The issue is that the SQL Users that are transfered with a log shipped database do not have the same SID as the SQL user on the destination server.  In order to make them compatible, you must create capture the SID/Login of the user from the Source Database Server and recreate the User on the destination Database Server.

This makes it so you don't have to modify the database on the destination server.

See this article on Microsoft Support:

How to transfer the logins and the passwords between instances of SQL Server 2005

This also helps if you are always restoring your development and test environments from production.  So, What I did was capture the user off of my production database server and populated it to my other databases.