How can I obtain an Active Directory Group name from a SQL Server stored SID?

If you're using sqlps (SQL Powershell host) which works against SQL 2000 (I've tested this on my 2000 instance) you can use this:

$query = @"select sid from syslogins where isntgroup = 1AND name = 'CONTOSO\mylogin'"@invoke-sqlcmd -ServerInstance "myserver" -Database master -Query $query | foreach {$SID = new-object security.principal.securityidentifier($_.SID,0); $SID.translate([system.security.principal.NTAccount]) }

For those without sqlps:use this online C# shell do format single sid to text

http://rextester.com/AFAC13570

code backup:

//Rextester.Program.Main is the entry point for your code. Don't change it.//Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5using System;using System.Collections.Generic;using System.Linq;using System.Text.RegularExpressions;using System.Text;using System.Runtime.Remoting.Metadata.W3cXsd2001;namespace Rextester{    public class Program    {        public static string ConvertByteToStringSid(Byte[] sidBytes)        {            StringBuilder strSid = new StringBuilder();            strSid.Append("S-");            // Add SID revision.            strSid.Append(sidBytes[0].ToString());            // Next six bytes are SID authority value.            if (sidBytes[6] != 0 || sidBytes[5] != 0)            {                string strAuth = String.Format                ("0x{0:2x}{1:2x}{2:2x}{3:2x}{4:2x}{5:2x}",                (Int16)sidBytes[1],                (Int16)sidBytes[2],                (Int16)sidBytes[3],                (Int16)sidBytes[4],                (Int16)sidBytes[5],                (Int16)sidBytes[6]);                strSid.Append("-");                strSid.Append(strAuth);            }            else            {                Int64 iVal = (Int32)(sidBytes[1]) +                (Int32)(sidBytes[2] << 8) +                (Int32)(sidBytes[3] << 16) +                (Int32)(sidBytes[4] << 24);                strSid.Append("-");                strSid.Append(iVal.ToString());            }            // Get sub authority count...            int iSubCount = Convert.ToInt32(sidBytes[7]);            int idxAuth = 0;            for (int i = 0; i < iSubCount; i++)            {                idxAuth = 8 + i * 4;                if (idxAuth >= sidBytes.Length)                {                    Console.WriteLine("OK :old NT account");                    return strSid.ToString();                }                UInt32 iSubAuth = BitConverter.ToUInt32(sidBytes, idxAuth);                strSid.Append("-");                strSid.Append(iSubAuth.ToString());            }            return strSid.ToString();        }         public static void Main(string[] args)        {            //Your code goes here            Console.WriteLine(                ConvertByteToStringSid(                    SoapHexBinary.Parse(                        "0x01050000000000051500000079542007311FAE6D096510145E540300".Substring(2)                    ).Value                )            );        }    }}

credits:

https://www.sqlservercentral.com/Forums/FindPost1322822.aspx

How do you convert Byte Array to Hexadecimal String, and vice versa?