Enumerate Windows Group Members

  • Lowell

    SSC Guru

    Points: 323411

    Comments posted to this topic are about the item Enumerate Windows Group Members

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the article.

  • Pieter-423357

    SSCommitted

    Points: 1619

    Need trick and scanning the AD-group is certainly useful, but wouldn't you want to know the real permissions in SQL Server itself to the Database level? Or at least compare AD-group results findings to what the SQL Server is permitting?

  • Lowell

    SSC Guru

    Points: 323411

    Pieter-423357 (3/17/2016)


    Need trick and scanning the AD-group is certainly useful, but wouldn't you want to know the real permissions in SQL Server itself to the Database level? Or at least compare AD-group results findings to what the SQL Server is permitting?

    This can be a piece of the puzzle to identify a permissions enumeration, you bet., and getting db level permisisons as well could be done easily.

    this piece provides a nice list, that can then be joined to one database, or something that gathered permissions from all databases.

    Perry Whittle has posted a nice script here, for example, that I've adapted into a procedure to enumerate permissions. I've not needed to join it to that AD info yet , though.

    http://www.sqlservercentral.com/Forums/Topic1560182-1550-1.aspx

    you would have to get all the results you want into a single table, so that you could join it to the results of this.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • rchantler

    SSCrazy

    Points: 2092

    Thanks for the useful script.

  • mryan1001

    SSC Journeyman

    Points: 99

    I'v been working on the same problem. However I took it a step further by recursively climbing the AD Group structure.

    While I did not use a cursor, I did use Dynamic SQL.

    USE [master];

    -----------------------------------------------------

    SET NOCOUNT ON;

    SET IMPLICIT_TRANSACTIONS OFF;

    -----------------------------------------------------------------------------------

    DECLARE

    ---------------------------------------------------------------------------

    -- CONSTANTS

    -- ASCII Map

    @CR NCHAR(1) = 0x0D, -- <CR> Carriage Return

    @LF NCHAR(1) = 0x0A, -- <LF> Line feed

    @DOT NCHAR(1) = 0x2E, -- . Dot

    @tab NCHAR(1) = 0x09, -- Tab

    @SQ NCHAR(1) = 0x27, -- ' Single Quote

    @DQ NCHAR(1) = 0x22, -- " Double Quote

    @sp NCHAR(1) = 0x20, -- Space

    @SC NCHAR(1) = 0x3B, -- ; Semi Colon

    @CO NCHAR(1) = 0x3A, -- : Colon

    @CM NCHAR(1) = 0x3A, -- , Comma

    ---------------------------------------------------------------------------

    -- Vars

    @Debug BIT = 0,

    @WhileMax TINYINT = 20,

    @Database SYSNAME = NULL,

    @Command NVARCHAR(2048) = NULL,

    @SQL NVARCHAR(MAX) = NULL;

    -----------------------------------------------------------------------------------

    DECLARE @Queue TABLE (

    [Account] SYSNAME NOT NULL,

    [Option] VARCHAR(10) NULL

    );

    --------------------------------------------------------------------------------------------------------------------------------------------------------

    IF OBJECT_ID('[tempdb]..[#Logins]') IS NOT NULL BEGIN

    DROP TABLE [#Logins];

    END;

    CREATE TABLE [#Logins] (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [Parent] SYSNAME NULL,

    [Account] SYSNAME NOT NULL,

    [Type] VARCHAR(10) NULL,

    [Privilege] VARCHAR(10) NULL,

    [Login] SYSNAME NULL,

    CONSTRAINT [PK|Logins] PRIMARY KEY CLUSTERED (

    [ID] ASC

    ),

    CONSTRAINT [UK|Account] UNIQUE NONCLUSTERED (

    [Account] ASC,

    [Parent] ASC

    )

    );

    -------------------------------------------------------------

    IF OBJECT_ID('[tempdb]..[#ErrorDetails]') IS NOT NULL BEGIN

    DROP TABLE [#ErrorDetails];

    END;

    CREATE TABLE [#ErrorDetails] (

    [ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [Account] SYSNAME NOT NULL,

    [Option] VARCHAR(10) NULL,

    [Number] INT NOT NULL,

    [Severity] INT NOT NULL,

    [State] INT NOT NULL,

    [Error] NVARCHAR(256) NOT NULL,

    );

    --------------------------------------------------------------------------------------------------------------------------------------------------------

    -- Fill the Queue

    INSERT

    INTO @Queue

    SELECT

    [name] AS [Account],

    'MEMBERS' AS [Option]

    FROM [sys].[server_principals] AS [Logins]

    WHERE [type] = 'G'

    ---------

    UNION ALL

    ---------

    SELECT

    [name] AS [Account],

    'ALL' AS [Option]

    FROM [sys].[server_principals] AS [Logins]

    WHERE [type] = 'U'

    ;

    --------------------------------------------------------------------------------------------------------------------------------------------------------

    -- Loop Here

    WHILE (@WhileMax < 0) OR EXISTS(SELECT 1 FROM @Queue) BEGIN

    IF @Debug = 1 BEGIN

    PRINT '--' + REPLICATE('=',132);

    RAISERROR('Max iteration %i ', 0, 0, @WhileMax) WITH NOWAIT;

    END;

    -------------------------------------------------------------------

    SELECT

    @SQL = '',

    @WhileMax -= 1,

    @Command = '

    DECLARE

    @Message VARCHAR(128) = NULL;

    DECLARE

    @Logins TABLE (

    [Account] SYSNAME NULL,

    [Type] VARCHAR(10) NULL,

    [Privilege] VARCHAR(10) NULL,

    [Login] SYSNAME NULL,

    [Parent] SYSNAME NULL

    );' ,

    @SQL += REPLACE(@Command, @DQ, @SQ)

    ;

    -------------------------------------------------------------------

    SELECT

    @Command = '

    SELECT @Message = "' + QUOTENAME([Account]) + ' ' + QUOTENAME([Option]) + ' ... ";

    DELETE FROM @Logins;

    BEGIN TRY

    INSERT INTO @Logins

    EXEC xp_logininfo

    @acctname = '+ QUOTENAME([Account]) +',

    @option = '+ QUOTENAME([Option], @SQ) +';

    END TRY BEGIN CATCH

    SELECT @Message += " Skipped! ";

    INSERT

    INTO [#ErrorDetails]

    SELECT

    ' + QUOTENAME([Account], @SQ) + ' AS [AD Account],

    ' + QUOTENAME([Option], @SQ) + ' AS [Option],

    ERROR_NUMBER() AS [Number],

    ERROR_SEVERITY() AS [Severity],

    ERROR_STATE() AS [State],

    ERROR_MESSAGE() AS [Error];

    END CATCH;

    --

    INSERT INTO #Logins

    SELECT

    [Source].[Parent],

    [Source].[Account],

    [Source].[Type],

    [Source].[Privilege],

    [Source].[Login]

    FROM @Logins AS [Source]

    LEFT

    JOIN #Logins AS [Target]

    ON [Source].[Parent] = [Target].[Parent]

    AND [Source].[Account] = [Target].[Account]

    WHERE [Target].[ID] IS NULL

    ;

    RAISERROR( "%s", 0, 0, @Message) WITH NOWAIT;

    --------------------------------------------------------------------------',

    @SQL += REPLACE(@Command, @DQ, @SQ)

    FROM @Queue -- The Queue

    OPTION (MAXDOP 1);

    --------------------------------------------------------------------------------------------------------------------------------------------------------

    -- Execute Dynamic SQL

    IF @Debug = 1 BEGIN

    PRINT '/*'

    + @LF + '-- Dynamic SQL Lenght: ' + CONVERT(VARCHAR,LEN(@SQL))

    + CASE WHEN LEN(@SQL) > 4000

    THEN ' - The display has been truncated.'

    ELSE ''

    END

    + @LF + REPLICATE('-',128);

    PRINT @SQL;

    PRINT REPLICATE('-',128)

    + @LF + ' */';

    END;

    RAISERROR('-- Dynamic SQL Response BELOW -- vvvvvvvv',0,0) WITH NOWAIT; -- Flush the buffer

    EXEC sp_executesql

    @stmt = @SQL;

    RAISERROR('-- Dynamic SQL Response ABOVE -- ^^^^^^^^',0,0) WITH NOWAIT; -- Flush the buffer

    PRINT '-- ' + REPLICATE('=',128);

    --------------------------------------------------------------------------------------------------------------------------------------------------------

    DELETE FROM @Queue; -- Stops the loop

    --------------------------------------------

    -- Feed the Queue

    DELETE [#Logins]

    OUTPUT DELETED.[Account], 'MEMBERS'

    INTO @Queue

    WHERE [#Logins].[Type] = 'group'

    --------------------------------------------

    END; -- of while loop

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------

    SELECT

    *

    FROM [#ErrorDetails]

    ORDER

    BY [ID];

    --

    SELECT

    *

    FROM [#Logins]

    ORDER

    BY [Account];

    --------------------------------------------------------------------------------------------------------------------------------------------------------

    IF @Debug = 1 BEGIN

    DROP TABLE [#Logins];

    DROP TABLE [#ErrorDetails];

    END;

    --------------------------------------------------------------------------------------------------------------------------------------------------------

    RETURN;

  • SQLBlimp

    SSCertifiable

    Points: 6322

    Hi - There is another article that describes virtually the same thing and includes database permissions as well.

    http://www.sqlservercentral.com/articles/Active+Directory/135710/[/url]

    Thanks

    John

  • Everett Music-289188

    SSC Enthusiast

    Points: 102

    Thanks very much for the script. My only problem is that I live in a case sensitive world so I had to modify the create #TMP table to use the same case as the other references. I must belong to a minority as I find this issue with almost every code posting I find.

    Thanks again!

    M

  • GeoffRuble-976616

    Old Hand

    Points: 378

    Thank you! Very useful!!! : )

  • SQLRNNR

    SSC Guru

    Points: 281210

    Useful scripts from the original by Lowell and the thread participation. Great to see this script get bumped to the front page today.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply