December 5, 2012 at 11:05 pm
For the Reporting purpose, we need to use another server standby database and logshipping have been configured for DR setup
exec sp_change_users_login @Action='update_one', @UserNamePattern='drdev',
@LoginName='drdev';
GO
Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 129
Failed to update database "drdev" because the database is read-only.
please guide me how to resolve this issues?
rgds
ananda
December 5, 2012 at 11:58 pm
What you are trying to do in effect is modify the system tables underneath the sys.database_principals catalog view which is disallowed when the database is read-only. Instead you'll need to align the Server Principal with the Database Principal by dropping the Server Login on the secondary instance and recreating it using the same SID that was used on the primary instance.
How to transfer logins and passwords between instances of SQL Server
PS You will no longer need sp_change_users_login for this scenario but for future needs please consider switching to use ALTER USER instead. sp_change_users_login was marked obsolete in SQL Server 2008 and will be removed in a future version of the product.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 6, 2012 at 1:40 am
Thanks for reply..
I try to created new user & login on the secondary server instance, but still facing same issues...not able to create new user on the standby read-only daabase
Error msg
Failed to update database "SALEPROD" because the database is read-only. (Microsoft SQL Server, Error: 3906)
December 6, 2012 at 2:56 am
ananda.murugesan (12/6/2012)
Thanks for reply..I try to created new user & login on the secondary server instance, but still facing same issues...not able to create new user on the standby read-only daabase
Error msg
Failed to update database "SALEPROD" because the database is read-only. (Microsoft SQL Server, Error: 3906)
The users are NOT created on the secondary standby database, you must create them on the primary first. The log shipping process will then apply the new users to the standby database.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 6, 2012 at 3:31 am
I have doubt..
Log shipping process have been configured on the primary database also transaction logs apply to secondary database instance, then after create new db user in primary instance then how it will be transfered to secondary database for connecting report module.
December 6, 2012 at 5:08 am
ananda.murugesan (12/6/2012)
I have doubt..
I think if this didn't work as designed Microsoft would have known about it by now 😉
ananda.murugesan (12/6/2012)
Log shipping process have been configured on the primary database also transaction logs apply to secondary database instance, then after create new db user in primary instance then how it will be transfered to secondary database for connecting report module.
Ok, let me explain a little
The creating of all database users must be carried out on the primary instance first, the log shipping processes then ship the logs to the secondary. During a restore the log containing the action
CREATE USER ........
is applied to the secondary database.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 6, 2012 at 5:24 am
yes..Thanks
user apperaing in secondary instance database by atuomatically during restore log shipping process..But it won't connect the application due to login is not apperaing under security->Login, Does we need to create login mannually?
December 6, 2012 at 5:26 am
ananda.murugesan (12/6/2012)
yes..Thanksuser apperaing in secondary instance database by atuomatically during restore log shipping process..But it won't connect the application due to login is not apperaing under security->Login, Does we need to create login mannually?
Yes Sir. Otherwise the users will be orphaned.
December 6, 2012 at 6:21 am
ananda.murugesan (12/6/2012)
yes..Thanksuser apperaing in secondary instance database by atuomatically during restore log shipping process..But it won't connect the application due to login is not apperaing under security->Login, Does we need to create login mannually?
Yes, you can either use the stored procedure
sp_help_revlogin
Alternatively, you could use this to generate a create login statement from the primary server for a single login 😉
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
from sys.sql_logins
where name = 'yoursqllogin'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 6, 2012 at 9:31 pm
Thank you ..&..working fine:-D
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply