Login Sync

[YL5956]

I restore a copy of our production database into a test machine. I want to sync up the user logins. I'm running into problems when I try to create a new user. It says a user already exist. How can I resolve this? Do I go into the SYSUSER table on that database and delete the user then recreate them? How do I sync the passwords? Is this a clean way to recreate logins or is there a better way? Thanks.

 

[tlbroadbent]

Use sp_change_users_login in the database to synchronize users and logins. The SP is documented in SQL BOL and at this link.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[YL5956]

Terry,

Thanks for you help but what if I don't want to recreat all the logins? how do I clean out the database? Do I just delete the rows from SYSUSERS that I don't need?

 

[tlbroadbent]

Try using sp_revokedbaccess to drop users. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.