InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:36:16 GMT20http://www.sqlservercentral.com/Forums/Topic1393326-1550-1.aspxThank you ..&..working fine:-DThu, 06 Dec 2012 21:31:32 GMTananda.murugesanhttp://www.sqlservercentral.com/Forums/Topic1393326-1550-1.aspx[quote][b]ananda.murugesan (12/6/2012)[/b][hr]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? [/quote]Yes, you can either use the stored proceduresp_help_revloginAlternatively, you could use this to generate a create login statement from the primary server for a single login ;-)[code="SQL"]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 = ' +casewhen is_expiration_checked = 0 then 'off'else 'on'end + ', CHECK_POLICY = ' + casewhen is_policy_checked = 0 then 'off'else 'on'endfrom sys.sql_loginswhere name = 'yoursqllogin'[/code]Thu, 06 Dec 2012 06:21:29 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1393326-1550-1.aspx[quote][b]ananda.murugesan (12/6/2012)[/b][hr]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? [/quote]Yes Sir. Otherwise the users will be orphaned.Thu, 06 Dec 2012 05:26:23 GMTarunyadav007http://www.sqlservercentral.com/Forums/Topic1393326-1550-1.aspxyes..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? Thu, 06 Dec 2012 05:24:00 GMTananda.murugesanhttp://www.sqlservercentral.com/Forums/Topic1393326-1550-1.aspx[quote][b]ananda.murugesan (12/6/2012)[/b][hr]I have doubt..[/quote]I think if this didn't work as designed Microsoft would have known about it by now ;-)[quote][b]ananda.murugesan (12/6/2012)[/b][hr]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.[/quote]Ok, let me explain a littleThe 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[code="SQL"]CREATE USER ........[/code]is applied to the secondary database.Thu, 06 Dec 2012 05:08:48 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1393326-1550-1.aspxI 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.Thu, 06 Dec 2012 03:31:35 GMTananda.murugesanhttp://www.sqlservercentral.com/Forums/Topic1393326-1550-1.aspx[quote][b]ananda.murugesan (12/6/2012)[/b][hr]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 daabaseError msgFailed to update database "SALEPROD" because the database is read-only. (Microsoft SQL Server, Error: 3906)[/quote]The users are [b][u]NOT[/u][/b] 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.Thu, 06 Dec 2012 02:56:09 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1393326-1550-1.aspxThanks 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 daabaseError msgFailed to update database "SALEPROD" because the database is read-only. (Microsoft SQL Server, Error: 3906)Thu, 06 Dec 2012 01:40:59 GMTananda.murugesanhttp://www.sqlservercentral.com/Forums/Topic1393326-1550-1.aspxWhat 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.[u][url=http://support.microsoft.com/kb/918992]How to transfer logins and passwords between instances of SQL Server[/url][/u]PS You will no longer need sp_change_users_login for this scenario but for future needs please consider switching to use [u][url=http://technet.microsoft.com/en-us/library/ms176060(v=sql.105).aspx]ALTER USER[/url][/u] instead. sp_change_users_login was marked obsolete in SQL Server 2008 and will be removed in a future version of the product.Wed, 05 Dec 2012 23:58:39 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1393326-1550-1.aspxFor the Reporting purpose, we need to use another server standby database and logshipping have been configured for DR setupexec sp_change_users_login @Action='update_one', @UserNamePattern='drdev', @LoginName='drdev';GOMsg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 129Failed to update database "drdev" because the database is read-only.please guide me how to resolve this issues?rgdsanandaWed, 05 Dec 2012 23:05:54 GMTananda.murugesan