cls
 
<#--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
*    How is a user accessing a SQL instance?
*    
*    This script will show through what login and AD groups a user is accessing
*    a SQL instance.
*
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==#>
 
<#--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
    Enter variables
--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==#>
 
<# What SQL instance to search on? #>
$instance_name = "SERVER\INSTANCE";
 
<# Set name to search for as you normally would in dsquery, like  > Lastname, first* #>
$user_search_term = "lastname, first*";
 
<# Set domain name including back slash, like > DOMAIN\ #>
$domain_name = 'DOMAINNAME\';
 
 
<#--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==#>
<#--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==#>
<# EVERYTHING BELOW CAN BE LEFT AS-IS                                                           #>
<#--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==#>
<#--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==#>
 
    if (Get-Module -ListAvailable -Name SqlServer) {
        Write-Host "Module exists"
    }
    else {
            Write-Host "Module does not exist. You need to run:" `t`t "Install-Module -Name SqlServer";
            return;
    }
     
     
    $usernames = dsquery user -name $user_search_term -o rdn;
     
    if($usernames.Count -gt 1){
        Write-Host "There are " $usernames.Count " usernames that match your search." -BackgroundColor White -ForegroundColor Red;
        $usernames;
        Write-Host "Continue? Y or N" -BackgroundColor White -ForegroundColor Red;
        $continue = Read-Host;
     
        if($continue.ToUpper() -eq 'N'){
            Write-Host "ABORTING" -ForegroundColor Black -BackgroundColor RED;
            Break Script;
        }elseif($continue.ToUpper() -eq 'Y'){
            Write-Host "CONTINUING" -ForegroundColor Black -BackgroundColor GREEN;
        }
    }
 
    $groups = @();
     
    foreach($username in $usernames)
    {
        $groups += dsquery user -name $username | dsget user -samid;
        $groups += dsquery user -name $username | dsget user -memberof -expand | dsget group -samid | Sort -Unique ;
    }  
   
    $groups = $groups | Sort -Unique;
   
    $groups_trimmed = @();
    foreach($group in $groups)
    {
        # AD group names can have single quotes in them
        # Escape single quotes with double single quotes
        $group = $group.replace("'", "''")
        $groups_trimmed += '''' + $group.Trim() + '''';
    }    
 
    $groups = $groups_trimmed;
    $groups_csv = $groups -join ",";
     
    <#  SET NOCOUNT ON Removes rows affected statement #>
    $query = "SET NOCOUNT ON;SELECT [name] FROM sys.server_principals where REPLACE([name], '$domain_name','') IN ($groups_csv)";
     
    <#  -h-1 removes headers #>
    $res = $null;
    $res = Invoke-Sqlcmd -ServerInstance $instance_name -Database master -Query $query ;
     
    $res | Sort-Object;


<#ADD COMMAS AND QUOTES AROUND LIST - Run this by highlighting and pressing F8 from the PowerShell ISE


$csv_res = "";
foreach($r in $res.Name){
    if($csv_res -ne ""){
        $csv_res += ",";
    }
    $csv_res += "'" + $r.Trim() + "'`n";
}

$csv_res;


#>