March 22, 2012 at 8:52 am
I have a SQL 2008 database which is in read-only mode..
There is a new user request from the customer who needs access on this server..
How will I be able to cater this request?
March 22, 2012 at 8:53 am
alter the database to read_write then add the user then change it back to read_only.
The probability of survival is inversely proportional to the angle of arrival.
March 22, 2012 at 8:56 am
The DB is in Standby too..Does it still work the way you advised..
March 22, 2012 at 9:00 am
Benki Chendu (3/22/2012)
The DB is in Standby too..Does it still work the way you advised..
No. Looks like the database may be the target of log shipping or was restored using WITH STANDBY to allow the database to be used read-only but allow subsequent t-logs to be applied.
March 22, 2012 at 9:02 am
I have another relevant doubt..
There is a windows user named X which is there on my production server and it has DBO rights on my production database..
Now, I want to back this prod database and put this as standby/read-only on a different server..
After I restore it, I may need to create this login account on the new server..
I then restore this database as standby/read only..
Now, when I run sp_change_users_login 'report', this windows login doesn't show up in the results..Does it mean, windows accounts are obviously fixed and doesn't behave like orphans?
March 22, 2012 at 9:02 am
If it's a log shipping secondary, add the user to the primary DB and make sure that the SIDs of the logins are the same on both servers.
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
March 22, 2012 at 9:03 am
Lynn Pettis (3/22/2012)
Benki Chendu (3/22/2012)
The DB is in Standby too..Does it still work the way you advised..No. Looks like the database may be the target of log shipping or was restored using WITH STANDBY to allow the database to be used read-only but allow subsequent t-logs to be applied.
True..It was restored WITH STANDBY option..
Now, whats the solution..Please read my above post too and suggest
March 22, 2012 at 9:03 am
GilaMonster (3/22/2012)
If it's a log shipping secondary, add the user to the primary DB and make sure that the SIDs of the logins are the same on both servers.
Its not in logshipping..Its only a warm standby...
Can you please elaborate on making SIDS of logins same on both servers..
March 22, 2012 at 9:04 am
Windows accounts get the SID from AD, so it'll be the same on both servers hence the users can't be orphaned because the SIDs match on the two servers.
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
March 22, 2012 at 9:06 am
Benki Chendu (3/22/2012)
The DB is in Standby too..Does it still work the way you advised..
Okay, I am suspecting that this server is maintained in synch with a primary server via log shipping and left in standby mode after each log restore.
I am assuming you are talking about a new LOGIN as well as a new user. You should add the login to the primary server and associate with a user (or create a new user) in the primary database. Then just add that login to your standby server and after the next log restore the login-user will have access.
The probability of survival is inversely proportional to the angle of arrival.
March 22, 2012 at 9:08 am
Benki Chendu (3/22/2012)
GilaMonster (3/22/2012)
If it's a log shipping secondary, add the user to the primary DB and make sure that the SIDs of the logins are the same on both servers.Its not in logshipping..Its only a warm standby...
Can you please elaborate on making SIDS of logins same on both servers..
RESTORE DATABASE [databasename] WITH RECOVERY
After this, the database will be active and no more t-logs can be applied. If this is not what you need, then add the users to the source database, do a t-log backup there and apply all necessary t-logs including the last one ensuring that you use WITH STANDBY if you still want the database ready to accept additiional t-log backups.
March 22, 2012 at 9:10 am
Benki Chendu (3/22/2012)
GilaMonster (3/22/2012)
If it's a log shipping secondary, add the user to the primary DB and make sure that the SIDs of the logins are the same on both servers.Its not in logshipping..Its only a warm standby...
Can you please elaborate on making SIDS of logins same on both servers..
You can script the specific login on the primary and create it on the secondary and the IDs will then match (this is assuming a SQL server login).
Of course... you can change the actual login name and password on your standby server so that the login will NOT have access to the primary... only the standby.
The probability of survival is inversely proportional to the angle of arrival.
March 22, 2012 at 9:20 am
ohh..I am in trouble now..
I don't know from where the customer got the database backup from..
Here is what I did...
Restored the database with recovery..
Added that windows user to the database as DBO user..
Backup the database
Restored the database WITH STANDBY/read-only option
will this help suffice the request?
Please note - This is not working with SQL Server Log shipping phenomenon..
They are doing this whole activity manually..
March 22, 2012 at 9:28 am
Benki Chendu (3/22/2012)
ohh..I am in trouble now..I don't know from where the customer got the database backup from..
Here is what I did...
Restored the database with recovery..
Added that windows user to the database as DBO user..
Backup the database
Restored the database WITH STANDBY/read-only option
will this help suffice the request?
Please note - This is not working with SQL Server Log shipping phenomenon..
They are doing this whole activity manually..
Okay, I understand you are not using log shipping now. Since you are restoring the database purely for the purpose of customer access then just restore and recover (not WITH STANDBY), then add the login(s) and user(s) you want, then place the database in read only mode.
The probability of survival is inversely proportional to the angle of arrival.
March 22, 2012 at 9:30 am
sturner (3/22/2012)
Benki Chendu (3/22/2012)
ohh..I am in trouble now..I don't know from where the customer got the database backup from..
Here is what I did...
Restored the database with recovery..
Added that windows user to the database as DBO user..
Backup the database
Restored the database WITH STANDBY/read-only option
will this help suffice the request?
Please note - This is not working with SQL Server Log shipping phenomenon..
They are doing this whole activity manually..
Okay, I understand you are not using log shipping now. Since you are restoring the database purely for the purpose of customer access then just restore and recover (not WITH STANDBY), then add the login(s) and user(s) you want, then place the database in read only mode.
Yeah..This is what I have done now..Upto the customer to confirm if its working or not now:-)
Thanks all..I will keep bugging you all till this ticket gets closed 😉
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply