SQL Server 2008 – Fixing Orphaned User in Log Shipped Database

log-shippingsql-server-2008

Forgive my greenness.. but would appreciate some help on this…

We are doing development work on an instance of SQL Server 2008 and have multiple databases on that server that are core to our application along with a particular user that in some cases owns database, and in some cases can just run queries against them.

There is a particular database that resides on a production server that we don't want to touch, so we've set up log shipping to replicate its data to our development box. That database is properly restored on our development box and receiving ongoing updates.

On the production box, we added the same user that's need on our development box to the database that's doing the log shipping

When we try to query the log shipped version of database (read-only) from our application on the development box, our user can't access the database under the "current security context."

I ran a stored procedure that verified that my user is indeed orphaned…

I've run into this scenario before when restoring databases to a different machine, and have run a stored procedure to fix the "orphaned" user; however, because the development box's version of the database is read-only the stored procedure won't work.

Any suggestions on how to deal with this?

Best Answer

If you are running sp_change_users_login 'Report' to get that list of orphaned users it will return the SID expected.

Just take that SID and create your login with the same SID:

CREATE LOGIN [mylogin] WITH PASSWORD='', SID = <SID for orphaned user>

This should allow your login to be mapped to that user in the database and allow you to access the database.

If you want to copy a login from production to your development box you can query sys.syslogins to get the SID, or just use sp_help_revlogin procedure from KB918992.

Related Question