Windows 2003 and SP_change_users_login
-
I'm having difficulty runing SP_Change_users_login on SQL Server 2000 SP3a on a Windows 2003 SP1(standard) server.
I'm trying to restore a database and fix the orphaned users but having the following problems.....
when I execute SP_change_users_login 'auto_fix','User_name' I get the following error: -
"The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0."
or if I create a login and then try sp_change_users_login 'update_one','user_name','login_name' the following error occurs
"Terminating this procedure. The User name 'mandys' is absent or invalid."
I have no problem fixing orphaned users on Windows 2000 server but sturggling on w2k3
Does anyone have any ideas?
Thanks in advance
Matt
-
Don't use autofix. I always specify both the user and login and I've never had problems. The Windows version should have nothing to do with this.
If you create a login and then run sp_change_users_login that won't work. The user name must exist in the database. The sp can create a new login of with the same name but it can't create a new user.
HTH
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Have you checked the accounts used for executing the services on both servers (mainly the 2K3 server) to make sure that they have the proper permissions ? Just a thought since AD is more titghly integrated into 2K3 (and if you use integrated security).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Since sp_change_users_login does not work on Windows Logins the service account and it's privs should not make any difference.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I have the same issue on a SQL Server 2k5 with Win2K3. My feeling is that is some new thing in SQL 2k5.
sp_change_users_login
'Update_one' will end with
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'XXXXXX' is absent or invalid.
And for sure the user exists at the database level and server level and it is an SQL account.
-
Matt,
If you execute this code:
SELECT isntname, [name]
FROM sysusers
WHERE isntname = 0
ORDER BY isntname, [name]
is one of the users you are trying to fix displayed?
(Comment out the WHERE clause to show all users in the sysusers table).
The error you are receiving only shows up in one place in the sp_change_users_login stored procedure, and that is when the SQL username you specify cannot be located in sysxlogins.
Note that if the sysusers.[name] column contains a row that matches the username you are specifying, you can still get this error if the column isntname = 1, which indicates it was mapped to a Windows login on the source server.
-
I end up deleting the user from the database and reassign the rights for that user at the server level.
Anyway it is kind of interesting
-
Hi,
I have same problem with 2008 R2. Have you found a other soltion than remove and add again the user
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply