Get Objects in the *deny* roles in Sql Server via Powershell

Quick script to find all the objects (Users, Groups) in the *deny* roles in all databases.  Handy for when your AD account setups are heavily nested with duplicate users in multiple conflicting groups .  If you find yourself having to use deny in order to get permissions correct, it’s probably time you flattened out your groups a bit.  Using DENY can be a chore to track down.  This just spits out a gridview of the members of the *deny* roles.

Import-Module SqlPS -DisableNameChecking

$objects = @();
$servers = gc -Path c:\Servers.txt

try{

    $Servers | %{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $_
        $srv.Databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{
            $dbName = $_.Name 
            $_.Roles | where{$_.Name -like '*deny*'} | %{
                $roleName = $_.Name 
                $roleMembers = $_.EnumMembers()
                $roleMembers | %{
                    $objects += [PSCustomObject] @{
                        ServerName = $srvName
                        DatabaseName = $dbName
                        Role = $roleName
                        RoleMember = $_
                    }
                }
            }
        }
    }
    $objects | Out-GridView
}
catch{
    $_ | fl -Force
}

Change the sa password in Sql Server via Powershell

Here is how you change the sa password for your sql servers via powershell.  Use with caution, marginally tested.  Make sure your sa password is somewhat complex or this will throw an exception.

import-module SqlPS -DisableNameChecking
[string]$newPwd = 'C0mp13xP@55word%'


try{
    gc -Path c:\Servers.txt | %{

        $srvName = $_
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvName
        $srv.Logins | where{$_.Name -eq 'sa'} | %{
            $_.ChangePassword($newPwd);
        }
    }
}
catch{
    $_ | fl -Force
}

Create a Zero’d out File with Powershell

When sql server adds space to a transaction log file (or creates the transaction log file) it needs to zero-out said file.  Whilst zeroing out the file Sql Server is unfortunately read-only.  Hence why you need to be careful in setting reasonable transaction log growth sizes.  I wanted to get a quick fix on how long a particularly old system took to create a file and zero it out, hence this script. 

$start = get-date
$File = 'c:\fileToCreate.txt'

if(Test-Path $File){
    Remove-Item $File -Force
}
 
$arrSize= 64kb
$fileSize= 1GB
$buffer = new-object byte[]($arrSize)
$stream = [io.File]::OpenWrite($FilePath)

try{
    $size = 0
    while($size -lt $fileSize){
        $stream.Write($buffer, 0, $buffer.Length);
        $size += $buffer.Length;
    }
} 
finally{
    if($stream){
        $stream.Close();
    }
    $ts = New-TimeSpan -Start $start -End (Get-Date)
    $ts
 }

Collect Perfmon Data via Powershell

Quick script to collection permon data via powershell.  This will continuously spit out just the physical disk stats to a csv file (max 1GB) every 10 seconds.  Use at your own risk.  Barely tested.

function Get-PhysicalDisk{
    param(
        [Parameter(Mandatory=$true,ValueFromPipeline=$false)] 
        $ComputerName,
        [Parameter(Mandatory=$true,ValueFromPipeline=$false)] 
        $FileName
    )

    $outputs = @();
    $1GB = 1GB

    if(Test-Path $FileName){
        Remove-Item $FileName -Force
    }

    $physDiskCounters = Get-Counter -ComputerName $ComputerName -ListSet PhysicalDisk 
    Get-Counter -ComputerName $ComputerName -Counter $physDiskCounters.Paths -Continuous -SampleInterval 10 | Export-Counter -Path $FileName -FileFormat "CSV" -Circular -MaxSize $1GB -Force

}



I usually start this in a job on multiple servers via the Start-Job cmdlet so it will run in the background like so:

gc -Path C:\Servers.txt | %{
    $srvName = $_
    $fileName = "c:\Test\$($srvName)_PhysicalDisk.csv"

    $job = Start-Job {
        param(
            $compName,
            $fName
        )
        . "C:\PathToGetPhysicalDiskFile\Get-PhysicalDisk.ps1"
        Get-PhysicalDisk $compName $fName
    } -ArgumentList @($srvName, $fileName)
}

Add a linked server & remote login to Sql Server via Powershell

Sort of.  Trying to do this via just SMO and powershell was immensely frustrating when dealing with the remotelogin security.  What I wanted the login to do was to use a sql login no matter the context (the bottom-most radio button in the screenshot below).

image

 

Alas, every time I set the remotelogin property and called the SetRemotePassword() method it would automatically add it to the Local server login to remote server login mappings list.

image

Not what I want.  In order to determine the proper…properties to set I created a new linked server and scripted it out to have a gander at the properties.

image

Two properties stood out.  The @locallogin and the @useself.  Okay, cool.  Easy enough, the smo linkedserverlogin has a properties collection in it, should be easy enough to set.  Except, the bloody properties never get populated.  Even after calling the Initialize($true) on the LinkedServerLogin smo object, said properties are not there. 

Okay, let’s add them.  Nope.  There is no Add() method on the SqlPropertyCollection (or on the smo PropertyCollection inherited class).  In the end, I ended up just having to execute a sql call in order to get this to work, as I’d already spent 4 stinking hours trying to get this to work correctly.  I detest having to resort to doing this, but I HAVE to move on.

Here it is.  If you have this figured, please please let me know what the poop is.

Import-Module sqlps -DisableNameChecking

$srvName = 'ServerName'
$linkedServerLogin = 'LinkedServerLoginName'
$pwd = 'LinkedServerPassword'
$servers = gc -Path C:\LinkedServers.txt

cls

try{
    $srv = New-Object Microsoft.SqlServer.Management.smo.server $srvName
    $servers | %{
        if($srv.LinkedServers.Contains($_)){
            return;
        }
        
        $linkedServer = New-Object Microsoft.SqlServer.Management.Smo.LinkedServer 
        $linkedServer.Parent = $srv
        $linkedServer.Name = $_
        $linkedServer.DataSource = $_
        #$srv.LinkedServers.Add($linkedServer);
        $linkedServer.Create();

        $sql = "
        USE [master]
        GO
        EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'$($_)', @locallogin = NULL , @useself = N'False', @rmtuser = N'$($linkedServerLogin)', @rmtpassword = N'$($pwd)'
        GO
        "
        $srv.ConnectionContext.ExecuteNonQuery($sql);

    }
}
catch{
    $_ | fl -Force
}
finally{

}

Turn off AutoShrink on databases via powershell

Easy peasy script to turn off AutoShrink on all databases.  I’ll let Paul Randal do the talking as to why you shouldn’t have autoshrink turned on.

Import-Module sqlps -DisableNameChecking

$ignoreDBs = @('IgnoreDB')

try{
    gc -Path 'c:\Servers.txt' | %{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $_
        $srv.Databases | where{-not $_.IsSystemObject -and $_.Name -notin $ignoreDBs} | %{
            if($_.AutoShrink -eq $true){
                $_.AutoShrink = $false
                $_.Alter();
            }
        }        
    }
}
catch{
    $srvName
    $_ | fl -Force
}
finally{

}

Change TERMSERV logins in Windows Credential Manager

This script will delete and then re-add windows credential manager entries via the cmdkey command line utility.  I use this when I have to change windows account login to update all the termserv entries so I don’t have to save them all again.  Use at your own risk. 

cls
$objects = @();
$select = @('Target:', 'Type:', 'User:')
[int]$counter = 0;

$obj = New-Object -TypeName PSObject -Property @{
    Target = ''
    Type = ''
    User = ''
}

$creds = cmdkey /list | Select-String $select
$creds | %{
    $counter++;

    [string]$line = $_.ToString()

    switch -Wildcard ($_){
        "*Target:*"{
            $obj.Target = $line.Substring($line.IndexOf(':')+2, $line.Length - $line.IndexOf(':')-2)
            break;
        }
        "*Type:*"{
            $obj.Type = $line.Substring($line.IndexOf(':')+2, $line.Length - $line.IndexOf(':')-2)
            break;
        }
        "*User:*"{
            $obj.User = $line.Substring($line.IndexOf(':')+2, $line.Length - $line.IndexOf(':')-2)
            break;
        }
        
    }

    if($counter % 3 -eq 0){
        $objects += $obj;
        $obj = New-Object -TypeName PSObject -Property @{
            Target = ''
            Type = ''
            User = ''
        }
    }

}

$userName = 'domain\login'
$password = 'password'

$objects | where{$_.Target -like "*TERMSRV/*"} | %{
    $string = $_.Target
    $string = $string.Substring($string.LastIndexOf("=")+1)
    $cmd = "cmdkey /delete:$string"
    Invoke-Expression -Command $cmd
    $cmd = "cmdkey /add:$string /user:$userName /pass:$password"
    Invoke-Expression -Command $cmd
}

Expand Windows Groups on Sql Server

Quick post on how to expand windows groups to show their sub-groups and logins in sql server.  This just writes the group hierarchy to the console.  Use at your own risk.

import-module activedirectory

function Get-GroupHierarchy{
    param(
        [Parameter(Mandatory=$true)]
        [String]$searchGroup
    )
    $outputs = @();
    [int]$i++ | out-null;

    get-adgroupmember $searchGroup | sort-object objectClass -descending | %{
        $output = new-object -TypeName PSObject -Property @{
            Parent = $searchGroup
            GroupName = $_.Name
            Type = $_.objectClass
            Hierarchy = $i
        }
        $outputs += $output

        if($_.ObjectClass -eq 'group'){
            $outputs += Get-GroupHierarchy $_.name
        }
    }
    return $outputs;
}

cls
$srvName = 'ServerName'
$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $srvName
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
$ignoreGroups = @('NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT');

$srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsGroup -and $_.Name -notin $ignoreGroups} | %{
    $loginName = $_.Name.Replace('TRX0\', '')
    Write-Host "Windows Group:  $loginName" -ForegroundColor Green
    Get-GroupHierarchy $loginName | ft -AutoSize   
    Write-Host "`n`r"
}

Drop Linked Server from All Servers

Quick script on how to drop a linked server from a list of servers.  Minimally tested, use at your own risk.

 $LinkedServerName = 'LinkedServerToFindAndDrop'

try{
    gc -Path c:\Servers.txt | %{

        $srvName = $_
	    $srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	    $srvConn.ServerInstance = $srvName
	    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

        $linkedServer = $srv.LinkedServers | where{$_.DataSource -eq $LinkedServerName} 
        if($linkedServer -ne $null){
            for([int]$i = 0; $i -lt $linkedServer.LinkedServerLogins.Count; $i++){
                $login = $linkedServer.LinkedServerLogins.item($i);
                $login.Drop();
            }

            $linkedServer.Drop();
        }
    }

}
catch{
    $_ | fl -Force
} 

Associate User & Logins in Sql Server via Powershell

Quick script to re-associate logins with users that have the same name.  If the user in the database is named the same as the login and has no login currently associated with it, it will set the database user to use the login with the same name.  Apparently, you can’t do this directly via powershell by setting the users’ login to the login name, as it errors out with “Modifying the Login property of the User object is not allowed. You must drop and recreate the object with the desired property.”  Hence the SQL call to sync up the user.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$serverName = 'ServerName'

try{
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $serverName
	$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
	
	$srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin} | %{
		$login = $_
		$srv.Databases | %{
			if($_.Users.Contains($login.Name)){
				$user = $_.Users[$login.Name];
				if($user.Login -eq ''){
<#					#can't do this apparently, smo will only let you drop & re-create...
					$user.Login = $login.Name;
					$user.Alter();
#>
					$_.ExecuteNonQuery("sp_change_users_login 'auto_fix', '" + $user.Name + "'")
				}
			}
		}
	}
}
catch{
	$_ | fl -Force
}