We help IT Professionals succeed at work.

sp_change_users_login

anushahanna
anushahanna used Ask the Experts™
on
when i run
EXEC sp_change_users_login 'Report'
i get one row output..

but when i run the next line of code, it does fix it..
EXEC sp_change_users_login 'Auto_Fix', 'user'

any thoughts on how to go about seeing what the problem is.

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
that user is an orphan user ( the internal user id's could be different across the servers , and the second sp make them matchable )

Author

Commented:
if the second sp did not make it ok, what could be done next?
Awarded 2008
Awarded 2008
Commented:
you're spids are mismatched, most likely.  Either that, or you have users that are not matched to logins.  

Author

Commented:
so what would you do in this case? drop the users/login and create again?

i have a backup before this was restored? can i restore it on the same server to get the right user/login info?
You can specify the user names that you want to connect.

EXEC sp_change_users_login 'Update_One', 'database_username', 'server_username'

Author

Commented:
what is 'Update_One'?
It is one of the 3 values for that parameter.  From the the SQL BOL

Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values.

Value Description
Auto_Fix Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.
user must be a valid user in the current database, and login must be NULL, a zero-length string (''), or not specified.
 
Report Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.
user and login must be NULL, a zero-length string (''), or not specified.
 
Update_One Links the specified user in the current database to login. login must already exist. user and login must be specified.

Author

Commented:

that worked.. thanks.