April 28, 2010 at 8:51 am
Hi everybody, I wonder if you can help.
I recently set up log shipping (SQL 2005), and so have a readonly/standby database that the logs are being shipped to. The idea was that this database would be used for reporting to take stress off the live box.
However, of course the users on the target box need to be fixed in order for this to work, but I can't do that on a readonly/standby db. What can I do?
As far as I know when setting up log shipping I have no option but to set the target to standby/readonly in the first place so how can I ever fix the users?
Any ideas?
Many thanks
Farren
April 28, 2010 at 9:11 am
fminns-1058143 (4/28/2010)
Hi everybody, I wonder if you can help.I recently set up log shipping (SQL 2005), and so have a readonly/standby database that the logs are being shipped to. The idea was that this database would be used for reporting to take stress off the live box.
However, of course the users on the target box need to be fixed in order for this to work, but I can't do that on a readonly/standby db. What can I do?
As far as I know when setting up log shipping I have no option but to set the target to standby/readonly in the first place so how can I ever fix the users?
Any ideas?
Many thanks
Farren
You need to be more specific by "fix the users". All tables are shipped over, if someone has the permissions in prod, they will have the permissions on the recipient server. However, you have to create a login for the user on the secondary. But since users and permissions are stored in the database, those cannot be changed.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 28, 2010 at 9:17 am
Hi
The same login already exists on the target server but I assume the SID doesn't match any more (which I would expect when restoring a db from one server to another).
But is there a way round this? As far as I can tell this would never be any different. To enable log shipping I have to set the targeg db as standby/readonly, but then I can no longer fic the user in question to match the correct login to the required user.
Farren
April 28, 2010 at 10:34 am
The SID's are the same for NT Users, use this SP for SQL users: sp_change_users_login
http://msdn.microsoft.com/en-us/library/ms174378.aspx
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 28, 2010 at 9:34 pm
Whilst you can change the SID if needed to match the SID of the User, you can avoid the problem by specifying the SID when you create the LOGIN on the standby server. This is an option that is available when you you the system stored proc to create the login. If you specifiy the SID of the login from the server you are log shipping from, the login will automatically be able to access the standby database
April 29, 2010 at 3:18 am
Ok, thanks for that, but i'm still a bit confused (I'm a pretty new SQL admin btw 🙂
On the target server the login and db user already exists. So do I need to delete the login and recreate it while specifying the SID from the db being log shipped from? If so, how can I find out the current SID of that user?
Also, will this process still not need to update the db (being log shipped to) in some way, and if so I assume that can't be done seeing as it's in standby/readonly mode?
Sorry for the all the questions.
Cheers
Farren
April 29, 2010 at 6:22 am
If you've already got the users set up in the Target server, don't worry about deleting and creating. As suggested earlier, just use sp_change_users_login and that will fix the database.
However, to find the users SIDS, do a query on system catalog view syslogins. (No period between the sys and the logins). It has a column called SID.
BTW, I just have to note that when I read the subtitle of this post, I was very tempted to respond only with: "You can't fix users. They're broken by default. It's why Help Desks have job security." :w00t:
April 30, 2010 at 11:00 am
sp_change_users_login will not work on read only database.
You should transfer SQL logins with SID as it described below.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply