102 lines
3.4 KiB
PowerShell
102 lines
3.4 KiB
PowerShell
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;
|
|
|
|
|
|
#> |