Log shipping, Orphaned User, and Read-only Issue

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

    We are doing development work on an instance of MSSQL 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?

  • kenny 40254 (12/5/2015)


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

    We are doing development work on an instance of MSSQL 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?

    You need to create the login on the target server with the same SID as the source server.

    Script the login with its SID from the source server and apply this to the dev server. This script should help

    SELECT'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +

    sys.fn_varbintohexstr(password_hash) +

    ' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +

    ', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +

    ', DEFAULT_LANGUAGE = ' + default_language_name +

    ', CHECK_EXPIRATION = ' +

    CASE

    WHEN is_expiration_checked = 0 THEN 'OFF'

    ELSE 'ON'

    END +

    ', CHECK_POLICY = ' +

    CASE

    WHEN is_policy_checked = 0 THEN 'OFF'

    ELSE 'ON'

    END +

    CASE is_disabled

    WHEN 0 THEN ''

    ELSE '; ALTER LOGIN [' + name + '] DISABLE;'

    END

    FROM master.sys.sql_logins

    WHERE name = 'theloginname'

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply