April 30, 2007 at 8:57 pm
I’ve moved a copy of prod db over to QA server, now I’m trying to fix logins (match sids.. etc) on QA server by running (sp_change_users_loign and it doesn’t work). Other way I could do is by scripting users on source server and running it on target. I’m trying to do a test where Prod1 SQL 2000 server fails and I’ve the backup in the tape drive. I would restore on prod2 from the backup, how can I fix logins in this case? It’s sort of a DR question. Can anybody help on this plz? Thanks
May 1, 2007 at 8:10 am
anybody?
May 1, 2007 at 9:37 am
What goes wrong when you run sp_change_users_login?
Greg
Greg
May 1, 2007 at 10:17 am
I get something like..
Server: Msg 15290, Level 16, State 1, Procedure sp_change_users_login, Line 166
Terminating this procedure. The Action 'AUTO_FIX' is incompatible with the other parameter values ('UserName', '(null)').
May 1, 2007 at 10:28 am
Hmm. AUTO_FIX only works if the user name and login name are identical. If they're not, you'll have to use the UPDATE_ONE argument and specify the user and login that you want link. Is that the problem?
Greg
Greg
May 1, 2007 at 2:57 pm
Login doesnt exist on the server but users are part of user db which are need to be fixed on QA server. Update_one doesnt help either:
sp_change_users_login 'update_one', TestUser, 'TestUser'
Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 96
Terminating this procedure. The Login name 'TestUser' is absent or invalid.
May 1, 2007 at 3:10 pm
sp_change_users_login doesn't create logins; it maps existing users to existing logins. If the login doesn't exist on the QA server, you'll need to create it then map the database user to it.
Greg
Greg
May 1, 2007 at 3:25 pm
So users should be scripted on weekly basis in case of DR, databases/users/jobs can be move to other server if you are in standalone environment??
May 1, 2007 at 4:22 pm
Users and logins are two different things. Users are copied with the database when you restore or attach it to the QA server. Logins must be copied over by scripting, by a DTS transfer logins task, or some other method.
You want to make sure the logins are created with the same SID on the QA server that they had on the prod server, then you can avoid all the sp_change_users_login calls.
May 2, 2007 at 9:58 am
Yes, the sp_help_revlogin procedure in this article makes it easy to script logins: http://support.microsoft.com/kb/246133/en-us. I've used it many times. Logins only need to be transferred when a new one is added to the prod server.
Greg
Greg
May 3, 2007 at 8:59 am
In SQL 2005 and SQL 2000 SP3 and later, sp_change_users_login does create a login if it is missing, so there is no need to have to script all logins and create them first.
This only works with the auto_fix action.
sp_change_users_login [ @Action = ] 'action'
[ , [ @UserNamePattern = ] 'user' ]
[ , [ @LoginName = ] 'login' ]
[ , [ @Password = ] 'password' ]
May 3, 2007 at 9:26 am
"sp_change_users_login does create a login if it is missing"
But, again, only if the user and login have the same name.
Greg
Greg
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply