SQL Server :审核AD用户-如何从AD组中标识AD用户帐户

Question

我想识别我们服务器上哪些帐户属于AD用户。

目前，我发现来自用户的大量帐户实际上应该是在同一Server上注册的AD组的一部分。实际上，这些AD用户帐户是多余的(或者应该是)。

问题是:如何在Server中识别这些AD用户和AD组？

谢谢,

Answer 1

下面是一个使用xp_login_info (https://msdn.microsoft.com/en-us/library/ms190369.aspx)为每个AD用户和组获取带有Server登录名的AD权限路径的脚本。

SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL
     DROP TABLE #results;
CREATE TABLE #results(
      account_name sysname
    , type char(8)
    , privilege char(9)
    , mapped_user_name sysname
    , permission_path sysname NULL
    );

DECLARE @SQL nvarchar(MAX) = (
    SELECT 'INSERT INTO #results EXECUTE xp_logininfo ''' + name + ''', ''all'';'
    FROM sys.server_principals
    WHERE type_desc IN(N'WINDOWS_LOGIN', N'WINDOWS_GROUP')
    FOR XML PATH(''));

EXECUTE(@SQL);

SELECT
      account_name
    , type
    , privilege
    , mapped_user_name
    , permission_path
FROM #results
ORDER BY account_name, mapped_user_name;

Answer 2

谢谢丹的建议。

DECLARE
    @T_LOGINS TABLE ( LOGIN_ NVARCHAR(300), SK INT IDENTITY(1,1) )
;

DECLARE
    @t_login_info TABLE (
        account_name    NVARCHAR(300)
    ,   account_type    NVARCHAR(300)
    ,   privilege   NVARCHAR(300)
    ,   mapped_login_name NVARCHAR(300)
    ,   permission_path NVARCHAR(300)
    ,   failed_ BIT DEFAULT 0
    )

;
    INSERT INTO @T_LOGINS
            ( LOGIN_ )
    SELECT p.name
    FROM sys.server_principals AS p
    WHERE type_desc IN(N'WINDOWS_LOGIN', N'WINDOWS_GROUP')


DECLARE
    @SK INT = 1
;

DECLARE
    @LOGIN_ NVARCHAR(300) = (
                    SELECT LOGIN_
                    FROM @T_LOGINS
                    WHERE
                        SK = @SK
                    )

;
WHILE ( @LOGIN_ IS NOT NULL )
BEGIN


BEGIN TRY

        INSERT INTO @t_login_info
                ( account_name ,
                  account_type ,
                  privilege ,
                  mapped_login_name ,
                  permission_path
                )

        EXEC sys.xp_logininfo @LOGIN_
        ;
END TRY
BEGIN CATCH

            INSERT INTO @t_login_info
                ( account_name ,
                  failed_
                )
            VALUES(
                @LOGIN_,
                CAST( 1 AS BIT)
                )
            ;

END CATCH



        SET @sk = @sk + 1
        ;

        SET
        @LOGIN_ = (
                    SELECT LOGIN_
                    FROM @T_LOGINS
                    WHERE
                        SK = @SK
        )

END




SELECT * FROM @t_login_info

Answer 3

我通常使用丹剧本的以下变体。它使用xp_login_info：

DECLARE @CurrentRow INT
DECLARE @TotalRows INT
SET @CurrentRow = 1

DECLARE  @SqlGroupMembership  TABLE(
    ACCOUNT_NAME      SYSNAME,
    ACCOUNT_TYPE      VARCHAR(30),
    ACCOUNT_PRIVILEGE VARCHAR(30),
    MAPPED_LOGIN_NAME SYSNAME,
    PERMISSION_PATH   SYSNAME
    )

DECLARE @WindowsGroupsOnServer TABLE(
      UniqueRowID int IDENTITY (1, 1) Primary key NOT NULL 
    , Name      SYSNAME
    )

INSERT INTO @WindowsGroupsOnServer (NAME)
SELECT [NAME] FROM master.sys.server_principals WHERE TYPE = 'G' 

SELECT @TotalRows = MAX(UniqueRowID) FROM @WindowsGroupsOnServer

DECLARE @WindowsGroupName sysname 


-- Loop Each Windows Group present on the server
WHILE @CurrentRow <= @TotalRows 
   BEGIN 

  SELECT @WindowsGroupName  = [Name] 
  FROM @WindowsGroupsOnServer
  WHERE UniqueRowID = @CurrentRow 

       BEGIN TRY
       -- Insert found logins into table variable
       INSERT INTO @SqlGroupMembership (ACCOUNT_NAME,ACCOUNT_TYPE,ACCOUNT_PRIVILEGE,MAPPED_LOGIN_NAME,PERMISSION_PATH)
       EXEC xp_logininfo @WindowsGroupName , 'members' 
       END TRY

       BEGIN CATCH
       -- No action for if xp_logininfo fails
       END CATCH

    SELECT @CurrentRow = @CurrentRow + 1   

   END 

-- Display final results
SELECT  @@servername AS Servername
                , [PERMISSION_PATH] AS WindowsGroup
                , Account_Name
                , Mapped_Login_Name
                , Account_Type
                , Account_Privilege
FROM @SqlGroupMembership ORDER BY [PERMISSION_PATH], [ACCOUNT_NAME]

我发现来自用户的大量帐户实际上应该是在同一个sqlserver上注册的AD组的一部分。

这意味着，您想要查找给定的Windows帐户，它是否被授予直接权限，而不是AD组的一部分。

您可以使用这个剧本来查找它。