A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Tuesday, December 29, 2009

An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'

Ran into this one today. Restored a database with a local user
account and the SIDs didn't align. Simple matter to fix, run
sp_change_users_login with auto_fix and I'm done.
*clickety*clickety*click* Boom

EXECUTE sp_change_users_login @action = 'auto_fix', @UserNamePattern =

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure

I ran this a few days ago against the same restore and it worked.
Same command even since I'm lazy and have a cheatsheet of infrequently
used but needed TSQL. A quick lap around google found similar people
having problems but no resolution. Invalid parameter... I tried
changing the case of auto_fix to all lower, all upper but no such
luck. I tried it by explicitly specifying the missing parameters to
no avail.

Being a simple man and not well versed in SQL Server users,
principals, etc I decided to try the simple things first. When I ran
the original command, I was in the sales database. I changed into the
master database and reran. Low and behold, it worked. Where it gets
weird is the command doesn't throw the invalid parameter or option
error if I run it against the parts database. I'm sure once I get
versed in securables in SQL Server, this will all make sense but for
now, I'm documenting it so I remember.



ant_t said...

Awesome - first result in google, you saved me some time!

Berserker said...

Yup this works