March 3, 2009 at 2:38 pm
I am running log shipping on SQL 2000 and am running into permissioning problems on one of my servers. The maintenance plan is log shipping a database from server A to servers B and C. The logs are loading fine in both servers and as sysadmin I can select data from both logship dbs via query analyzer. Server B has the correct permissions and end users can run select statements from it. Server C will not allow the same user to run a select statement due to permissions.
I would like to temporarily take the db out of standby/read-only just for a moment on server C to revoke and grant (sp_revokedbaccess and sp_grantdbaccess) then put it back into standby mode to continue loading logfiles.
Thanks.
March 3, 2009 at 3:13 pm
If you’ll recover the secondary database in order to modify anything in it, you’ll have to rebuild the log shipping and you’ll end up in the same situation as you are now. In any case I think that you can correct this situation in another way. Most chances that you simply have users in the database on server C that are mapped to SID that doesn’t belong to any valid login on that server. If you’ll run sp_change_users_login to report orphaned users in the database (on server C), you’ll be able to see if there are such users. In case that there are, you can drop the logins from the master and recreate them with the same SID as they have in the original server. You can read about how to transfer the logins from this URL - http://support.microsoft.com/kb/246133. Don’t forget to create a script first that maps those logins to the other databases that they are using and grant them the same permissions as they have now.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 3, 2009 at 3:17 pm
Its more probable the users on server C have become orphaned. run sp_helpuser or select * from sysusers and see if the login column is NULL.
Did you set the logins up in the same way on both secondary servers?
Don't bring the database online, you will have to re-initialise log shipping from a full backup if you do that.
sorry adi, posts crossed
---------------------------------------------------------------------
March 4, 2009 at 10:05 am
Adi, you were spot on in your assessment and solution. I was able to bring the correct user and SID from server A over to server C in my database, drop the user from server C master db, then run sp_addlogin to recreate it in the master db with the same SID. I am able to log ship and the user can select from the standby database. Thanks So Much!
March 4, 2009 at 2:30 pm
Well to be fair about it, Goerge also was correct. I just typed it faster then he did:-). In any case I’m glad that both posts solved your problem.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply