Sometimes you need to know how User ID works “under the hood.”

We see ‘systemuserid’ all over the database identifying a user … but how does it decide which systemuserid to use?

CRM appears to figure out which user is logging in by querying the MSCRM_CONFIG database systemuserauthentication table looking for a value in the authinfo column that matches the objectsid for the user from activedirectory (SID, *not* GUID).

You can get the user SID (and GUID) from active directory by registering a dll from the windows server 2003 resource toolkit ( http://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe ) on a 32-bit OS (I don't know how to make it work on 64 bit; docs say 64 bit is not supported) - install toolkit, copy acctinfo.dll to c:\windows\system32, "regsvr32 acctinfo.dll" , then browse to user in AD ( start/run/dsa.msc ) and look at the 'additional account info' tab (doesn't show up when user is found using find, unfortunately).

Then you can dig up the entry for the sid in systemuserauthentication, which gives you a userid, which you can find in systemuserorganization, which gives you a crmuserid, which you can go find as systemuser in the [orgname]_mscrm database (which is NOT the same as systemuser in the mscrm_config database).

If you have a problem with a mismapping between the two databases (for example, somebody mistakenly doing a backup of one but not the other), there are two easy ways to fix the problem:

The first thing to try is to edit the user the in the normal CRM interface and change the AD login name to a not-yet-used-by-CRM value, save the entity, then change it back to the value you want to be used.  This often cleans up a broken mapping.

The next thing to try is to use the crm deployment manager to export and import the organization; during the import there is a user mapping step where you can have CRM set up these relationships properly for you.

Finally, filtered views have their own method of identifying the user – they use [fn_FindUserGuid] which checks for a GUID on context_info(), and if not found, they query SystemUserBase where DomainName = SUSER_SNAME() .

I recommend against trying to operate on this data at the raw sql level, system user id is simply referenced in too many places to reliably get everything.  You would end up with orphaned records at best, for example due to all the ‘createdby’ and ‘modifiedby’ references on every entity.  So ‘select’ is fine to learn things, but ‘delete’ or ‘update’ at your own risk and preferably not even then.  Still, if you have to, the AD SID is where to start.