SqlServer AD Login Auditing with nested group parsing

Simple script to help determine who has access to your SQL Server and through what groups with some generic SA rights Checking. This requires the DSquery Tools(Windows Feature ) to be installed to allow querying against the AD server as well as the permissions to run both XP_LoginInfo and xp_cmdshell.

    
  declare @sql Varchar(max)  
    ,@ADName Varchar(max)  
    ,@LoginType Varchar(Max)  
    ,@cmd varchar(2000)  
    ,@DomainPrefix Varchar(max)  
 
 ---------------------SETTINGS------------------------------------  
  -- Domain Prefix: for the Account Mydomain\Mylogin the domain Prefix would be the Mydomain\ piece.Include the \  
  set @DomainPrefix = 'FHLBDM\'  
 -----------------------------------------------------------------  
 
  declare     @BadLogins table (  
    Name varchar(max)  
    )  
  Declare @GroupPerms table (            
       [AccountName] [varchar](max) NULL,  
      [Type] [varchar](max) NULL,  
      [Privelege] [varchar](max) NULL,  
      [Mapped_Login_name] [varchar](max) NULL,  
      [Permission_Path] [varchar](max) NULL  
    )  
  declare @GroupUsers table(  
      [AccountName] [varchar](max) NULL,  
      [Type] [varchar](max) NULL,  
      [Privelege] [varchar](max) NULL,  
      [Mapped_Login_name] [varchar](max) NULL,  
      [Permission_Path] [varchar](max) NULL  
    )  
 
  Declare @nestedTemp table(  
      Value varchar(max)  
      )                 
     
  declare LoginCursor Cursor for  
  SELECT name,type  
   FROM sys.server_principals   
   where type in ('u','g')  
 
    
  open LoginCursor  
  Fetch next from LoginCursor into @ADName,@LoginType  
 
  while @@FETCH_STATUS = 0  
  begin  
   If @LoginType = 'u'  
   Begin  
    set @sql = 'EXEC xp_logininfo '''+ @ADName + ''''  
    Print (@Sql)  
    Begin Try  
      Insert @GroupUsers  
       exec (@sql)  
    End Try  
    Begin Catch  
      insert @BadLogins   
       select @ADName  
    end Catch       
   end  
   --If Principal is a group than determine if the group is part of admin group  
   Else if @LoginType = 'g'  
   Begin  
    set @sql = 'EXEC xp_logininfo '''+ @ADName + ''',''all'''  
    Print (@Sql)  
    begin try  
      delete @GroupPerms  
      Insert @GroupPerms  
      exec (@sql)  
      
    end try  
    begin catch  
      insert @BadLogins   
       select @ADName  
    end catch  
    --Nested Group Parsing of all users(Requires DSQuery Installed)  
    set @Cmd = 'dsquery group -samid "'+replace(@ADName,@DomainPrefix,'')+'" |dsget group -members -expand -l | dsget user -samid -c -l'  
    Print (@Cmd)  
     
    Delete @nestedTemp  
    insert @nestedTemp  
    exec xp_cmdshell @command_String = @cmd       
     
    If exists( select * from @nestedTemp where Value like '%is not recognized as an internal or external command%')  
    begin  
      Raiserror('DSQUERY not enabled on system. Cannot parse nested groups. Install remote admin tools via windows features',11,11)  
    end  
    else  
    begin   
      Insert @GroupUsers  
       Select Replace(nt.Value,'samid: ',@DomainPrefix) Account  
         ,gp.Type  
         ,GP.Privelege  
         ,GP.Mapped_Login_name  
         ,@ADName Permission_Path  
       from @nestedTemp nt  
       left join @GroupPerms gp  
       on 1=1  
       where Value like 'samid:%'  
    end  
   End       
   Fetch next from LoginCursor into @ADName,@LoginType  
  end  
  deallocate LoginCursor  
   
  --Return List of Accounts that have Access and how they have that access  
  --AccountName is the Windows Name(Could be local or Domain)  
  --Type dictates if account accesses the server via a group or direct access  
  --Privelege simply states Either admin or user. Admin indicates they have ServerAdmin(SA) rights  
  --MappedLoginName and Permission_Path are tpyically the same in the case of group membership.   
  --For direct access via a user account the permission path will be null.   
  Select *  
    from @GroupUsers  
  order by AccountName  
   
  --Accounts/Groups can be removed from AD/LocalCOmputer without SQL server being aware.   
  --For this reason we store those Bad records in this table and report them  
  Select Name as 'Invalid Groups or logins(Deleted or renamed?)' from @BadLogins