PROD to QA

Question

Post reply

PROD to QA

Jimmpy Gill

Say Hey Kid

Points: 670
More actions
April 30, 2007 at 8:57 pm

#70021

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jimmpy Gill Say Hey Kid Points: 670 More actions · Answer 1

Jimmpy Gill

Say Hey Kid

Points: 670

May 1, 2007 at 8:10 am

#703538

anybody?

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

What goes wrong when you run sp_change_users_login?

Greg

Jimmpy Gill Say Hey Kid Points: 670 More actions · Answer 3

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)').

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

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

Jimmpy Gill Say Hey Kid Points: 670 More actions · Answer 5

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.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

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

Jimmpy Gill Say Hey Kid Points: 670 More actions · Answer 7

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??

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

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.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 9

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

Tom Schuettke SSCarpal Tunnel Points: 4127 More actions · Answer 10

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.

Syntax

sp_change_users_login [ @Action = ] 'action'

[ , [ @UserNamePattern = ] 'user' ]

[ , [ @LoginName = ] 'login' ]

[ , [ @Password = ] 'password' ]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 11

"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