How do I fix an Orphaned User in Log Shipped version of a SQL Server 2008 database

Question

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?

Answer 1

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.

Answer 2

This may help

Fix SQL Orphaned Users

The problem is that the user in the database is an "orphan". This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-speak) that has to match as well.

This used to be a pain to fix, but currently (SQL Server 2000, SP3) there is a stored procedure that does the heavy lifting.

All of these instructions should be done as a database admin, with the restored database selected.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Answer 3

with

1. all logins created on the other side
2. If the users are just schema users

It is important to notice that, all users are given random GUIDs for Password.

you just need to use a script like the below script for fixing all orphaned users:

 Declare 
  @UserName varchar(100),
  @GUID varchar(1001)

declare MyUserCR cursor for
select Name from sysUsers
where Name not in ('public' , 'Sys' , 'DBO' , 'Information_Schema','Guest')

OPEN MyUserCR 

FETCH NEXT FROM  MyUserCR Into @UserName 
while @@Fetch_Status = 0 
begin 
  set @GUID = NewId()
  exec sp_change_users_login 'Auto_fix' ,@UserName,Null,@GUID
  FETCH NEXT FROM MyUserCR Into @UserName 
end

Close MyUserCR 
deallocate MyUserCR 

Answer 4

Here's how I fixed this issue.

1) Dropped the problematic user on the "primary" server. Initially I had issues because the user had been made an owner of the database that was being log shipped, but once I cleared that up, I was at a clean state.

2) I queried the SID for the user that I wanted to be in sync on the secondary server and copied the SID for use when recreating the login on the primary

SELECT principal_id, sid, name FROM sys.server_principals

3) I then created a login on the primary that matched the login name/SID... this link helped on that: http://blog.sqlauthority.com/2015/04/18/sql-server-create-login-with-sid-way-to-synchronize-logins-on-secondary-server/

4) On the primary, I gave this new user db_datareader right to the log shipped table. When the next update happened, the right got sent over and we were able to read from the secondary version of the table with no issues.