I was looking recently into writing a wrapper for the LookupAccountSid WinAPI function, so that I could use this functionality in T-SQL. I decided to write a CLR function and document the steps here:

1. First, we need to write the code into a file - I named it LookupNameSid.cs. I tested this code with both the 1.1 CLR and the 2.0 CLR. To keep this post shorter, I will not include comments in the code, but instead I'll provide a quick overview of what gets done here: the LookupSid function is passed the SID in a byte array, it copies these bits into memory pointed to by an IntPtr, and then we call the WinAPI function LookupAccountSid - first to determine the length of the buffers that will receive the output and a second time to get the result; the rest is just error handling and memory management.

/////////////////
//
// LookupNameSid
//
// Wrapper around WinAPI function LookupAccountSid
//
// compile command:
// csc /target:library /out:LookupNameSid.dll LookupNameSid.cs
//
/////////////////

using System;
using System.Runtime.InteropServices;
using System.Text;

public class LookupNameSid
{
    const int NO_ERROR = 0;
    const int ERROR_INSUFFICIENT_BUFFER = 122;

    enum SID_NAME_USE
    {
        SidTypeUser = 1,
        SidTypeGroup,
        SidTypeDomain,
        SidTypeAlias,
        SidTypeWellKnownGroup,
        SidTypeDeletedAccount,
        SidTypeInvalid,
        SidTypeUnknown,
        SidTypeComputer
    }

    [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
    private static extern bool LookupAccountSid(
        string lpSystemName,
        IntPtr Sid,
        StringBuilder lpName,
        ref uint cchName,
        StringBuilder ReferencedDomainName,
        ref uint cchReferencedDomainName,
        out SID_NAME_USE peUse);

    public static string LookupSid(byte[] rgSid)
    {
        if (rgSid == null || rgSid.Length == 0)
            return null;

        IntPtr pSid = IntPtr.Zero;
        uint cbSid = (uint)rgSid.Length;

        pSid = Marshal.AllocCoTaskMem(Convert.ToInt32(cbSid));

        Marshal.Copy(rgSid, 0, pSid, (int)cbSid);

        StringBuilder name = new StringBuilder();
        uint cchName = (uint)name.Capacity;
        StringBuilder referencedDomainName = new StringBuilder();
        uint cchReferencedDomainName = (uint)referencedDomainName.Capacity;
        SID_NAME_USE sidUse;

        int err = NO_ERROR;

        if (!LookupAccountSid(null, pSid,
            name, ref cchName,
            referencedDomainName, ref cchReferencedDomainName, out sidUse))
        {
            err = Marshal.GetLastWin32Error();
            if (err == ERROR_INSUFFICIENT_BUFFER)
            {
                err = NO_ERROR;
                name.EnsureCapacity((int)cchName);
                referencedDomainName.EnsureCapacity((int)cchReferencedDomainName);

                if (!LookupAccountSid(null, pSid,
                    name, ref cchName, referencedDomainName,
                    ref cchReferencedDomainName, out sidUse))
                {
                    err = Marshal.GetLastWin32Error();
                }
            }
        }

        Marshal.FreeCoTaskMem(pSid);

        if (err == NO_ERROR)
        {
            return referencedDomainName.ToString() + "\\" + name.ToString();
        }
        else
        {
            return null;
        }
    }
}

2. The next step is to compile this into a DLL. I used the command line compiler for this: csc.exe. This can be found in the Windows directory, under Microsoft.NET\Framework\version\, where version is the CLR version. On my machine, the command looked like this:

C:\WINNT\Microsoft.NET\Framework\v2.0.50727\csc.exe /target:library /out:LookupNameSid.dll LookupNameSid.cs

The path to csc.exe may need to be updated according to the machine settings, for this to work. The result of this step should be that now we have a DLL file containing the LookupSid function.

3. Now come the steps required for installing this assembly. Note that this is an unsafe assembly because it calls unmanaged code - the WinAPI. Also, I decided to install this code in my master database, but a better idea is to put it in another database. Finally, because this is an unsafe assembly and because I didn't want to sign it, I decided instead to mark the database in which I placed it as trustworthy. Here are the T-SQL commands that I used to install the assembly (the path to the DLL in CREATE ASSEMBLY will most likely need to be updated):

--
-- Steps for importing the CLR code into SQL Server
--
ALTER DATABASE master SET TRUSTWORTHY ON;
GO

CREATE ASSEMBLY LookupNameSid FROM 'C:\TEMP\LookupNameSid.dll' WITH PERMISSION_SET = UNSAFE;
GO

CREATE FUNCTION LookupSid (@sid VARBINARY(85)) RETURNS SYSNAME AS EXTERNAL NAME [LookupNameSid].[LookupNameSid].[LookupSid];
GO

sp_configure 'clr enabled', 1
GO
reconfigure
GO

SELECT dbo.LookupSid(sid), name FROM syslogins;
GO

So, I turned on the TRUSTWORTHY attribute of the master database, then I created the assembly and the LookupSid function, and then I turned CLR on. Last command is just to test that the function works ok; it basically looks up the SID for each entry in the syslogins catalog, and it prints the name found, as well as the name recorded in the catalog. For SQL logins, we'll just print NULL. If the code from step 1 is modified to use a namespace, then the second part of the external name used in CREATE FUNCTION will also need to be modified to include the namespace name.

That's it! The LookupSid function can now be used to map SIDs to account names.

4. The following T-SQL commands can be used to revert the changes made to SQL Server in the above steps. I assume a default SQL Server state where CLR was not enabled and the master database was not marked as trustworthy, so if this was not the case, the corresponding cleanup steps should be skipped.

--
-- Cleanup steps
--

DROP FUNCTION LookupSid;
GO
DROP ASSEMBLY LookupNameSid;
GO
sp_configure 'clr enabled', 0
GO
reconfigure
GO
ALTER DATABASE master SET TRUSTWORTHY OFF
GO