Orphaned SQL login in replicated db

Look up this procedure, sp_change_users_login, in BOL and run it on your other system (hopefully). Using log shipping, I'm not sure you will be able to, but worth a shot.

thanks for the replies.

I tried the orphaned users resolutions and this account did show as orphaned but after running EXEC sp_change_users_login 'Auto_Fix', 'user' the account still does not have the secondary replicated db as a mapping in the login properties in SSMS. Any other suggesstions?

Thanks,

TKE402

Stop, breathe. Can the user login and access the database?

No the login in question can not access this particular DB. It has access to the other replicated dbs on this secondary server.

At a lose right now. I had a glimer of an idea but it fled away into the desert (inside joke).

If I can think of anything I'll pop back in.

Post the output of sp_helpuser 'username' after u run it under the db in question.

Thanks for the replies. Here are the results:

username groupname login name DefDBName DefSchemaName UserID SID

test, public, test, usersdb, users , 12 ,0xE247C7..........

When I run this on the primary server I get two entries:

username groupname login name DefDBName DefSchemaName UserID SID

testdb_datareadertestusersdb Select_All_Tables 130x03B9...

testSelect_All_Tablestestusersdb Select_All_Tables 13 0x03B9...

tke402..

run below command on your replicated DB

EXEC sp_change_users_login 'Update_One', 'UserName', 'LoginName'

Theory:

When you restore DB only users are replicated not Logins....that is one of the reason of Orphaned users in the DB.

Try this script.

-- Drop the working table if it already exists

IFOBJECT_ID('tempdb..#tblUsers') IS NOT NULL drop table #tblUsers

GO

-- Get the users into a working table. Code is non-destructive if any of

-- them don't happen to be orphaned.

SELECTIDENTITY(int, 1,1) AS RowNumber,

s.name

INTO#tblUsers

FROMsysusers s

WHEREsid IS NOT NULL AND sid NOT IN (0x00, 0X01)

-- Set up the number of times to loop

DECLARE@RowCounter INTEGER

DECLARE@TheUserName VARCHAR(255)

DECLARE@WorkingString NVARCHAR(255)

SELECT@RowCounter = COUNT(*) FROM #tblUsers

-- Now, loop through each user and link 'em up

WHILE@RowCounter > 0

BEGIN

SELECT@TheUserName = name

FROM#tblUsers

WHERERowNumber = @RowCounter

SELECT@WorkingString = 'sp_change_users_login

@Action = ''Update_One'',

@UserNamePattern = ''' + @TheUserName + ''',

@LoginName = ''' + @TheUserName + ''''

EXECUTEsp_executesql@WorkingString

SET @RowCounter = @RowCounter - 1

END

PRINT'Done!'

thanks for the replies. I did as you said but still no dice. I checked the SQL login properties through SSMS and the mapping to the replicated db is still unchecked. I can not manually add a check through SSMS because I get an error saying that the db is in read only mode.

Thanks SSC-Enthusiastic

I ran the script on the secondary server master db and received a bunch of errors:

Terminating this procedure. The User name 'db_accessadmin' is absent or invalid.

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108

Terminating this procedure. The User name 'db_owner' is absent or invalid.

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108

Terminating this procedure. The User name 'DOMAIN\GROUP' is absent or invalid.

I then ran it against the replicated db and I get the same error messages except the additional mention that the db is in read only mode:

Terminating this procedure. The User name 'db_accessadmin' is absent or invalid.

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108

Terminating this procedure. The User name 'db_owner' is absent or invalid.

Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 123

Failed to update database "DB" because the database is read-only.

Can you please run this, i believe this SQL 2005 instance.

select name, is_read_only, state_desc from sis.databases.

If you see is_ready_only column value as '1' for the DB you have issue, Do this.

alter database Databsename set read_write with rollback immediate, it should fix.

EnjoY!

Hi,

The DB is a replicated db due to log-shipping. Isn't it supposed to be in read only standby mode? This is the error I get:

Database 'DB' is in warm standby. A warm-standby database is read-only.