The user cannot be remapped to a login

Last Post 26 Jul 2011 11:50 AM by gunneyk. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
khatruu
New Member
New Member

--
22 Jul 2011 10:24 AM
Hi,

I have a SQL authenticated login named CRMReplication which is a user in databases CDB and CDB_AUDIT

The sid of the user in CDB_AUDIT matches with the SID of the login. The sid of the user in the CDB database does not.

Neither ALTER USER nor sp_change_users_login work. They both fail with:
Msg 33016, Level 16, State 1, Line 2 The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.


For the login:
SELECT name, sid, type_desc FROM master.sys.server_prin***ls where name = 'CRMReplication'

name                sid                                                                 type_desc
CRMReplication 0xBAFED4885B0D7D4B8691D90FD0440B75 SQL_LOGIN



For the good database:
select name, sid from CDB_AUDIT.dbo.sysusers where name = 'CRMReplication'

name                sid
CRMReplication 0xBAFED4885B0D7D4B8691D90FD0440B75



For the errant database:
select name, sid from CDB.dbo.sysusers where name = 'CRMReplication'

name                sid
CRMReplication 0x01050000000000090300000037DAC850D1E71F4FB2F0F25CBA5F2E85

Running this:
SELECT SUSER_SNAME(0x01050000000000090300000037DAC850D1E71F4FB2F0F25CBA5F2E85)
SELECT SUSER_SNAME(0xBAFED4885B0D7D4B8691D90FD0440B75)

yields this:
NULL
(1 row(s) affected)
CRMReplication
(1 row(s) affected)


This looks like a windows AD-type sid to me. The database was restored from live but there it has a SQL authenticated login. How does it changed to a non-existent login?

Are there options other than dropping the user? (Back in SQL 2000 I would have updated the system table as this is a development box.)

I'm loathe to drop the login because it is set as the execution context in a whole bunch (30-40) of triggers which will all need dropping and recreating spread across several hundred tables.

Any cunning workarounds/tips etc to minimise the amount of work would be greatly appreciated.

Thanks for your time and thoughts.

Simon
gunneyk
New Member
New Member

--
23 Jul 2011 06:09 AM
Can you show the exact syntax you used for the sp_change_users_login?
rm
New Member
New Member

--
23 Jul 2011 04:28 PM
Tried update_one option in sp_change_users_login?
khatruu
New Member
New Member

--
25 Jul 2011 01:24 AM

HI,

This is what I used:

sp_change_users_login 'update_one', 'CRMReplication', 'CRMReplication'

Output is:
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114 Terminating this procedure. The User name 'CRMReplication' is absent or invalid.

I'm going to try and find the long sid in AD or in another SQL Server/database. I've worked with SQL for too long to believe it would just make one up.

Simon

khatruu
New Member
New Member

--
25 Jul 2011 08:09 AM
Hi,

For completeness thought I'd post that this is resolved. I had to resort to dropping objects for which the user was used as the execution context then dropping and recreating the user and recreating all the objects and permissions.
 Ho Hum!

Thanks to contributors for your time and suggestions.

Over and out.

Simon
rm
New Member
New Member

--
25 Jul 2011 08:12 AM
Is CRMReplication a valid sql login on that server and valid db user in cdb? For domain account, only thing you need to do is creating sql login for that account.
khatruu
New Member
New Member

--
26 Jul 2011 09:22 AM
Hi rm.

Yes to both (as clearly shown in original post :-) )

It is a SQL authenticated login. The only thing domain-related about all this is the length of the non-matching sid.

I do appreciate your input and having taken the time to follow up etc but due to just finding out that there have been disk out of space errors on C: and I've just had to correct a corruption in msdb I think the server is holed below the waterline and probably not worth wasting your or anyone else's time on it.

Time for a rebuild on new hardware!

Thanks again.

Simon
gunneyk
New Member
New Member

--
26 Jul 2011 11:50 AM
If you have corruption all bets are off.
Sorry we couldn't find an actual cause but it just didn't make any sense why it would be that way normally.
You are not authorized to post a reply.

Acceptable Use Policy