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

Can you show the exact syntax you used for the sp_change_users_login?

Tried update_one option in sp_change_users_login?

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

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.

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

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.