SQL Server Database and Agent Account permissions

param($accountToAdd)
#written by Ingo Karstein, http://blog.karstein-consulting.com
#  v1.0, 01/03/2014

## <--- configure="" here="" p="">
if( [string]::IsNullOrEmpty($accountToAdd) ) {
Write-Host "no account specified"
exit
}

## ---> End of Config

$sidstr = $null
try {
$ntprincipal = new-object System.Security.Principal.NTAccount "$accountToAdd"
$sid = $ntprincipal.Translate([System.Security.Principal.SecurityIdentifier])
$sidstr = $sid.Value.ToString()
} catch {
$sidstr = $null
}

Write-Host "Account: $($accountToAdd)" -ForegroundColor DarkCyan

if( [string]::IsNullOrEmpty($sidstr) ) {
Write-Host "Account not found!" -ForegroundColor Red
exit -1
}

Write-Host "Account SID: $($sidstr)" -ForegroundColor DarkCyan

$tmp = [System.IO.Path]::GetTempFileName()

Write-Host "Export current Local Security Policy" -ForegroundColor DarkCyan
secedit.exe /export /cfg "$($tmp)"

$c = Get-Content -Path $tmp

$currentSetting = ""

foreach($s in $c) {
## Logon as a service
if( $s -like "SeServiceLogonRight*") {
$x = $s.split("=",[System.StringSplitOptions]::RemoveEmptyEntries)
$currentSetting = $x[1].Trim()

        if( $currentSetting -notlike "*$($sidstr)*" ) {
        Write-Host "Modify Setting ""Logon as a Service""" -ForegroundColor DarkCyan

        if( [string]::IsNullOrEmpty($currentSetting) ) {
        $currentSetting = "*$($sidstr)"
        } else {
        $currentSetting = "*$($sidstr),$($currentSetting)"
        }

        Write-Host "$currentSetting"

        $outfile = @"
        [Unicode]
        Unicode=yes
        [Version]
        signature="`$CHICAGO`$"
        Revision=1
        [Privilege Rights]
        SeServiceLogonRight = $($currentSetting)
"@

        $tmp2 = [System.IO.Path]::GetTempFileName()


        Write-Host "Import new settings to Local Security Policy" -ForegroundColor DarkCyan
        $outfile | Set-Content -Path $tmp2 -Encoding Unicode -Force

        #notepad.exe $tmp2
        Push-Location (Split-Path $tmp2)

        try {
        secedit.exe /configure /db "secedit.sdb" /cfg "$($tmp2)" /areas USER_RIGHTS
        #write-host "secedit.exe /configure /db ""secedit.sdb"" /cfg ""$($tmp2)"" /areas USER_RIGHTS "
        } finally {
        Pop-Location
        }
        } else {
        Write-Host "NO ACTIONS REQUIRED! Account already in ""Logon as a Service""" -ForegroundColor DarkCyan
        }
}
## Logon as a batch job
if( $s -like "SeBatchLogonRight*") {
$x = $s.split("=",[System.StringSplitOptions]::RemoveEmptyEntries)
$currentSetting = $x[1].Trim()

if( $currentSetting -notlike "*$($sidstr)*" ) {
Write-Host "Modify Setting ""Logon as a Batch job""" -ForegroundColor DarkCyan

if( [string]::IsNullOrEmpty($currentSetting) ) {
$currentSetting = "*$($sidstr)"
} else {
$currentSetting = "*$($sidstr),$($currentSetting)"
}

Write-Host "$currentSetting"

$outfile = @"
        [Unicode]
        Unicode=yes
        [Version]
        signature="`$CHICAGO`$"
        Revision=1
        [Privilege Rights]
        SeBatchLogonRight = $($currentSetting)
"@

$tmp2 = [System.IO.Path]::GetTempFileName()


Write-Host "Import new settings to Local Security Policy" -ForegroundColor DarkCyan
$outfile | Set-Content -Path $tmp2 -Encoding Unicode -Force

#notepad.exe $tmp2
Push-Location (Split-Path $tmp2)

try {
secedit.exe /configure /db "secedit.sdb" /cfg "$($tmp2)" /areas USER_RIGHTS
#write-host "secedit.exe /configure /db ""secedit.sdb"" /cfg ""$($tmp2)"" /areas USER_RIGHTS "
} finally {
Pop-Location
}
    } else {
    Write-Host "NO ACTIONS REQUIRED! Account already in ""Logon as a Batch job""" -ForegroundColor DarkCyan
    }
}

## Replace a process level token
if( $s -like "SeAssignPrimaryTokenPrivilege*") {
$x = $s.split("=",[System.StringSplitOptions]::RemoveEmptyEntries)
$currentSetting = $x[1].Trim()

        if( $currentSetting -notlike "*$($sidstr)*" ) {
        Write-Host "Modify Setting ""Replace a process level token""" -ForegroundColor DarkCyan

        if( [string]::IsNullOrEmpty($currentSetting) ) {
        $currentSetting = "*$($sidstr)"
        } else {
        $currentSetting = "*$($sidstr),$($currentSetting)"
        }

        Write-Host "$currentSetting"

    $outfile = @"
        [Unicode]
        Unicode=yes
        [Version]
        signature="`$CHICAGO`$"
        Revision=1
        [Privilege Rights]
        SeAssignPrimaryTokenPrivilege = $($currentSetting)
"@

        $tmp2 = [System.IO.Path]::GetTempFileName()


        Write-Host "Import new settings to Local Security Policy" -ForegroundColor DarkCyan
        $outfile | Set-Content -Path $tmp2 -Encoding Unicode -Force

        #notepad.exe $tmp2
        Push-Location (Split-Path $tmp2)

        try {
        secedit.exe /configure /db "secedit.sdb" /cfg "$($tmp2)" /areas USER_RIGHTS
        #write-host "secedit.exe /configure /db ""secedit.sdb"" /cfg ""$($tmp2)"" /areas USER_RIGHTS "
        } finally {
        Pop-Location
        }
        } else {
        Write-Host "NO ACTIONS REQUIRED! Account already in ""Replace a process level token""" -ForegroundColor DarkCyan
        }
}

## Bypass traverse checking
if( $s -like "SeChangeNotifyPrivilege*") {
$x = $s.split("=",[System.StringSplitOptions]::RemoveEmptyEntries)
$currentSetting = $x[1].Trim()

        if( $currentSetting -notlike "*$($sidstr)*" ) {
        Write-Host "Modify Setting ""Bypass traverse checking""" -ForegroundColor DarkCyan

        if( [string]::IsNullOrEmpty($currentSetting) ) {
        $currentSetting = "*$($sidstr)"
        } else {
        $currentSetting = "*$($sidstr),$($currentSetting)"
        }

        Write-Host "$currentSetting"

    $outfile = @"
        [Unicode]
        Unicode=yes
        [Version]
        signature="`$CHICAGO`$"
        Revision=1
        [Privilege Rights]
        SeChangeNotifyPrivilege = $($currentSetting)
"@

        $tmp2 = [System.IO.Path]::GetTempFileName()


        Write-Host "Import new settings to Local Security Policy" -ForegroundColor DarkCyan
        $outfile | Set-Content -Path $tmp2 -Encoding Unicode -Force

        #notepad.exe $tmp2
        Push-Location (Split-Path $tmp2)

        try {
        secedit.exe /configure /db "secedit.sdb" /cfg "$($tmp2)" /areas USER_RIGHTS
        #write-host "secedit.exe /configure /db ""secedit.sdb"" /cfg ""$($tmp2)"" /areas USER_RIGHTS "
        } finally {
        Pop-Location
        }
        } else {
        Write-Host "NO ACTIONS REQUIRED! Account already in ""Bypass traverse checking""" -ForegroundColor DarkCyan
        }
}

## Perform Volume Maintenance Tasks
if( $s -like "SeManageVolumePrivilege*") {
$x = $s.split("=",[System.StringSplitOptions]::RemoveEmptyEntries)
$currentSetting = $x[1].Trim()

        if( $currentSetting -notlike "*$($sidstr)*" ) {
        Write-Host "Modify Setting ""Perform Volume Maintenance Tasks""" -ForegroundColor DarkCyan

        if( [string]::IsNullOrEmpty($currentSetting) ) {
        $currentSetting = "*$($sidstr)"
        } else {
        $currentSetting = "*$($sidstr),$($currentSetting)"
        }

        Write-Host "$currentSetting"

    $outfile = @"
        [Unicode]
        Unicode=yes
        [Version]
        signature="`$CHICAGO`$"
        Revision=1
        [Privilege Rights]
        SeManageVolumePrivilege = $($currentSetting)
"@

        $tmp2 = [System.IO.Path]::GetTempFileName()


        Write-Host "Import new settings to Local Security Policy" -ForegroundColor DarkCyan
        $outfile | Set-Content -Path $tmp2 -Encoding Unicode -Force

        #notepad.exe $tmp2
        Push-Location (Split-Path $tmp2)

        try {
        secedit.exe /configure /db "secedit.sdb" /cfg "$($tmp2)" /areas USER_RIGHTS
        #write-host "secedit.exe /configure /db ""secedit.sdb"" /cfg ""$($tmp2)"" /areas USER_RIGHTS "
        } finally {
        Pop-Location
        }
        } else {
        Write-Host "NO ACTIONS REQUIRED! Account already in ""Perform Volume Maintenance Tasks""" -ForegroundColor DarkCyan
        }
}

## Adjust memory quotas for a process
if( $s -like "SeIncreaseQuotaPrivilege*") {
$x = $s.split("=",[System.StringSplitOptions]::RemoveEmptyEntries)
$currentSetting = $x[1].Trim()

        if( $currentSetting -notlike "*$($sidstr)*" ) {
        Write-Host "Modify Setting ""Adjust memory quotas for a process""" -ForegroundColor DarkCyan

        if( [string]::IsNullOrEmpty($currentSetting) ) {
        $currentSetting = "*$($sidstr)"
        } else {
        $currentSetting = "*$($sidstr),$($currentSetting)"
        }

        Write-Host "$currentSetting"

    $outfile = @"
        [Unicode]
        Unicode=yes
        [Version]
        signature="`$CHICAGO`$"
        Revision=1
        [Privilege Rights]
        SeIncreaseQuotaPrivilege = $($currentSetting)
"@

        $tmp2 = [System.IO.Path]::GetTempFileName()


        Write-Host "Import new settings to Local Security Policy" -ForegroundColor DarkCyan
        $outfile | Set-Content -Path $tmp2 -Encoding Unicode -Force

        #notepad.exe $tmp2
        Push-Location (Split-Path $tmp2)

        try {
        secedit.exe /configure /db "secedit.sdb" /cfg "$($tmp2)" /areas USER_RIGHTS
        #write-host "secedit.exe /configure /db ""secedit.sdb"" /cfg ""$($tmp2)"" /areas USER_RIGHTS "
        } finally {
        Pop-Location
        }
        } else {
        Write-Host "NO ACTIONS REQUIRED! Account already in ""Adjust memory quotas for a process""" -ForegroundColor DarkCyan
        }
}

## Act as part of operating system
if( $s -like "SeTcbPrivilege*") {
$x = $s.split("=",[System.StringSplitOptions]::RemoveEmptyEntries)
$currentSetting = $x[1].Trim()

        if( $currentSetting -notlike "*$($sidstr)*" ) {
        Write-Host "Modify Setting ""Act as part of operating system""" -ForegroundColor DarkCyan

        if( [string]::IsNullOrEmpty($currentSetting) ) {
        $currentSetting = "*$($sidstr)"
        } else {
        $currentSetting = "*$($sidstr),$($currentSetting)"
        }

        Write-Host "$currentSetting"

    $outfile = @"
        [Unicode]
        Unicode=yes
        [Version]
        signature="`$CHICAGO`$"
        Revision=1
        [Privilege Rights]
        SeTcbPrivilege = $($currentSetting)
"@

        $tmp2 = [System.IO.Path]::GetTempFileName()


        Write-Host "Import new settings to Local Security Policy" -ForegroundColor DarkCyan
        $outfile | Set-Content -Path $tmp2 -Encoding Unicode -Force

        #notepad.exe $tmp2
        Push-Location (Split-Path $tmp2)

        try {
        secedit.exe /configure /db "secedit.sdb" /cfg "$($tmp2)" /areas USER_RIGHTS
        #write-host "secedit.exe /configure /db ""secedit.sdb"" /cfg ""$($tmp2)"" /areas USER_RIGHTS "
        } finally {
        Pop-Location
        }
        } else {
        Write-Host "NO ACTIONS REQUIRED! Account already in ""Act as part of operating system""" -ForegroundColor DarkCyan
        }
}



}

