QOD 6/17
-
This has got to be another question that will raise some 'disscussion'. I'm old school I guess so I'm used to removing a user and re-adding them, then fixing persissions as necessary, so the sp_change_users_login is new to me... although it may have been there all along. But reading the BOL it clearly states that some of the parameters are missing on the syntax provided in the question.
BOL Quote:
When using Auto_Fix, you must specify user and password; login must be NULL. user must be a valid user in the current database.
So that leaves the delete user and recreate as the only 'viable' option.
Am I wrong?...
--Tom
-
I agree with the fact that sp_change_user_login is the right way to it but I'm not happy with the AUTO_FIX option. Update_one seems to be the better way.
[font="Verdana"]Markus Bohse[/font]
-
Books online seems to say that both username and password must be supplied in order to run the sp_change_users_login procedure.
Is this correct or have I just answered another question really badly<g>
---------------------------------------------
If data can screw you, it will; never assume the data are correct. -
I think correct answer should be :
EXEC sp_change_users_login 'Update_One', 'webuser', 'webuser'
-
quote:
This has got to be another question that will raise some 'disscussion'. I'm old school I guess so I'm used to removing a user and re-adding them, then fixing persissions as necessary, so the sp_change_users_login is new to me... although it may have been there all along. But reading the BOL it clearly states that some of the parameters are missing on the syntax provided in the question.BOL Quote:
When using Auto_Fix, you must specify user and password; login must be NULL. user must be a valid user in the current database.
So that leaves the delete user and recreate as the only 'viable' option.
Am I wrong?...
--Tom
I think the clarifying part of BOL is a little farther down on the page:
quote:
If a matching login already exists, the user and login are mapped and password is ignored. If a matching login does not exist, sp_change_users_login creates a new SQL Server login and assigns password as the password for the new login.
That seemed to be the answer to me.
-Caleb
-
Supplying the password in sp_change_users_login is new with SP3. I guess to be completely up to date they should have included that.
-
quote:
Supplying the password in sp_change_users_login is new with SP3. I guess to be completely up to date they should have included that.
Ahh... now that makes sense. Looking at an old test server I see that the SP3 changes are not there. Doesn't pay to keep things current I guess
.I agree the Update_One option is a much better choice. The SP3 BOL states that the Auto_Fix option may not get the login to user link right, and the pre-SP3 BOL states that the Auto_Fix option may grant more permissions than were previously allowed. Not good...
At least I learn something every time I visit this site. Keep up the excellent work! A little 'discussion' is healthy for all of us!
--Tom
-
Has anybody tried this 'sp_change_users_login' on a 6.5 database? I couldn't get it to fix the login, but ended up deleting and recreating the login, which removed all the database permission assignments - not good.
WM
-
I don't think the question has a single correct answer. I think this one is a case of "an" answer rather than "the" answer.
Having had to do this for real I used sp_addlogin and specified the SID from the old login when creating the new one on the spare server.
As I always grant access via roles I have also used the drop and recreate the user method then assign that user to the appropriate roles.
.Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply