Click here to monitor SSC
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 3465
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
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8233 Visits: 14368
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 3465
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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 16554
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 3465
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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 16554
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 3465
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 Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 16554
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 3465
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