Sql Server Powershell

Add Domain User to Local Admin Group via DSC

Toying with DSC.  Here is some code to add a user to a local administrators group via DSC.  Use at your own risk.  Marginally tested (works on my machine…).

cls

configuration UserConfig{
    param(
        [System.Management.Automation.PSCredential]$DomainCredential
    )

    Import-DscResource -ModuleName PSDesiredStateConfiguration    

    node $AllNodes.NodeName{

        Group Admin{
            GroupName = 'Administrators'
            Ensure = 'Present'
            #PsDscRunAsCredential = $mycreds
            Credential = $DomainCredential
            Members = @('domainname\username')
        }

    }
}

$configData = @{
    AllNodes = @(
        @{
            NodeName = 'SEAPR1DBBAT046'
            PSDscAllowPlainTextPassword = $true
            PSDscAllowDomainUser = $true
        }
    )
}

$cred = Get-Credential -UserName domain\user -Message "Password please"
UserConfig -DomainCredential $cred -ConfigurationData $configData
Advertisements

Windows Live Writer

More for me than you.  If you like using windows live writer, the link to download it is now here.

Set Sql Server Configuration Property

Quick example function on how to set a sql server configuration property.

function Set-SqlConfigValue{
    [cmdletbinding(SupportsShouldProcess)]
    param(
        [string]$ServerName,
        [string]$ConfigName,
        [int]$ConfigValue
    )
    begin{
        Import-Module SqlPS -DisableNameChecking -Verbose:$false
    }
    process{

        Write-Verbose "Connecting to server $ServerName..."
        $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $ServerName

        try{
            $srvConn.connect();
            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

            Write-Verbose "Checking to see if $ConfigName is a valid property of the Sql Server Configuration..."
            $prop = $srv.Configuration | Get-Member -MemberType Property | where{$_.Name -eq $ConfigName}
            if($prop -eq $null){
                throw "Property $ConfigName is not a valid sql server property"
                return;
            }

            Write-Verbose "Altering configuration $ConfigName to $ConfigValue"
            $srv.Configuration.Properties[$ConfigName].ConfigValue = $ConfigValue;
            $srv.Alter();
        }
        catch{
            throw $_ 
        }
        finally{
            $srvConn.Disconnect();
        }

    }
    end{

    }
}

Get Backup Directory for all Sql Servers via Powershell

Quick script to spit out the backup directories for your sql servers. 

Import-Module SqlPS -DisableNameChecking

$objects = @();

try{

    gc -Path c:\Servers.txt | %{
        $srv = new-object Microsoft.SqlServer.Management.Smo.Server $_
        $objects += [PSCustomObject]@{
            ServerName = $srv.Name
            BackupDir = $srv.BackupDirectory
        }
    }

    $objects | Out-GridView
}
catch{
    $_ | fl -Force
}

Remove Database User & Login via powershell

Kind of a re-post of an older post of mine.  This one doesn’t use SQLPS though and it also drops the login from the server as well.  Use at your own risk.

Import-Module SqlPS -DisableNameChecking

$serverName = 'ServerName'
$loginToRemove = 'LoginName'
 
try{

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName
    $srv.Databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{

        foreach($user in $_.Users | where{$_.Name -eq $loginToRemove}){
            $user.Drop();
        }
    }
    foreach($login in $srv.Logins | where{$_.Name -eq $loginToRemove}){
        $login.drop();
    }
}
catch{
    $_ | fl -Force
}

Check Disk Offset using Powershell

Quick script to make sure your disk partitions are properly aligned for a sql server installation.  Read this article for disk partition best practices as to why you should always ensure that your offset is correctly set. 

cls

$objects = @();

@('Server1', 'Server2') | %{
	try{
        $srvName = $_
		Get-WmiObject -Class Win32_DiskPartition -ComputerName $srvName | %{
            $objects += [PSCustomObject]@{
                ServerName = $srvName
                DiskName = $_.Name
                StartOffset = $_.StartingOffset 
                Result = if(($_.StartingOffset % 4096) -eq 0){"Partitioned Correctly"}else{"ISSUE"}
            }
		}
	}
	catch{
		$_ | fl -Force
	}
}
$objects | Out-GridView

Set Server Default Backup Directory via Powershell

Quick script on how to set a sql servers’ default backup directory via powershell.  Use at your own risk.

#requires -module SqlPS
#requires -version 4

function Set-SqlDefaultBackupDirectory{
    [cmdletbinding(SupportsShouldProcess=$true)]
    param(
        [string]$ComputerName,
        [string]$BackupDirectory
    )
    begin{
        Import-Module SqlPS -DisableNameChecking
    }
    process{
        try{
            if($PSCmdlet.ShouldProcess($ComputerName)){
                $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $ComputerName
                $srv.BackupDirectory = $BackupDirectory
                $srv.Alter();
            }
            else{
                Write-Host "Setting the default backup directory on server $ComputerName to $BackupDirectory"
            }
        }
        catch{
            throw $_
        }
    }
    end{

    }
}

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
 }