Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not able to connect db user in standby database Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 11:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008

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
Post #1393326
Posted Wednesday, December 5, 2012 11:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1393338
Posted Thursday, December 6, 2012 1:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008
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)
Post #1393366
Posted Thursday, December 6, 2012 2:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 6,350, Visits: 13,677
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"
Post #1393391
Posted Thursday, December 6, 2012 3:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008

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.

Post #1393405
Posted Thursday, December 6, 2012 5:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 6,350, Visits: 13,677
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"
Post #1393445
Posted Thursday, December 6, 2012 5:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008
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?

Post #1393451
Posted Thursday, December 6, 2012 5:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:46 PM
Points: 42, Visits: 290
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.
Post #1393454
Posted Thursday, December 6, 2012 6:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 6,350, Visits: 13,677
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"
Post #1393474
Posted Thursday, December 6, 2012 9:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008
Thank you ..&..working fine
Post #1393845
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse