SQL Server : auditing AD Users - how can we identity AD User account from AD Groups

Question

I would like to identity which accounts on our servers belong to AD Users.

Currently, I find a plethora of accounts from users that should in fact be part of AD Groups registered on the same SQL Server. In effect these AD User accounts are redundant (or they should be).

Question is: how can we identify these AD users and AD groups in SQL Server?

Thanks,

Answer 1

Below is a script that uses xp_login_info (https://msdn.microsoft.com/en-us/library/ms190369.aspx) to get the AD permission paths for each AD user and group with a SQL Server login.

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

Thanks Dan for the suggestion.

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

I normally use the below variation of Dan's script. It uses 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]

I find a plethora of accounts from users that should in fact be part of AD Groups registered on the same sqlserver.

This means, you want to find out for a given Windows Account, is it given a direct permission rather than being part of AD group.

You can use this script to find that out.

Answer 4

Depending on your permissions I've had problems with xp_logininfo so here is an alternate solution.

First you can use sys.sp_validatelogins to generate a list of AD accounts that exist on your instance but no longer exist in AD.

Second you can use sys.login_token to get a list of all windows groups that you currently belong to. Combine that with EXECUTE as and you can do this for anyone. Unfortunately it doesn't work the other way around. You can't get member information for a given group. But for a single AD user you can do the following:

EXECUTE AS LOGIN = 'domain\username'
SELECT principal_id, sid, name FROM sys.login_token WHERE type = 'WINDOWS GROUP'
REVERT

Where the principal_id > 0 it is the principal id from sys.server_principals. This method is more limited but it only requires permissions on your SQL Instance (fairly high level ones admittedly) but none in AD.