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

GMSA Account Creation

For SQL Server 2012 Version (Windows Server 2016), Use Service Account

For SQL Server 2016 Version (Windows Server 2016), Use Group Managed Service Account (GMSA)

• Steps to create the Group Managed Service Account:
  
  $Group="DB1"
  $HostName=""
  Import-Module ActiveDirectory
  New-ADGroup -Name $Group -SamAccountName $Group -GroupCategory Security -GroupScope Global -DisplayName "SQLServer GMSA Account" -Path "OU=Groups,OU=Windows 2016,OU=Ser,OU=ITDept,DC=dd,DC=aux,DC=intranet" -Description "Members of this group are SQL Server GMSA"
  Add-ADGroupMember -Identity $Group -Members $HostName
  New-ADServiceAccount -name $Group -DNSHostName $($Group).com.au -PrincipalsAllowedToRetrieveManagedPassword $Group
  Add-ADGroupMember -Identity $Group -Members $HostName
• Check with the Windows Team to see if the Group Managed Service Account is added to correct Group Policy.

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

Cassandra Shutdown and Node Check

# Increase memory on the cassandra node

# nodetool drain
# nodetool stopdaemon
# shutdown now

# increase ram in the vsphere client

# check the memory

# free -m
#cat /proc/meminfo

# check for error message
# cat /var/log/messages

# GC pauses
sav /etc/cassandra/conf/cassandra-env.conf

# nodetool drain
# nodetool flush
# nodetool stopdaemon
# nodetool status
change the directory in the /etc/cassandra/conf/cassandra-env.conf

Change the current one from JVM_OPTS="$JVM_OPTS -Xms12g -Xmx20g
to -Xms20000m  -Xmx20000m

#service cassandra start

#free -m
#cat /proc/meminfo

#cat /var/log/messages to see the errors