| Author |
Topic |
|
vpathisseril
Starting Member
6 Posts |
 Posted - 2003-11-20 : 15:12:20
|
| Hi,Is there any way I can Sync Users two DB.I want to take a backup from my production server and restore in to a Development Server. The issues is how can I create all users and there previllages. I used to use to "sp_change_users_login update_one, 'UserId', 'UserId'" in SQL 7.0. This statement is not valid in 2000.Any suggestions?ThanksVarghese |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-20 : 15:29:33
|
| To transfer accounts, use isp_Transfer_Logins. It is a stored procedure that I wrote to transfer the logins between two server. Do a forum search here for the code for it. Then run sp_change_users_login script. And yes the statement is valid in SQL 2000, but the account probably hasn't been created yet. Starting with service pack 3a, the account has to exist already for security reasons. Then you run sp_change_users_login to unorphan the account. Here is the link to the script:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615Tara |
 |
|
|
denisemc
Starting Member
26 Posts |
Posted - 2003-11-20 : 16:10:03
|
| sp_change_users_login is a valid stored proc in SS2K. What error are you getting?Denise |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-20 : 16:15:48
|
| The error is most likely due to service pack 3a. With service pack 3a, the stored procedure no longer creates the account for you with a blank password. You have to do that step for security reasons.Tara |
|
|
|
denisemc
Starting Member
26 Posts |
Posted - 2003-11-21 : 12:59:56
|
quote:
With service pack 3a, the stored procedure no longer creates the account for you with a blank password.
Sorry, I'm confused...I thought that in order to use sp_change_users_login the login had to exist already. Does it try to create the login that you are mapping if it doesn't already exist?Denise |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-21 : 13:12:15
|
| Prior to service pack 3a, the account did not have to exist. It would create it for you but with a blank password. If you have service pack 3a installed, then you must have the accounts created first.Tara |
|
|
|
denisemc
Starting Member
26 Posts |
Posted - 2003-11-21 : 13:19:44
|
| Hmmm, this might be one of those does-not-work-as-advertised features. I just tried the same command on an SP2 and an SP3a server, and got the same behavior. If the login doesn't exist, it throws an error:version: SP3Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 96Terminating this procedure. The Login name 'testthis' is absent or invalid.version: SP2Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 96Terminating this procedure. The Login name 'testthis' is absent or invalid.Denise |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-21 : 13:22:29
|
| Yes, that will happen with the UPDATE_ONE option. If you use AUTOFIX, then it will create the account for you, except on service pack 3a or later.Tara |
|
|
|
denisemc
Starting Member
26 Posts |
Posted - 2003-11-21 : 13:25:10
|
| ah, got it, thanks.and, yikes, what a security hole that was!Denise |
|
|
|
|
Create or Sync Login