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

Question

This is a follow-up of a question I asked earlier this morning (posted here.) Following the instructions provided, I've managed to query my SQL Server 2000 database for a SID associated with an AD Group. The SID, however, looks like this:

0x0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF01234567

What can I do to obtain the name of the AD Group referenced by the SID? I've tried googling PowerShell scripts, however, most of their examples of SIDs look like this:

S-1-5-21-1454471165-1004335555-1606985555-5555

Obviously, that doesn't look like the value I'm getting back from the SQL Server. How can I do this?

Answer 1

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 = 1
AND name = 'CONTOSOmylogin'
"@

invoke-sqlcmd -ServerInstance "myserver" -Database master -Query $query | 
foreach {$SID = new-object security.principal.securityidentifier($_.SID,0); $SID.translate([system.security.principal.NTAccount]) }