March 24, 2006 at 3:45 pm
Greetings, I have set up a standby database using log shipping. We would like to be able to query the database occasionally to make sure that changes are in fact being propagated. The only way I have been able to do this is to add the user to the sysadmin role. There must be another way. If someone can help me I would greatly appreciate it. Thanks.
March 25, 2006 at 9:11 am
Are the SIDs for the logins the same on both servers? The standby database may have different security IDs for logins with the same name, so the users are not recognized.
March 27, 2006 at 4:43 pm
In the Query Analyzer I execute the following query against each server...
select suser_sid('user')
and a different value is returned for each sid so I guess that means they are different. Does that mean I can not give a user rad-only access without making them a member of sysadmins or is there a way around it?
Thanks.
March 28, 2006 at 8:47 am
Search sp_helprevlogin on Technet and use that to move the logins you need to the new server.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 28, 2006 at 9:15 am
You can't make any changes to the log-shipped database, so there's no way for you to add users or modify permissions.
You have to sync the SIDs for the logins between the two servers. You'll have to drop the logins on the log shipping destination server and recreate them with the correct SIDs. The sp_help_revlogin info Steve refers to is at http://support.microsoft.com/kb/246133/, although you could probably figure out how to do it by looking up sp_addlogin in BOL.
March 28, 2006 at 4:43 pm
Thank you for the suggestions. I will check into this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply