January 13, 2011 at 2:11 pm
Hi,
I have configured log shipping for a database Mydb and the secondary is in Standby mode.
Now I have created a login called scott on secondary instance and trying to map that login to the standby mode database. But I'm getting the below error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for User 'scott'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Failed to update database "Mydb" because the database is read-only. (Microsoft SQL Server, Error: 3906)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=3906&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Please advice
January 13, 2011 at 2:20 pm
You cannot make changes to a db in standby mode.
January 13, 2011 at 2:50 pm
You cannot make changes to a db in standby mode.
Then how a user can query that standby mode database? Because log shipping says, users can query the database if we configure log shipping secondary in a standby mode.Please advice..
My requirement is to logship the primary database to a secondary instance on nightly basis and give access to user on secondary database in business hours to query that database. I mean the restore job on secondary will run from 7pm to 7am.
from the link http://msdn.microsoft.com/en-us/library/ms189970.aspx
Standby mode:
Leave the secondary database in STANDBY mode. This mode will allow read-only operations to be performed on the database
Thanks
January 13, 2011 at 2:58 pm
Existing users can query the log shipping secondary.
You cannot create new users in the secondary because that's a change and the database is read-only (no changes permitted)
You can create the user on the primary and the log shipping will take it over to the secondary. Make sure if you're also creating a login that you explicitly specify the SID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2011 at 3:09 pm
Existing users can query the log shipping secondary.
You cannot create new users in the secondary because that's a change and the database is read-only (no changes permitted)
You can create the user on the primary and the log shipping will take it over to the secondary. Make sure if you're also creating a login that you explicitly specify the SID
without creating a login, how we can create a user? because if we go to a database and try to create a user, then it will ask a login name right? So we must need to create login first in order to create a user right?
Make sure if you're also creating a login that you explicitly specify the SID
Could you please explain me the above a bit more..
Thanks
January 13, 2011 at 3:10 pm
January 13, 2011 at 3:20 pm
Thanks,
It's clear that we must need to create login in order to create a user.
But Is that must to create a user on Primary production Database, in order to access the log shipped standby database on secondary for that user?
In that case we are giving access to Production database unnecessarily. Because the user actually do need access to Production database. He only required access to the secondary database so that he can query that database instead of Prod.
Please advice
January 13, 2011 at 3:21 pm
You create a login and user on the source system.
You then migrate that login to the new system (the user will come through log shipping) using the same SPID.
This should help: http://support.microsoft.com/kb/246133
January 13, 2011 at 3:26 pm
You can create a user without a login in SQL Server 2005 and newer. However, uyou can se these steps to simplify it for you:
1. Create login on production server
2. Create user in database mapped to login
3. Drop login on production server
4. Grant any required permissions to user in database
5. Create login on log shipping standby server
- a. If a SQL authenticated login, create login while specifying SID from production machine
- b. If Windows auth. login, create login without specifying SID
6. Make sure log shipping jobs have completed one full run since completion of step 4
January 13, 2011 at 3:41 pm
You create a login and user on the source system.
You then migrate that login to the new system (the user will come through log shipping) using the same SPID.
But we do not want to create these users in Source production instance. Is there any way to create the logins and give access to standby mode database on secondary?
Thanks
January 13, 2011 at 3:58 pm
You can drop the login without any consequences on the primary system.
January 13, 2011 at 3:59 pm
gmamata7 (1/13/2011)
But we do not want to create these users in Source production instance.
In log shipping, the secondary is an exact copy of primary (at the point of the log backups anyway). You cannot make changes directly to the database on the secondary. You can create logins on the server that will link to users in the DB, but you cannot create users directly in the DB.
Is there any way to create the logins and give access to standby mode database on secondary?
The login you can create on the secondary. The user you have to create on the primary so that the log shipping will copy it over. Follow Robert's steps. If you do you'll have a login-less user on the primary (hence one that cannot be used to log in) and a login linked to the user on the secondary (hence one that you can use to log in)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2011 at 4:10 pm
Hi, I have performed the below steps:
1. on production server(Primary sql server instance), Created a sql server login called Test and mapped the login to the database Mydb
2. Granted db_datareader permissions to login Test
3. Ran the logshipping backup job
4. Ran the log shipping copy and restore jobs
5. Created the the login called Test on secondary instance.
6. Able to see the user Test in Mydb (which is in standby mode) when I logged in as a sysadmin
7. Logged in as the user Test using Management studio
8. Able to see the standby database Mydb. But when I click it, I'm getting the below error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
The database Mydb is not accessible. (ObjectExplorer)
------------------------------
BUTTONS:
OK
------------------------------
Please advice...what should I do to make the user Test to query the standby database Mydb?
Thanks for all your help
January 13, 2011 at 4:22 pm
Test is a SQL login not a Windows login? Then you have to make sure that you specify the SID when creating the login on the standby server or it will get a different SID and will not map to the user.
To query for the SID on the live server:
Select SID
From sys.database_principals
Where name = 'Test'
To create the login on the secondary server:
Create Login [Test]
With SID = '<put SID from query here>',
Password = '<add password here>'
January 13, 2011 at 7:09 pm
On live server, I got the SID of the login and I'm trying to create the login on secondary server as below:
Create Login [Test]
With SID = '0xE1EDB47AD4DEB243A4AA578ACBE92700',
Password = 'Passw0rd'
In above the password field is expecting the binary format and giving the below error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Passw0rd'.
So I tried to copy the same password from the results of the query on live server but still it did not work on secondary server
Select sid,password
From sys.syslogins
Where name = 'Test'
Please advice
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply