December 5, 2015 at 3:56 pm
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?
December 7, 2015 at 3:49 am
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