SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Not able to connect db user in standby database


Not able to connect db user in standby database

Author
Message
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6611 Visits: 3501
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38412 Visits: 14411
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
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6611 Visits: 3501
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)
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52559 Visits: 17672
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" ;-)
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6611 Visits: 3501
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.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52559 Visits: 17672
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" ;-)
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6611 Visits: 3501
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?
arunyadav007
arunyadav007
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 299
ananda.murugesan (12/6/2012)
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?



Yes Sir. Otherwise the users will be orphaned.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52559 Visits: 17672
ananda.murugesan (12/6/2012)
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?


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" ;-)
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6611 Visits: 3501
Thank you ..&..working fine:-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search