Write-Host "Done." -ForegroundColor DarkCyan

MSDTC Settings

Set-ItemProperty -Path HKLM:\Software\Microsoft\MSDTC\Security -Name NetworkDtcAccessAdmin -Value 1

Set-ItemProperty -Path HKLM:\Software\Microsoft\MSDTC\Security -Name XaTransactions -Value 1

Set-ItemProperty -Path HKLM:\Software\Microsoft\MSDTC\Security -Name NetworkDtcAccessTransactions -Value 1

Set-ItemProperty -Path HKLM:\Software\Microsoft\MSDTC\Security -Name NetworkDtcAccess -Value 1

Set-ItemProperty -Path HKLM:\Software\Microsoft\MSDTC\Security -Name NetworkDtcAccessOutbound -Value 1

Set-ItemProperty -Path HKLM:\Software\Microsoft\MSDTC\Security -Name NetworkDtcAccessClients -Value 1

Set-ItemProperty -Path HKLM:\Software\Microsoft\MSDTC\Security -Name NetworkDtcAccessTip -Value 1

Set-ItemProperty -Path HKLM:\Software\Microsoft\MSDTC\Security -Name NetworkDtcAccessInbound -Value 1

Set Failover Cluster parameters

Import-Module FailoverClusters
Get-ClusterResource CLAG1 -T | get-ClusterParameter
Get-ClusterResource CLAG1 -T | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource CLAG1 -T | Set-ClusterParameter HostRecordTTL 30

