4

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?

6

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.

| improve this answer | |
  • A couple of questions... 1) what would happen if I dropped the user on the production server, but then readded it with the SID of the proper user on the secondary box? if so, how do I get that SID to display so that I create the user properly on the production box 2) assuming the above is not workable, is there going to be an issue with two users of the same name on our development box but with different SIDs? I'm not anxious to break what's on the development box. – Kendor Dec 6 '15 at 1:04
  • Just grab the SID from production and create the login on DEV with the same. – user507 Dec 6 '15 at 1:12
1

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'
| improve this answer | |
0

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 
| improve this answer | |
  • The development box (secondary) has users/logins that I'm nervous about breaking. Is there any way to do this, where I get the SID of the user that I want to also be on the production (primary) from the secondary system, and then create that user on production (primary) with the SID from the secondary box? I may have really fouled things up as I tried to drop the user on the primary box, but was not able to drop that database reference to that user (something to do with ownership). – Kendor Dec 8 '15 at 22:25
  • if you 1. have the login and password, then you can just use it in the code I mentioned, and instead of all users and the GUID, you just need to make the cursor over you list of username and passwords. 2. don't have the logins and passwords but the are already created server level and you have them on you sql server. then you just need to use exec sp_change_users_login 'Auto_fix' ,@UserName instead in the code and don't use the password. hope it helps. – Sina Hassanpour Dec 9 '15 at 8:43
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.