March 17, 2016 at 12:01 am
Comments posted to this topic are about the item Enumerate Windows Group Members
Lowell
March 17, 2016 at 6:25 am
Thanks for the article.
March 17, 2016 at 6:45 am
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?
March 17, 2016 at 6:53 am
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://qa.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
March 17, 2016 at 7:25 am
Thanks for the useful script.
March 17, 2016 at 7:25 am
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;
March 17, 2016 at 8:12 am
March 17, 2016 at 1:01 pm
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
July 21, 2016 at 11:55 am
Thank you! Very useful!!! : )
February 24, 2017 at 12:22 pm
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