What is the Key stored in the UserItem table?

Topics: Developer Forum
May 14, 2008 at 5:14 PM
Hey folks,

I'm wondering what the value is that's being stored in the Key column of the UserItem table in the SLK database? Is it the SharePoint SID for users?

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spuser.sid.aspx

Thanks,
Nick
May 14, 2008 at 5:28 PM
Also, if anyone can provide some detail on how SLK uses this key and updates the UserItem table based on what it looks up that would be very helpful.

Thanks,
Nick    
May 15, 2008 at 3:30 PM
Yes, that is correct. It is the SPUser SID in the case of windows authentication. And in the case of Forms based authentication, it is the login name.
Oct 8, 2009 at 4:54 PM
Edited Oct 8, 2009 at 5:24 PM

I am trying to tie the rows in the UserItem table back to their corresponding records in Active Directory.  The value in the name column could tie back to the “Display Name” field in Active Directory, but that’s not guaranteed to be unique.  There could be 2 people in active directory with the same name.  That leaves me with the “Key” column in the UserItem table and I’m curious where that value came from and what table I could link back to, with that field, to get more information about that user that I could link back to Active Directory for a unique link.  ie…NT UserID.

I was looking at referencing the Key column from the UserItem table back to the Shared Service Provider database UserProfile_Full table SID column thinking that this column could tie back to the Active Directory Account.

The Key column data in the UserItem table doesn’t look anything like the SID column data.

UserItem table Key column data format: S-1-5-21-1087510777-1779112194-1179000955-22614

UserProfile_Full table SID column is a GUID.

Some help would be appreciated.

Apr 1, 2010 at 7:22 AM

Has anyone figure this out.

I have been asked to do a data extract file,  and i need to match data from the SKL database and the sharepoint content database.

Any Ideas anyone? Also is it okay to do this sort of stuff without going through the Object model?

Cheers,

May 11, 2011 at 10:27 AM

SELECT
   [Id]
,   [Name]
,   SUBSTRING([Key], 41, 1000) AS [Key]
INTO  #Table1
FROM  UserItem
WHERE  [Key] LIKE '%S-1-5-21%'
ORDER BY [Id]

--

SELECT
   [Id]
,   [Name]
,   CASE WHEN
    LEN(CAST(UPPER (dbo.ConvertToBase([Key], 16)) AS VARCHAR(4))) = 3
   THEN
    CAST(UPPER (dbo.ConvertToBase([Key], 16)) AS VARCHAR(4)) + '0'
   ELSE
    CAST(UPPER (dbo.ConvertToBase([Key], 16)) AS VARCHAR(4))
   END AS [Key]
,   CASE WHEN
    LEN(CAST(UPPER (dbo.ConvertToBase([Key], 16)) AS VARCHAR(4))) = 3
   THEN
    1
   ELSE
    0
   END AS [Logic]
INTO  #Table2
FROM  #Table1

--

SELECT
   [Id]
,   [Name]
,   CASE WHEN [Logic] = 1
   THEN
    CONVERT (VARCHAR(MAX), '010500000000000515000000AFA1F911678596228D64A28C' + SUBSTRING([Key], 2, 4) + SUBSTRING([Key], 1, 1) + '0000')
   ELSE
    CONVERT (VARCHAR(MAX), '010500000000000515000000AFA1F911678596228D64A28C' + SUBSTRING([Key], 3, 4) + SUBSTRING([Key], 1, 2) + '0000')
   END AS [Key]
INTO  #Table3
FROM  #Table2

--

SELECT
   [tp_Title]
,   [tp_Login]
,   CONVERT(VARCHAR(MAX), [tp_SystemID], 2) AS [tp_SystemID]
INTO  #Table4
FROM  UserInfo

--

SELECT
   [Id]
,   [tp_login]
INTO  #Table5
FROM  #Table3 AS ab LEFT OUTER JOIN
   #Table4 AS cd
ON   ab.[Key] = cd.[tp_SystemID]
ORDER BY [Id]

--

SELECT
   ab.[Id] AS [PersonID]
,   cd.[UserName]
FROM  #Table5 AS ab JOIN
   [V_Users] AS cd
ON   ab.[tp_login] = cd.[UserName]
ORDER BY [PersonID]

--

DROP TABLE #Table1
DROP TABLE #Table2
DROP TABLE #Table3
DROP TABLE #Table4
DROP TABLE #Table5

--

CREATE FUNCTION ConvertToBase
(
    @value AS BIGINT,
    @base AS INT
) RETURNS VARCHAR(MAX) AS BEGIN

    -- some variables
    DECLARE @characters CHAR(36),
            @result VARCHAR(MAX);

    -- the encoding string and the default result
    SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
           @result = '';

    -- make sure it's something we can encode.  you can't have
    -- base 1, but if we extended the length of our @character
    -- string, we could have greater than base 36
    IF @value < 0 OR @base < 2 OR @base > 36 RETURN NULL;

    -- until the value is completely converted, get the modulus
    -- of the value and prepend it to the result string.  then
    -- devide the value by the base and truncate the remainder
    WHILE @value > 0
        SELECT @result = SUBSTRING(@characters, @value % @base + 1, 1) + @result,
               @value = @value / @base;

    -- return our results
    RETURN @result;
 -- http://dpatrickcaldwell.blogspot.com/2009/05/converting-decimal-to-hexadecimal-with.html
END

Coordinator
Jul 31, 2011 at 2:25 PM

Correct the key is the Active Directory SID. In Version 1.6, there is also a linked table UserItemSite which links SLkUser to the SPUser ID.

Richard

SLK Coordinator

http://blog.salamandersoft.co.uk