SID to AD objects conversion using SQL query.

Few years back I was asked by many customers to generate a report that shows assigned permission on resources. The glitch is, resources were mapped with SID of the domain objects (user /group) and to convert SID to AD object we had to create linked with AD database.

Finally we were able to create a temp function that can convert SID to AD object. I would not write an entire story but will share a use case that may be useful for many other DBA/ programmer.

-- Create a DB Name SidConversionDemo.
Use master
Create database SidConversionDemo
GO
-- Create table that holds resources & SID who they have permission. 
Use SidConversionDemo
Create Table Resources
    ( 
    ID INT IDENTITY(1,1), 
    SID VARCHAR(Max), 
    NAME VARCHAR (100)
    )
GO
-- Insert few rows. Please note, in production table schema might be different. Following SIDS are taken from my test SQL server and will not be the same for your environment.  
USE SidConversionDemo
INSERT INTO Resources VALUES
('S-1-5-21-3472616350-1934245764-4034525558-1104', 'Room1'),
('S-1-5-21-3472616350-1934245764-4034525558-1120', 'Room2'),
('S-1-5-21-3472616350-1934245764-4034525558-1172', 'Room3'),
('S-1-5-21-3472616350-1934245764-4034525558-1185', 'Room4'),
('S-1-5-21-3472616350-1934245764-4034525558-1186', 'Room5'),
('S-1-5-21-3472616350-1934245764-4034525558-1187', 'Room6'),
('S-1-5-21-3472616350-1934245764-4034525558-1188', 'Room7'),
('S-1-5-21-3472616350-1934245764-4034525558-1189', 'Room7'),
('S-1-5-21-3472616350-1934245764-4034525558-1191', 'Room8'),
('S-1-5-21-3472616350-1934245764-4034525558-1206', 'Room9')
GO

 -- Select current stored rows & column. 
USE SidConversionDemo
SELECT ID, SID, NAME FROM Resources
GO

–Expected output with SID only, difficult to find out AD objects (user/group..etc) associated with SID.

first

-- Now to change SID TO ADObject we need to create Function called 'dbo.sidconvert' as there is no inbuilt function.
USE SidConversionDemo
If OBJECT_ID (N'dbo.sidconvert', N'FN') IS NOT NULL
    DROP FUNCTION sidconvert;
GO
CREATE FUNCTION dbo.SidConvert (@MYSID AS VARCHAR(max))
RETURNS varbinary(256)
As
BEGIN
       If LEN (@MYSID)>12                    
BEGIN                    
DECLARE
@A AS BIGINT ,@B AS BIGINT ,@C AS BIGINT ,@D AS BIGINT
SET @MYSID = REVERSE(@MYSID)                    
SET @D = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))                    
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)                    
SET @C = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))                    
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)                    
SET @B = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))                    
SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)                    
SET @A = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))                    
RETURN 0x010500000000000515000000                    
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@A)))                    
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@B)))                    
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@C)))                    
+ CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@D)))                    
END
              BEGIN 
              RETURN null
              END
END
GO
-- Now run select query again and use dbo.sidconvert function. Following example also include well known global SID
USE SidConversionDemo 
SELECT 
    ID, 
    SID, 
    NAME, 
    CASE ISNULL(SID,'No') WHEN 'No' THEN 'No Permission' ELSE SID  END SID,
    CASE ISNULL (SID, 'No') WHEN 'NO' THEN 'No Permission'  WHEN 'S-1-3-0' THEN 'CREATORE_OWNER' When 'S-1-1-0' then 'EVERYONE'
    WHEN 'S-1-5-11' THEN 'AUTHENTICATED_USER' WHEN 'S-1-5-32-544' Then 'BUILT-IN ADMINISTRATORS' WHEN 'S-1-5-32-545' Then 'USERS'
    WHEN 'S-1-5-18' THEN 'LOCAL_SYSTEM' WHEN 'S-1-5-32-547' THEN 'POWER USERS'
    Else SUSER_Sname(dbo.sidconvert(SID))  END 'AD_Object'
FROM Resources
GO

–Expected output with SID and AD objects using Sidconvert function.  

second

-- Once testing done, you can drop database called SidConversionDemo
USE master
Drop Function sidconvert
DROP DATABASE SidConversionDemo 

I have discussed similar stuff in Microsoft & VERITAS forum.
Microsoft
Veritas

Please note:-
All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about SQL Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.
If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to comment and contact me directly.

Advertisements

#active-directory, #sid, #sql