TempDB sizing

-- Get the temp sizing for DB
IF OBJECT_ID ('tempdb..#tableSizes') IS NOT NULL
DROP TABLE tempdb..#tableSizes

CREATE TABLE #tableSizes
(
TableName VARCHAR(1000),
EstimatedRowCount BIGINT,
TotalSpaceGB DECIMAL(10,2)
);

DECLARE @DB_Name varchar(100)
DECLARE @Command NVARCHAR(MAX)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM master.sys.sysdatabases WHERE name IN (
'test1',
'test2',
'test3')

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Command = 'USE [@DB_Name]
INSERT INTO #tableSizes
SELECT ''['' + DB_NAME() + ''].['' + s.Name + ''].['' + t.NAME + '']'' AS TableName,
p.rows AS EstimatedRowCounts,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) / 1024 AS NUMERIC(36, 2)) AS TotalSpaceGB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
'
SELECT @Command = REPLACE(@Command,'@DB_Name',@DB_Name)
EXEC sp_executesql @Command
  FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor


 SELECT * FROM #tableSizes

 SELECT TOP 1 (TotalSpaceGB)*1.5 AS 'TempDB' FROM #tableSizes ORDER BY TotalSpaceGB DESC

Enable AlwaysOn Feature using powershell

I have noticed many might be enabling or disabling SQLServer Alwayson using GUI which is time-consuming and also tedious. Once you have failover cluster manager setup on a collection of the node. You can use the below commands to enable Alwayson feature and restart SQL server on all the instance which are part of the failover clustering. 

Default Instance 

$Nodes  =('ULDB01')
$InstanceName='MSSQLSERVER'
foreach($node in $Nodes ){
Enable-SqlAlwaysOn -Path "SQLSERVER:\SQL\$node\DEFAULT" -Force
Get-service "$InstanceName" -Computername $node | stop-service -passthru -force
Get-service "$InstanceName" -Computername $node | start-service -passthru
Get-service "SQLSERVERAGENT" -Computername $node | start-service -passthru
}

Named Instance

$Nodes  =('ULDB01')
$InstanceName='MSSQL'
$NamedInstance='$MON'
foreach($node in $Nodes ){
Enable-SqlAlwaysOn -Path "SQLSERVER:\SQL\$node\DEFAULT" -Force
Get-service "$InstanceName$NamedInstance" -Computername $node | stop-service -passthru -force 
Get-service "$InstanceName$NamedInstance" -Computername $node | start-service -passthru
Get-service "SQLAGENT$NamedInstance" -Computername $node | start-service -passthru
}