Enumerate Windows Group Members

  • 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!

  • Thanks for the article.

  • 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?

  • 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!

  • Thanks for the useful script.

  • 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

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

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

    @DOTNCHAR(1)= 0x2E, -- . Dot

    @tabNCHAR(1)= 0x09, --Tab

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

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

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

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

    @CONCHAR(1)= 0x3A, -- :Colon

    @CMNCHAR(1)= 0x3A, -- ,Comma

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

    -- Vars

    @DebugBIT= 0,

    @WhileMaxTINYINT= 20,

    @DatabaseSYSNAME= NULL,

    @CommandNVARCHAR(2048)= NULL,

    @SQLNVARCHAR(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]INTIDENTITY(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]INTNOT NULL,

    [Severity]INTNOT NULL,

    [State]INTNOT 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

    @MessageVARCHAR(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;

    --

    INSERTINTO #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;

    PRINTREPLICATE('-',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]

    OUTPUTDELETED.[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;

  • 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

  • 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

  • Thank you! Very useful!!! : )

  • 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 9 (of 9 total)

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