problem w/ sp_change_users_login on 64bit SQL Server 2005?

Here's the situation: I just restored a full backup of a SQL Server 2K
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen> How can it work on one and not the other?
Because these are different instances and might not have the same logins.
> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo