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://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
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
@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;
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