SqlStig

0.1.0.2

SqlStig.psm1

$script:LocalNames = @(".", "localhost", "127.0.0.1", "", $env:COMPUTERNAME)
[System.String[]]$script:IgnoreNames = @("##*##", "NT SERVICE\*", "NT AUTHORITY\*", "sys", "INFORMATION_SCHEMA")
[System.String[]]$script:DatabaseAudits = @("SELECT", "INSERT", "UPDATE", "DELETE", "EXECUTE")
[System.String[]]$script:LogLevels = @("Always", "Never", "OnFailure", "OnSuccess")
[System.String]$script:SA_SID = [System.Convert]::ToBase64String(0x01)
[System.Boolean]$script:EXPLICIT_TRUE = $true
[System.Boolean]$script:INHERITED_FALSE = $false

[System.String]$EXISTING_CONNECTION = "ExistingConnection"

Import-SqlModule -LoadSMO

#region Top Level Functions

Function Set-SQLInstanceStigItems {
    <#
        .SYNOPSIS
            Executes all of the SQL 2014 INSTANCE STIG settings included in the module.
 
        .DESCRIPTION
            The cmdlet runs each audit and configuration setting in the module.
 
            This cmdlet will take the following actions:
 
            1) Sets permissions on the audit files identified in the database
            2) Sets permissions and auditing on the installation location of the shared files and directories
            3) Sets permissions on the directory structure holding the SQL Instance database and log files
            4) Enables the default trace
            5) Disables xp_cmdshell
            6) Renames the built in sa account
            7) Disabled the built in sa account
            8) Creates logins and builds 2 roles with those logins as members that have permissions for Instance and Database auditing respectively
            9) Creates the required auditing for the Instance and optionally on every database
            10) Creates logins and builds 4 roles with those logins as members that supplement the built in fixed roles, sysadmin, serveradmin, and dbcreator. It also creates a
                role for the CONNECT SQL permission. Any existing logins that have the permissions assigned to these roles explicity will have those permissions revoked and moved to
                the new role. Additionally, any existing logins that are members of the built in fixed roles will be moved into the new role. The 4 roles are named SYSADMIN_ROLE, SERVERADMIN_ROLE,
                DBCREATOR_ROLE, and CONNECT_SQL_ROLE. This action ignores login principals like ##*##, NT SERVICE\*, NT AUTHORITY\*, sys, and INFORMATION_SCHEMA.
            11) Forces encrypted connections, if no certificate is defined for the SQL instance, this setting is ignored
            12) Creates a job category for STIG Audits
            13) Sets the password policy and expiration for local SQL accounts, including the sa account.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER AuditorAdministratorsName
            The name of the user or group responsible for the audit administrator role.
 
        .PARAMETER AuditorsName
            The name of the user or group responsible for auditing databases, primarily accessing audit log files.
 
        .PARAMETER EncryptConnection
            Specifies that connections made to the SQL instance are encrypted. The SQL instance must have a trusted certificate configured for this to work.
 
        .PARAMETER Version
            The version of SQL being configured, select from either 10 (SQL 2005), 11 (SQL 2008), 12 (SQL 2014), or 13 (SQL 2016). This defaults to 12. It is used to locate the correct modules in the filesystem.
 
        .PARAMETER SqlCredential
            The credentials to use for SQL Authentication.
 
        .PARAMETER Credential
            The credential to use to connect to the host node to set the force encryption registry key.
 
        .PARAMETER Port
            The port to connect to the SQL Instance on, this defaults to using the SQL Browser service to determine the correct port for the instance.
 
        .PARAMETER SQLAdministratorsName
            The Windows User or Group that is granted full control over the SQL Instance directory structure.
 
        .PARAMETER ServerAuditor
            The Windows User or Group that is granted ALTER ANY SERVER AUDIT on the SQL Instance via role membership.
 
        .PARAMETER DatabaseAuditor
            The Windows User or Group that is granted ALTER ANY DATABASE AUDIT on every database on the SQL Instance via role membership.
 
        .PARAMETER NewSAName
            The new name to assign to the built in sa account. This defaults to xsa.
 
        .PARAMETER AuditDestination
            The destination to send audits, either the ApplicationLog, SecurityLog, or File. This defaults to ApplicationLog. There are special considerations when choosing the SecurityLog option, see https://msdn.microsoft.com/en-us/library/cc645889.aspx.
             
        .PARAMETER AuditFileDestinationPath
            If the AuditDestination parameter is set to File, this is the location the audit file will be written. This defaults to "$env:SYSTEMROOT\Program Files\Microsoft SQL Server\Logs\AuditLog.trc". If the AuditDestination is set to ApplicationLog or SecurityLog, this setting is ignored.
 
        .PARAMETER SysAdminRoleMembers
            The names of Windows Users or Groups or local SQL accounts that should have a new Server Login created, if it doesn't already exist, and made a member of the new SYSADMIN role.
 
        .PARAMETER ServerAdminRoleMembers
            The names of Windows Users or Groups or local SQL accounts that should have a new Server Login created, if it doesn't already exist, and made a member of the new SERVERADMIN role.
 
        .PARAMETER DBCreatorRoleMembers
            The names of Windows Users or Groups or local SQL accounts that should have a new Server Login created, if it doesn't already exist, and made a member of the new DBCREATOR role.
 
        .PARAMETER ConnectSqlRoleMembers
            The names of Windows Users or Groups or local SQL accounts that should have a new Server Login created, if it doesn't already exist, and made a member of the new CONNECTSQL role.
 
        .PARAMETER IncludeDatabaseLevelAuditing
            This parameter specifies that all INSERT, UPDATE, SELECT, DELETE, and EXECUTE actions on every database except the master are audited. Use with caution since this will fill up the logs quickly.
 
        .PARAMETER IsManagedSQL
            This parameter specifies that the SQL Instance being STIG'd is a managed service, so the configurations at the OS level, such as file and folder permissions and auditing will not be applied.
 
        .PARAMETER DatabaseMailOriginatingAddress
            The email address used as the origin for database emails.
 
        .PARAMETER DatabaseMailReplyToAddress
            The email address used for replies from database emails.
 
        .PARAMETER DatabaseMailSmtpServer
            The smtp server address used to send emails. This defaults to the local server.
             
        .PARAMETER DoNotSetupDatabaseMail
            This parameter disables setting up the default database mail settings.
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .EXAMPLE
            Set-SQLInstanceStigItems -SqlAdministratorsName "Contoso\UG-SQL-Admins" `
                        -AuditorAdministratorsName "Contoso\UG-SQL-Audit-Admins" `
                        -AuditorsName "Contoso\UG-SQL-Auditors" `
                        -ServerAuditor "Contoso\UG-SQL-ServerAuditors" `
                        -DatabaseAuditor "Contoso\UG-SQL-DatabaseAuditors" `
                        -ComputerName "SQL2014" `
                        -AuditDestination "ApplicationLog" `
                        -SysAdminRoleMembers @("Contoso\UG-SQL-DEV_001-SysAdmins") `
                        -ServerAdminRoleMembers @("Contoso\UG-SQL-DEV_001-ServerAdmins") `
                        -DBCreatorRoleMembers @("Contoso\UG-SQL-DEV_001-DBCreators") `
                        -Verbose
 
            Configures all of the settings in this module.
 
        .NOTES
            This cmdlet must be run with sysadmin permissions on the SQL instance and local admin access on the node or server supporting the instance.
             
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
 
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-011300
                SQL4-00-030400
                SQL4-00-011900
                SQL4-00-012000
                SQL4-00-012100
                SQL4-00-012200
                SQL4-00-012300
                SQL4-00-012400
                SQL4-00-030600
                SQL4-00-034000
                SQL4-00-034200
                SQL4-00-035600
                SQL4-00-037500
                SQL4-00-037600
                SQL4-00-037700
                SQL4-00-037800
                SQL4-00-037900
                SQL4-00-038000
                SQL4-00-017100
                SQL4-00-010200
                SQL4-00-017200
                SQL4-00-018700
                SQL4-00-020500
                SQL4-00-032500
                SQL4-00-033900
                SQL4-00-038900
                SQL4-00-013600
                SQL4-00-013700
                SQL4-00-013800
                SQL4-00-015350
                SQL4-00-031400
                SQL4-00-017100
    #>

    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$true, ParameterSetName = "Normal")]
        [Parameter(ParameterSetName = "ManagedSQL")]
        [ValidateNotNullOrEmpty()]
        [System.String]$SQLAdministratorsName,

        [Parameter(Mandatory=$true, ParameterSetName = "Normal")]
        [ValidateNotNullOrEmpty()]
        [System.String]$AuditorAdministratorsName,

        [Parameter(Mandatory=$true, ParameterSetName = "Normal")]
        [ValidateNotNullOrEmpty()]
        [System.String]$AuditorsName,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$ServerAuditor,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$DatabaseAuditor,

        [Parameter(Position=3)]
        [System.String]$ComputerName = "localhost",

        [Parameter(Position=4)]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(Position=5)]
        [switch]$EncryptConnection,

        [Parameter(Position=6)]
        [ValidateSet(10,11,12,13)]
        [System.Int32]$Version = 12,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$NewSAName = "xsa",

        [Parameter()]
        [ValidateSet("ApplicationLog","SecurityLog","File")]
        [System.String]$AuditDestination = "ApplicationLog",

        [Parameter()]
        [ValidateScript({ if ($AuditDestination -eq "File") { return ![System.String]::IsNullOrEmpty($_) } else {return $true} })]
        [System.String]$AuditFileDestinationPath = "$env:SYSTEMROOT\Program Files\Microsoft SQL Server\Logs\AuditLog.trc",

        [Parameter()]
        [System.String[]]$SysAdminRoleMembers,

        [Parameter()]
        [System.String[]]$ServerAdminRoleMembers,

        [Parameter()]
        [System.String[]]$DBCreatorRoleMembers,

        [Parameter()]
        [System.String[]]$ConnectSqlRoleMembers,

        [Parameter()]
        [switch]$IncludeDatabaseLevelAuditing,
        [Parameter(ParameterSetName = "ManagedSQL", Mandatory=$true)]
        [switch]$IsManagedSQL,

        [Parameter()] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter()] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]
        $SqlCredential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter()]
        [System.Int32]$Port = -1,

        [Parameter()]
        [ValidateNotNull()]
        [System.String]$DatabaseMailOriginatingAddress,

        [Parameter()]
        [ValidateNotNull()]
        [System.String]$DatabaseMailReplyToAddress,

        [Parameter()]
        [ValidateNotNull()]
        [System.String]$DatabaseMailSmtpServer,

        [Parameter()]
        [switch]$DoNotSetupDatabaseMail
    )

    Begin {
        if ($PSBoundParameters.ContainsKey("Version"))
        {
            Import-SqlModule -Version $Version -LoadSMO
        }
    }

    Process {
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $SqlCredential -Port $Port

        if(!$IsManagedSQL) {
            Write-Verbose -Message "The instance is not Managed SQL, implementing OS level configurations."

            Write-Host "Setting installation directory permissions and auditing."
            Set-SQLInstanceInstallationFilesPermissionsAndAuditing -SqlServer $SqlServer -InheritDefault

            Write-Host "Setting database directory and file permissions."
            Set-SQLInstanceDatabaseFilePermissions -SqlServer $SqlServer -SQLAdministratorsName $SQLAdministratorsName -IncludeLocalAdministrators

            Write-Host "Setting audit file permissions."
            Set-SQLInstanceAuditFilePermissions -SqlServer $SqlServer -AuditorsName $AuditorsName -AuditorAdministratorsName $AuditorAdministratorsName

            Write-Host "Setting force encryption."
            Set-SQLInstanceForceEncryption -SqlServer $SqlServer -Enable $true -Credential $Credential
        }
        else {
            Write-Verbose -Message "The instance is Managed SQL, not implementing OS level configurations."
        }

        if (!$DoNotSetupDatabaseMail)
        {
            Write-Host "Enabling database mail."
            Write-Host "Ensuring SQL Agent Service is available"
            Wait-SQLAgentService -SqlServer $SqlServer | Out-Null

            Enable-SQLInstanceDatabaseMail -SqlServer $SqlServer -EnableLogging

            Write-Host "Setting up database mail smtp account."

            if ([System.String]::IsNullOrEmpty($SmtpServer))
            {
                $SmtpServer = $SqlServer.NetName
            }

            $Account = New-SQLInstanceDatabaseMailSmtpAccount -SqlServer $SqlServer -OriginatingAddress $DatabaseMailOriginatingAddress -ReplyToAddress $DatabaseMailReplyToAddress -SmtpServer $DatabaseMailSmtpServer

            Write-Host "Setting up database mail default profile."
            [Microsoft.SqlServer.Management.Smo.Mail.MailProfile]$Profile = New-SQLInstanceDatabaseMailProfile -SqlServer $SqlServer -MailAccount $Account.Name
            Write-Host -Object "Use database mail profile name $($Profile.Name) when configuring database STIG later." -ForegroundColor Yellow  

            Set-SQLInstanceAgentMail -SqlServer $SqlServer -ProfileName $Profile.Name
        }

        Write-Host "Enabling default trace."
        Set-SQLInstanceDefaultTrace -SqlServer $SqlServer -Enabled $true

        Write-Host "Disabling xp_cmdshell."
        Set-SQLInstanceXPCmdShell -SqlServer $SqlServer -Enabled $false

        Write-Host "Setting auditors."
        Set-SQLInstanceAuditors -SqlServer $SqlServer -ServerAuditors $ServerAuditor -DatabaseAuditors $DatabaseAuditor

        Write-Host "Setting auditing."
        Set-SQLInstanceAuditing -SqlServer $SqlServer -AuditName "STIG_Audit" -AuditSpecificationName "STIG_Audit_Specification" -AuditDestination $AuditDestination -FilePath $AuditFileDestinationPath -IncludeDatabaseLevelAudting:$IncludeDatabaseLevelAuditing

        Write-Host "Setting management roles and logins."
        Set-SQLInstanceManagementRoles -SqlServer $SqlServer -ConnectSqlRoleMembers $ConnectSqlRoleMembers -SysAdminRoleMembers $SysAdminRoleMembers -ServerAdminRoleMembers $ServerAdminRoleMembers -DBCreatorRoleMembers $DBCreatorRoleMembers -DefaultSysAdmins $SQLAdministratorsName -DropSysAdmins $true -Force

        Write-Host "Creating Job Category"
        New-SQLInstanceJobCategory -SqlServer $SqlServer -Name "STIG Audits"

        Write-Host "Setting password polices."
        Set-SQLInstanceLoginPasswordPolicies -SqlServer $SqlServer -IncludeSA

        Write-Host "Renaming sa account to $NewSAName."
        Rename-SQLInstanceAccount -SqlServer $SqlServer -Sid 0x01 -NewName $NewSAName

        Write-Host "Disabling sa account."
        Disable-SQLInstanceAccount -SqlServer $SqlServer -Sid 0x01 

        Write-Host "Disabling Named Pipes and Shared Memory"
        Set-SQLInstanceProtocols -SqlServer $SqlServer -NamedPipesEnabled $false -SharedMemoryEnabled $false -RestartSqlService

        $SqlServer.ConnectionContext.Disconnect()

        Set-Location -Path $env:SystemDrive
        Write-Host "Completed STIG." -ForegroundColor Green
    }

    End {
    }
}

Function Set-SQLDatabaseStigItems {
    <#
        .SYNOPSIS
            Executes all of the SQL 2014 DATABASE STIG settings included in the module.
 
        .DESCRIPTION
            This cmdlet assumes there is a Mail Profile and at least 1 Operator set up with email addresses to utilize for sending notifications from the jobs that are created.
 
            This cmdlet will take the following actions:
     
            1) Creates a job to review the default trace file for CREATE, ALTER, and DROP commands targetted at functions, the job will email the results of the query
            2) Creates a job to review the default trace file for CREATE, ALTER, and DROP commands targetted at triggers
            3) Creates a job to review the default trace file for CREATE, ALTER, and DROP commands targetted at stored procedures
            4) If the database has an owner in a built in role, and the database is set to trustworthy, disables the trustworthy setting
            5) Optionally creates a Database DDL trigger to proactively notify administrators when CREATE, ALTER, and DROP commands are issued for functions, triggers, and stored procedures
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER DatabaseName
            The name of the database to run the commands against. In the case of the nightly jobs, these are created to run against every database and will not be recreated each time the cmdlet is run.
 
        .PARAMETER AllDatabases
            This parameter specifies that the trustworthy setting and Database DDL Trigger (if selected) should be run against every database.
 
        .PARAMETER SqlCredential
            The credentials to use for SQL Authentication.
 
        .PARAMETER Port
            The port to connect to the SQL Instance on, this defaults to using the SQL Browser service to determine the correct port for the instance.
 
        .PARAMETER OperatorToEmail
            The Operator name who has an email address configured. The email property is extracted from the Operator object and used with the sp_send_dbmail procedure to send HTML formatted emails.
            The job results and DDL Trigger notifications are sent to these email addresses.
 
        .PARAMETER JobCategory
            The Job Category name to create the 3 jobs in. The defaults to "STIG Audits".
 
        .PARAMETER AlterFunctionJobName
            The name to use when the job to monitor function modifications is created. This defaults to Alter_Function_Audit. This name is also used to name the Job Step.
         
        .PARAMETER AlterTriggerJobName
            The name to use when the job to monitor trigger modifications is created. This defaults to Alter_Trigger_Audit. This name is also used to name the Job Step.
         
        .PARAMETER AlterXPJobName
            The name to use when the job to monitor stored procedure modifications is created. This defaults to Alter_XP_Audit. This name is also used to name the Job Step.
 
        .PARAMETER JobTime
            A TimeSpan object indicating the time of day the jobs should be run. This defaults to 0200.
 
        .PARAMETER MailProfile
            The mail profile to use when sending database mail.
 
        .PARAMETER CreateAuditTrigger
            This parameter specifies that Database DDL Triggers should be created to proactively notify administrators when CREATE, ALTER, or DROP statements are executed against functions, triggers, and stored procedures.
 
            This is a much more reliable way to accomplish the intent of the scheduled jobs, but does not explicitly meet the STIG requirements. Results of the trigger are sent via HTML email.
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .EXAMPLE
            Set-SQLDatabaseStigItems -ComputerName "SQL2014" `
                            -AllDatabases `
                            -OperatorToEmail "Database Administrators" `
                            -MailProfile "MSSQLSERVER Profile" `
                            -Verbose
 
            Implements the database STIG against all databases in the MSSQLSERVER instance on the SQL2014 server.
 
        .EXAMPLE
            Set-SQLDatabaseStigItems -ComputerName "SQL2014" `
                            -InstanceName "MyInstance" `
                            -DatabaseName "msdb" `
                            -OperatorToEmail "Database Administrators" `
                            -MailProfile "MSSQLSERVER Profile" `
                            -Verbose
 
            Implements the database STIG against the msdb database on the MyInstance instance on the SQL2014 server.
 
        .EXAMPLE
            Set-SQLDatabaseStigItems -ComputerName "SQL2014" `
                            -AllDatabases `
                            -OperatorToEmail "Database Administrators" `
                            -MailProfile "MSSQLSERVER Profile" `
                            -CreateAuditTrigger `
                            -Verbose
 
            Implements the database STIG against all databases in the MSSQLSERVER instance on the SQL2014 server and creates the Database DDL Trigger on all databases.
 
        .NOTES
            This cmdlet must be run with sysadmin permissions on the SQL instance and local admin access on the node or server supporting the instance.
             
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-014900
                SQL4-00-015100
                SQL4-00-015200
                SQL4-00-015610
                SQL4-00-015620
    #>
    [CmdletBinding()]
    Param(
        [Parameter()]
        [System.String]$ComputerName = "localhost",

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(Mandatory=$true, Position=0, ParameterSetName="DB")]
        [ValidateNotNullOrEmpty()]
        [System.String]$DatabaseName,

        [Parameter(Mandatory=$true, ParameterSetName="All")]
        [switch]$AllDatabases,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$OperatorToEmail,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$JobCategory = "STIG Audits",

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$AlterFunctionJobName = "Alter_Function_Audit",

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$AlterTriggerJobName = "Alter_Trigger_Audit",

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$AlterXPJobName = "Alter_XP_Audit",

        [Parameter()]
        [ValidateNotNull()]
        [System.TimeSpan]$JobTime = (New-Object -TypeName System.TimeSpan(2, 0, 0)),

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$MailProfile,

        [Parameter()]
        [switch]$CreateAuditTrigger,

        [Parameter()] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]
        $SqlCredential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter()]
        [System.Int32]$Port = -1
    )

    Begin {
    }

    Process {
        

        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Port $Port -Credential $SqlCredential

        Write-Host "Ensuring SQL Agent service is running"
        Wait-SQLAgentService -SqlServer $SqlServer | Out-Null
        Start-Sleep -Seconds 2
        Write-Host "Continuing..."

        if ($JobTime -in @($null, [System.TimeSpan]::Zero, [System.TimeSpan]::MaxValue, [System.TimeSpan]::MinValue)) {
            $JobTime = New-Object -TypeName System.TimeSpan(2, 0, 0)
        }

        [Microsoft.SqlServer.Management.Smo.Database[]]$DBs = @()

        if ($AllDatabases) {
            $DBs += $SqlConnection.Databases
        }
        else {
            $DBs += ($SqlConnection.Databases | Where-Object {$_.Name -ieq $DatabaseName} | Select-Object -First 1)
        }

        #region STIG ID: SQL4-00-014900
        Write-Host "Creating Function audit job"
        Write-Verbose -Message "STIG ID: SQL4-00-014900"

        if (($SqlServer.JobServer.Jobs | Where-Object {$_.Name -ieq $AlterFunctionJobName }) -eq $null) {
            Write-Verbose -Message "Creating function audit command text."
            $FunctionAuditCommandText = Get-SQLInstanceAuditCommandText -DaysDiff 1 `
                                            -JobName $AlterFunctionJobName `
                                            -OperatorToEmail $OperatorToEmail `
                                            -EmailProfile $MailProfile `
                                            -EmailSubject " - *Alert* FUNCTION Modification Detected" `
                                            -ObjectToAudit Function `

            Write-Verbose -Message "Creating function audit job."
            [Microsoft.SqlServer.Management.Smo.Agent.Job]$FunctionCheckJob = New-SQLAgentJob -SqlServer $SqlServer `
                -Name $AlterFunctionJobName `
                -Description "Daily audit to make sure no modifications to functions have been made." `
                -EmailLevel Always `
                -EventLogLevel OnFailure `
                -NetSendLevel Never `
                -PageLevel Never `
                -DeleteLevel Never `
                -OperatorToEmail $OperatorToEmail `
                -JobCategory $JobCategory 

            Write-Verbose -Message "Creating function audit job step."
            [Microsoft.SqlServer.Management.Smo.Agent.JobStep]$FunctionCheckJobStep = New-SQLAgentJobStep -SqlServer $SqlServer `
                -Name $AlterFunctionJobName `
                -Job $FunctionCheckJob `
                -DatabaseName "master" `
                -OnFailAction QuitWithFailure `
                -OnSuccessAction QuitWithSuccess `
                -RetryAttempts 0 `
                -RetryInterval 0 `
                -OSRunPriority 0 `
                -SubSystem TransactSql `
                -Command $FunctionAuditCommandText

            Write-Verbose -Message "Creating function audit job schedule."
            [Microsoft.SqlServer.Management.Smo.Agent.JobSchedule]$FunctionCheckSchedule = New-SQLAgentJobSchedule -SqlServer $SqlServer `
                -Name "Daily" `
                -Job $FunctionCheckJob `
                -ActiveStartTimeOfDay $JobTime `
                -FrequencyType Daily `
                -FrequencyInterval 1 
        }
        else {
            Write-Verbose -Message "$AlterFunctionJobName job already exists."
        }

        #endregion

        #region STIG ID: SQL4-00-015100
        Write-Host "Creating Trigger audit job"
        Write-Verbose -Message "STIG ID: SQL4-00-015100"

        if (($SqlServer.JobServer.Jobs | Where-Object {$_.Name -ieq $AlterTriggerJobName }) -eq $null) {
            Write-Verbose -Message "Creating trigger audit command text."
            $TriggerAuditCommandText = Get-SQLInstanceAuditCommandText -DaysDiff 1 `
                                            -JobName "Alter_Trigger_Audit" `
                                            -OperatorToEmail $OperatorToEmail `
                                            -EmailProfile $MailProfile `
                                            -EmailSubject " - *Alert* TRIGGER Modification Detected" `
                                            -ObjectToAudit Trigger `

            Write-Verbose -Message "Creating trigger audit job."
            [Microsoft.SqlServer.Management.Smo.Agent.Job]$TriggerCheckJob = New-SQLAgentJob -SqlServer $SqlServer `
                -Name "Alter_Trigger_Audit" `
                -Description "Daily audit to make sure no modifications to triggers have been made." `
                -EmailLevel Always `
                -EventLogLevel OnFailure `
                -NetSendLevel Never `
                -PageLevel Never `
                -DeleteLevel Never `
                -OperatorToEmail $OperatorToEmail `
                -JobCategory $JobCategory 
        
            Write-Verbose -Message "Creating trigger audit job step."
            [Microsoft.SqlServer.Management.Smo.Agent.JobStep]$TriggerCheckJobStep = New-SQLAgentJobStep -SqlServer $SqlServer `
                -Name $AlterTriggerJobName `
                -Job $TriggerCheckJob `
                -DatabaseName "master" `
                -OnFailAction QuitWithFailure `
                -OnSuccessAction QuitWithSuccess `
                -RetryAttempts 0 `
                -RetryInterval 0 `
                -OSRunPriority 0 `
                -SubSystem TransactSql `
                -Command $TriggerAuditCommandText

            Write-Verbose -Message "Creating trigger audit job schedule."
            [Microsoft.SqlServer.Management.Smo.Agent.JobSchedule]$TriggerCheckSchedule = New-SQLAgentJobSchedule -SqlServer $SqlServer `
                -Name "Daily" `
                -Job $TriggerCheckJob `
                -ActiveStartTimeOfDay $JobTime `
                -FrequencyType Daily `
                -FrequencyInterval 1 
        }
        else {
            Write-Verbose -Message "$AlterTriggerJobName job already exists."
        }

        #endregion

        #region STIG ID: SQL4-00-015200

        Write-Host "Creating XP audit job"
        Write-Verbose -Message "STIG ID: SQL4-00-015200"

        if (($SqlServer.JobServer.Jobs | Where-Object {$_.Name -ieq $AlterXPJobName}) -eq $null) {

            Write-Verbose -Message "Creating stored procedure audit command text."
            $XPAuditCommandText = Get-SQLInstanceAuditCommandText -DaysDiff 1 `
                                            -JobName "Alter_XP_Audit" `
                                            -OperatorToEmail $OperatorToEmail `
                                            -EmailProfile $MailProfile `
                                            -EmailSubject " - *Alert* STORED PROCEDURE Modification Detected" `
                                            -ObjectToAudit Trigger `

            Write-Verbose -Message "Creating stored procedure audit job."
            [Microsoft.SqlServer.Management.Smo.Agent.Job]$XPCheckJob = New-SQLAgentJob -SqlServer $SqlServer `
                -Name "Alter_XP_Audit" `
                -Description "Daily audit to make sure no modifications to stored procedures have been made." `
                -EmailLevel Always `
                -EventLogLevel OnFailure `
                -NetSendLevel Never `
                -PageLevel Never `
                -DeleteLevel Never `
                -OperatorToEmail $OperatorToEmail `
                -JobCategory $JobCategory 
        
            Write-Verbose -Message "Creating stored procedure audit job step."
            [Microsoft.SqlServer.Management.Smo.Agent.JobStep]$XPCheckJobStep = New-SQLAgentJobStep -SqlServer $SqlServer `
                -Name $AlterXPJobName `
                -Job $XPCheckJob `
                -DatabaseName "master" `
                -OnFailAction QuitWithFailure `
                -OnSuccessAction QuitWithSuccess `
                -RetryAttempts 0 `
                -RetryInterval 0 `
                -OSRunPriority 0 `
                -SubSystem TransactSql `
                -Command $XPAuditCommandText

            Write-Verbose -Message "Creating stored procedure audit job schedule."
            [Microsoft.SqlServer.Management.Smo.Agent.JobSchedule]$XPCheckSchedule = New-SQLAgentJobSchedule -SqlServer $SqlServer `
                -Name "Daily" `
                -Job $XPCheckJob `
                -ActiveStartTimeOfDay $JobTime `
                -FrequencyType Daily `
                -FrequencyInterval 1 
        }
        else {
            Write-Verbose -Message "$AlterXPJobName job already exists."
        }

        #endregion

        #region STIG ID: SQL4-00-015610, SQL4-00-015620

        Write-Host "Performing database trustworthy check"
        Write-Verbose -Message "STIG ID: SQL4-00-015610, SQL4-00-015620"

        foreach ($Database in ($DBs | Where-Object {$_.Name -ne "msdb"})) {
            [Microsoft.SqlServer.Management.Smo.ServerRole[]]$Roles = Get-SQLInstanceServerRoleMembership -SqlServer $SqlConnection -PrincipalName $Database.Owner -OnlyFixedRoles

            if ($Database.Trustworthy -eq $true -and $Roles.Count -gt 0) {
                Write-Verbose -Message "Database $($Database.Name) is set to Trustworthy and has an owner, $($Database.Owner), that is a member of a fixed role: $($Roles -join `",`")."
                Set-SQLDatabaseTrustworthy -SqlServer $SqlConnection -DatabaseName $Database.Name -Enabled $false
            }
        }

        #endregion

        if ($CreateAuditTrigger) {
            [Microsoft.SqlServer.Management.Smo.DatabaseDdlTriggerEventSet]$EventSet = New-Object -TypeName Microsoft.SqlServer.Management.Smo.DatabaseDdlTriggerEventSet
            $EventSet.CreateProcedure = $true
            $EventSet.AlterProcedure = $true
            $EventSet.DropProcedure = $true
            $EventSet.CreateFunction = $true
            $EventSet.AlterFunction = $true
            $EventSet.DropFunction = $true
            $EventSet.CreateTrigger = $true
            $EventSet.AlterTrigger = $true
            $EventSet.DropTrigger = $true

            foreach ($Database in $DBs) {
                $Command = Get-SQLDatabaseDdlTriggerCommandText -OperatorToEmail $OperatorToEmail -Subject " $($Database.Name) - *ALERT* Object Modification Detected" -EmailProfile $MailProfile
                $NewTrigger = New-SQLDatabaseDDLTrigger -SqlServer $SqlConnection -Name "AuditModificationsTrigger" -DatabaseName $Database.Name -CommandText $Command -EventSet $EventSet -ImplementationType TransactSql
            }
        }

        $SqlServer.ConnectionContext.Disconnect()
        Set-Location -Path $env:SystemDrive
        Write-Host "Completed STIG implementation." -ForegroundColor Green
    }

    End {        
    }
}

#endregion

Function Set-SQLInstanceForceEncryption {
    <#
        .SYNOPSIS
            Sets the force encryption registry key.
 
        .DESCRIPTION
            The cmdlet sets the force encryption registry key for the SQL Instance. If the Instance does not have a certificate configured and the cmdlet is enabling encryption, the setting does not get applied.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Enabled
            Specifies whether force encryption should be enabled or not. Defaults to true.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .INPUTS
            None
 
        .OUTPUTS
            None or System.Management.Automation.PSCustomObject
 
        .EXAMPLE
            Set-SQLInstanceForceEncryption -ComputerName "SQL2014" -InstanceName "MyInstance" -Enabled $true
 
            Sets the force encryption key to true.
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-018700
            Rule ID
                SQL4-00-018700_rule
            Vuln ID
                SQL4-00-018700
            Severity
                CAT I
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection", Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter()]
        [bool]$Enable = $true,

        [Parameter()] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter()]
        [switch]$PassThru 
    )

    Begin {
    }

    Process {
        Write-Verbose -Message "Setting Force Encryption."
        Write-Verbose -Message "STIG ID: SQL4-00-018700"

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        [System.String]$Instance = "MSSQLSERVER"

        #InstanceName will return an empty string for the default instance.

        if (-not [System.String]::IsNullOrEmpty($SqlConnection.InstanceName))
        {
            $Instance = $SqlConnection.InstanceName
        }

        [System.String]$Path = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL$($SqlConnection.VersionMajor)`.$Instance\MSSQLServer\SuperSocketNetLib"
        Write-Verbose -Message "Using registry path $Path"

        if ($SqlConnection.NetName -iin $script:LocalNames) {
            Write-Verbose -Message "Running command locally."
            
            if ($Enable -eq $true ) {
                if (![System.String]::IsNullOrEmpty((Get-ItemProperty -Path $Path -Name "Certificate" | Select-Object -ExpandProperty "Certificate"))) {
                    $Property = Set-ItemProperty -Path $Path -Name "ForceEncryption" -Value $Enable -PassThru:$PassThru
                }
                else {
                    Write-Verbose -Message "A certificate is not defined, will not force encryption."
                }
            }
            else {
                $Property = Set-ItemProperty -Path $Path -Name "ForceEncryption" -Value $Enable -PassThru:$PassThru
            }
        }
        else {
            Write-Verbose -Message "Running command remotely."
            $Session = New-PSSession -ComputerName $SqlConnection.NetName -Credential $Credential

            try {
                $Property = Invoke-Command -Session $Session -ScriptBlock { 
                    if ($args[2] -eq $true) {
                        if (![System.String]::IsNullOrEmpty((Get-ItemProperty -Path $args[0] -Name "Certificate" | Select-Object -ExpandProperty "Certificate"))) {
                            Write-Verbose -Message "Certificate is present."
                            return (Set-ItemProperty -Path $args[0] -Name "ForceEncryption" -Value $args[1] -PassThru:$args[2])
                        }
                        else {
                            throw "A certificate is not defined, will not force encryption."
                        }
                    }
                    else {
                        Write-Verbose -Message "Disabling forced encryption."
                        return (Set-ItemProperty -Path $args[0] -Name "ForceEncryption" -Value $args[1] -PassThru:$args[2])
                    }
                } -ArgumentList @($Path, $Enable, $PassThru) -Credential $Credential
            }
            catch [System.Exception] {
                Write-Verbose -Message $_.Exception.Message
            }
                
            Remove-PSSession -Session $Session
        }

        if ($PassThru) {
            Write-Output -InputObject $Property
        }
    }

    End {        
    }
}

#region Smo Functions

Function Set-SQLInstanceDatabaseFilePermissions {
    <#
        .SYNOPSIS
            Access to database files must be limited to relevant processes and to authorized, administrative users.
 
        .DESCRIPTION
            The Set-DatabaseFilePermissions cmdlet sets the required security permissions for the SQL database directories and files.
 
        .PARAMETER SQLAdministratorsName
            The user or group specified as the sysadmin for the SQL instance.
 
        PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .PARAMETER IncludeLocalAdministrators
            Specifies that the local administrators group of the server maintaining the database shared storage should receive full control permissions.
 
        .EXAMPLE
            Set-SQLInstanceDatabaseFilePermissions -SQLAdministratorsName "UG-SQL-Admins"
             
            Configures the required permissions for the database files and directories on the MSSQLSERVER instance on the localhost.
 
        .EXAMPLE
            Set-SQLInstanceDatabaseFilePermissions -SQLAdministratorsName "UG-SQL-Admins" -ComputerName "Server01" -InstanceName "MyInstance" -IncludeLocalAdministrators
 
            Configures the required permissions for the database files and directories on the MyInstance instance on Server01 and includes the local administrators group in those permissions.
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
 
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-031400
            Rule ID
                SQL4-00-031400_rule
            Vuln ID
                SQL4-00-031400
            Severity
                CAT II
    #>

    [CmdletBinding()]
    Param(
        [Parameter(Position=0,Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$SQLAdministratorsName,

        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter()]
        [switch]$IncludeLocalAdministrators,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin{
    }

    Process {
        Write-Verbose -Message "Setting Database File and Directory Permissions."
        Write-Verbose -Message "STIG ID: SQL4-00-031400"

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) 
        {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else 
        {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        Write-Verbose -Message "Getting service account SID."
        [System.Security.Principal.SecurityIdentifier]$ServiceAccountSid = Get-AccountSid -UserName $SqlConnection.ServiceAccount -ComputerName $SqlConnection.NetName -Credential $Credential

        Write-Verbose -Message "Getting SQL Agent account SID."
        if ([System.String]::IsNullOrEmpty($SqlConnection.JobServer.ServiceAccount)) 
        {
            Write-Verbose -Message "The SQL Agent service account returned null, getting the account through a query."
            $SqlAgentAccount = ($SqlConnection.Databases | Where-Object {$_.Name -eq "master"} | Select-Object -First 1).ExecuteWithResults("SELECT DSS.service_account FROM master.sys.dm_server_services AS DSS WHERE DSS.servicename LIKE 'SQL Server Agent%'").Tables[0] | Select-Object -ExpandProperty service_account
        }
        else
        {
            $SqlAgentAccount = $SqlConnection.JobServer.ServiceAccount
        }

        [System.Security.Principal.SecurityIdentifier]$SqlAgentAccountSid = Get-AccountSid -UserName $SqlAgentAccount -ComputerName $SqlConnection.NetName -Credential $Credential

        Write-Verbose -Message "Getting $SQLAdministratorsName SID."                
        [System.Security.Principal.SecurityIdentifier]$SQLAdministratorsSid = Get-AccountSid -UserName $SQLAdministratorsName -ComputerName $SqlConnection.NetName -Credential $Credential

        Write-Verbose -Message "Getting data directories."
        $DataDir = $SqlConnection.DefaultFile
        $LogDir = $SqlConnection.DefaultLog
        $BackupDir = $SqlConnection.BackupDirectory
        $RootDir = $SqlConnection.RootDirectory
        $TempDBDir = $SqlConnection.Databases | Where-Object {$_.Name -eq "tempdb"} | Select-Object -First 1 | Select-Object -ExpandProperty PrimaryFilePath

        $DataDirectories = @($DataDir, $LogDir, $BackupDir, $RootDir, $TempDBDir)
        $DataDirectories = $DataDirectories | Select-Object -Unique

        Write-Verbose -Message "Removing child directories from the list of directories."
        $FinalDirs = @()

        foreach ($Dir in $DataDirectories) 
        {
            $ShouldAdd = $true
            foreach ($OtherDir in ($DataDirectories | Where-Object {$_ -ne $Dir })) 
            {
                if ($Dir.StartsWith($OtherDir, [System.StringComparison]::OrdinalIgnoreCase)) 
                {
                    $ShouldAdd = $false
                    break
                }
            }
            if ($ShouldAdd) 
            {
                Write-Verbose -Message "Unique parent path $Dir."
                $FinalDirs += $Dir
            }
            else 
            {
                Write-Verbose -Message "Removing $Dir, it is a child of another data directory."
            }
        }

        #$DataDirectories = Get-SQLInstanceDataDirectories -InstanceName $InstanceName -ComputerName $ComputerName -EncryptConnection:$EncryptConnection -Credential $Credential
        
        Write-Verbose -Message "Creating access rules."
        $AccessRules = New-SQLInstanceDatabaseDirectoryAccessRuleSet -SqlServiceAccountSid $ServiceAccountSid -SqlAgentSid $SqlAgentAccountSid -AdministratorsSid $SQLAdministratorsSid -IncludeLocalAdministrators

        Write-Verbose -Message "Transforming directories."
        $ComputerName = $SqlConnection.NetName

        foreach ($Directory in $FinalDirs) 
        {
            if (!$Directory.StartsWith("\\") -and $ComputerName -notin $script:LocalNames) 
            {
                $Directory = $Directory.Replace(":","`$")
                $Directory = "\\$ComputerName\$Directory"
            }

            #This will just add the new rules, replace any explicit rules, and propagate them down to children
            Set-FileSecurity -Path $Directory -AccessRules $AccessRules -AddIfNotPresentAndReplace -EnableChildInheritance
        }

        <#Get-Member -InputObject $DataDirectories -MemberType Properties | ForEach-Object {
            $Path = $DataDirectories."$($_.Name)"
 
            if (!$Path.StartsWith("\\") -and $ComputerName -notin $script:LocalNames) {
                $Path = $Path.Replace(":","`$")
                $Path = "\\$ComputerName\$Path"
            }
             
            Set-FileSecurity -Path $Path -Rules $AccessRules -ReplaceAllRules -ForceChildInheritance
        }#>

        Write-Verbose -Message "Completed setting database folder and file permissions."
    }

    End {        
    }
}

Function Set-SQLInstanceInstallationFilesPermissionsAndAuditing {
    <#
        .SYNOPSIS
            Software, applications, and configuration files that are part of, or related to, the SQL Server installation must be monitored to discover unauthorized changes.
 
        .DESCRIPTION
            The Set-SQLInstanceInstallationFilesPermissionsAndAudting cmdlet sets the required security permissions for the SQL installation and auditing.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER InheritDefault
            Alternatively reset inherited permissions from the parent directory, this is typically sufficient if the installation directory was set under $env:ProgramFiles
 
        .PARAMETER Replace
            Replace all permissions of the installation directory, subfolders, and files instead of re-inheriting default permissions.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Set-SQLInstanceInstallationFilesPermissionsAndAuditing -ComputerName "SQL2014" -InstanceName "MyInstance"
             
            Configures the required permissions for the audit logs by forcing inheriting the default permissions.
 
        .EXAMPLE
            Set-SQLInstanceInstallationFilesPermissionsAndAuditing -ComputerName "SQL2014" -InstanceName "MyInstance" -Replace
             
            Configures the required permissions for the audit logs by replacing existing permissions with a new permission set.
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-015350
            Rule ID
                SQL4-00-015350_rule
            Vuln ID
                SQL4-00-015350
            Severity
                CAT I
    #>

    [CmdletBinding(DefaultParameterSetName = "ExistingConnectionWithInherit")]
    Param(
        [Parameter(ParameterSetName="NewConnectionWithReplace")]
        [Parameter(ParameterSetName="NewConnectionWithInherit")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnectionWithReplace")]
        [Parameter(ParameterSetName="NewConnectionWithInherit")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnectionWithReplace",Mandatory=$true)]
        [Parameter(ParameterSetName="ExistingConnectionWithInherit",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName="NewConnectionWithReplace", Mandatory=$true)]
        [Parameter(ParameterSetName="ExistingConnectionWithReplace",Mandatory=$true)]
        [switch]$Replace,

        [Parameter(ParameterSetName="NewConnectionWithInherit")]
        [Parameter(ParameterSetName="ExistingConnectionWithInherit")]
        [switch]$InheritDefault,

        [Parameter(ParameterSetName="NewConnectionWithInherit")]
        [Parameter(ParameterSetName="NewConnectionWithReplace")]
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process
    {
        Write-Verbose -Message "Setting Installation Directory Permissions and Auditing."
        Write-Verbose -Message "STIG ID: SQL4-00-015350" 
              
        if ($PSCmdlet.ParameterSetName -like "$EXISTING_CONNECTION*") {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        #$Path = (Get-SQLInstanceDetails -ComputerName $ComputerName -InstanceName $InstanceName -EncryptConnection:$EncryptConnection -Credential $Credential).InstallationPath

        Write-Verbose -Message "Getting installation data directory."
        $InstallDataDirectory = [System.IO.Directory]::GetParent($SqlConnection.InstallDataDirectory) | Select-Object -ExpandProperty FullName

        Write-Verbose -Message "Getting installation shared directory."
        $InstallSharedDirectory = [System.IO.Directory]::GetParent($SqlConnection.InstallSharedDirectory) | Select-Object -ExpandProperty FullName

        $Paths = @($InstallDataDirectory, $InstallSharedDirectory) | Select-Object -Unique

        $ComputerName = $SqlConnection.NetName

        foreach ($Path in $Paths)
        {
            #Modify the path if the storage is remote, use a UNC path
            if (!$Path.StartsWith("\\") -and $ComputerName -notin $script:LocalNames) 
            {
                Write-Verbose -Message "Transforming the retrieved directory, $Path."
                $Path = $Path.Replace(":","`$")
                $Path = "\\$ComputerName\$Path"
                Write-Verbose -Message "The transformed path is $Path."
            }
        }

        <#
            The path is probably something like C:\Program Files\Microsoft SQL Server\MSSQL##.InstanceName
            This receives inherited permissions from the Program Files directory, but we'll explicitly set them
        #>

        Write-Verbose -Message "Creating audit rules for Everyone Modify Success/Failure on the installation directory."
        [System.Security.AccessControl.FileSystemAuditRule[]]$AuditRules = New-SQLInstanceInstallationDirectoryAuditRuleSet

        Write-Verbose -Message "Creating default access rules for the installation directory"
        [System.Security.AccessControl.FileSystemAccessRule[]]$AccessRules = New-SQLInstanceInstallationDirectoryAccessRuleSet

        switch -wildcard ($PSCmdlet.ParameterSetName) {
            "*Replace" {
                Write-Verbose -Message "Replacing current file permissions on $Path and forcing inheritance."
                Set-FileSecurity -Path $Path -AuditRules $AuditRules -AccessRules $AccessRules -ReplaceAllRules -ForceChildInheritance
                break
            }
            "*Inherit" {
                Write-Verbose -Message "Ensuring standard inherited permissions are present on $Path."
                Set-FileSecurity -Path $Path -AuditRules $AuditRules -AccessRules $AccessRules -AddIfNotPresent -ForceChildInheritance
                break
            }
            default {
                throw "Could not determine parameter set name."
            }
        }
        

        Write-Verbose -Message "Completed setting installation directory permissions."
    }
    
    End {    
    }
}

Function Set-SQLInstanceAuditFilePermissions {
    <#
        .SYNOPSIS
            The audit information produced by SQL Server must be protected from unauthorized read access.
 
        .DESCRIPTION
            The Set-SQLInstanceAuditFilePermissions cmdlet sets the required security permissions for the SQL audit log files.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER AuditorAdministratorsName
            The name of the user or group responsible for the audit administrator role.
 
        .PARAMETER AuditorsName
            The name of the user or group responsible for auditing the database, primarily accessing audit log files.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Set-SQLInstanceAuditFilePermissions -AuditAdministratorsName "UG-SQL-Audit-Admins" -AuditorsName "UG-SQL-Auditors"
             
            Configures the required permissions for the audit logs on the localhost for the default MSSQLSERVER instance.
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
 
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-013600
                SQL4-00-013700
                SQL4-00-013800
            Rule ID
                SQL4-00-013600_rule
                SQL4-00-013700_rule
                SQL4-00-013800_rule
            Vuln ID
                SQL4-00-013600
                SQL4-00-013700
                SQL4-00-013800
            Severity
                CAT II
                CAT III
                CAT III
    #>

    [CmdletBinding()]
    Param(
        [Parameter(Position=0,Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$AuditorAdministratorsName,

        [Parameter(Position=1, Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$AuditorsName,

        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin{
    }

    Process
    {
        Write-Verbose -Message "Setting Audit File Permissions."
        Write-Verbose -Message "STIG IDs: SQL4-00-013600, SQL4-00-013700, SQL4-00-013800"  

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }
        
        <# These values can also be retrieved from the registry
        if ($InstanceName -ieq "mssqlserver") {
            $InstanceServiceName = $InstanceName
            $SqlAgentService = "SQLSERVERAGENT"
        }
        else {
            $InstanceServiceName = "MSSQL`$$InstanceName"
            $SqlAgentService = "SQLAgent`$$InstanceName"
        }
 
        if ($ComputerName -iin $script:LocalNames) {
            $SqlServiceAccount = Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\$InstanceServiceName" -Name "ObjectName" | Select-Object -ExpandProperty ObjectName
            $SqlAgentServiceAccount = Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\$SqlAgentService" -Name "ObjectName" | Select-Object -ExpandProperty ObjectName
        }
        else {
            $Session = New-PSSession -ComputerName $ComputerName -Credential $Credential
            $SqlServiceAccount = Invoke-Command -Session $Session -ScriptBlock { Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\$($args[0])" -Name "ObjectName" | Select-Object -ExpandProperty ObjectName } -ArgumentList @($InstanceServiceName)
            $SqlAgentServiceAccount = Invoke-Command -ComputerName $ComputerName -ScriptBlock { Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\$($args[0])" -Name "ObjectName" | Select-Object -ExpandProperty ObjectName } -ArgumentList @($SqlAgentService)
            Remove-PSSession -Session $Session
        }
 
        $ServiceAccountSid = Get-AccountSid -UserName $SqlServiceAccount -ComputerName $ComputerName -Credential $Credential
        $SqlAgentAccountSid = Get-AccountSid -UserName $SqlAgentServiceAccount -ComputerName $ComputerName -Credential $Credential
        #>

        Write-Verbose -Message "Getting service account SID."
        [System.Security.Principal.SecurityIdentifier]$ServiceAccountSid = Get-AccountSid -UserName $SqlConnection.ServiceAccount -ComputerName $SqlConnection.NetName -Credential $Credential

        Write-Verbose -Message "Getting SQL Agent account SID."
        if ([System.String]::IsNullOrEmpty($SqlConnection.JobServer.ServiceAccount)) {
            Write-Verbose -Message "The SQL Agent service account returned null, getting the account through a query."
            $SqlAgentAccount = ($SqlConnection.Databases | Where-Object {$_.Name -eq "master"} | Select-Object -First 1).ExecuteWithResults("SELECT service_account FROM master.sys.dm_server_services svc JOIN master.sys.server_principals prin ON svc.service_account = prin.name WHERE svc.servicename LIKE 'SQL Server Agent%'").Tables[0] | Select-Object -ExpandProperty service_account
        }
        else
        {
            $SqlAgentAccount = $SqlConnection.JobServer.ServiceAccount
        }

        [System.Security.Principal.SecurityIdentifier]$SqlAgentAccountSid = Get-AccountSid -UserName $SqlAgentAccount -ComputerName $SqlConnection.NetName -Credential $Credential

        Write-Verbose -Message "Getting audit administrators SID."
        $AuditAdministratorsSid = Get-AccountSid -UserName $AuditorAdministratorsName -ComputerName $SqlConnection.NetName -Credential $Credential

        Write-Verbose -Message "Getting auditors SID."
        $AuditorsSid = Get-AccountSid -UserName $AuditorsName -ComputerName $SqlConnection.NetName -Credential $Credential

        Write-Verbose -Message "Building access rules."
        $AccessRules = New-SQLInstanceAuditLogAccessRuleSet -SqlServiceAccountSid $ServiceAccountSid -SqlAgentSid $SqlAgentAccountSid -AuditAdministratorSid $AuditAdministratorsSid -AuditorsSid $AuditorsSid

        Write-Verbose -Message "Getting error log path."
        $Directory = Get-SQLInstanceErrorLogPath -SqlServer $SqlConnection

        Write-Verbose -Message "Transforming error log path."
        $ComputerName = $SqlConnection.NetName
        if (!$Directory.StartsWith("\\") -and $ComputerName -inotin $script:LocalNames) {
            $Directory = $Directory.Replace(":\","`$\")
            $Directory = "\\$ComputerName\$Directory"
        }
        
        Write-Verbose -Message "Setting updated permissions on SQL logging directory, $Directory."
        Set-FileSecurity -Path $Directory -Rules $AccessRules -AddIfNotPresentAndReplace -ForceChildInheritance
        Write-Verbose -Message "Completed setting audit file permissions."
    }

    End{        
    }
}

Function Set-SQLInstanceAuditors {
    <#
        .SYNOPSIS
            SQL Server must allow only the ISSM (or individuals or roles appointed by the ISSM) to select which auditable events are to be audited or traced.
 
        .DESCRIPTION
            The Set-SQLInstanceAuditors cmdlet creates logins for specified auditor Windows Groups, creates 2 roles, one for Server level auditing, and one for Database level auditing, grants them permissions
            and adds the new logins to the respective groups.
 
            Users or roles with existing ALTER ANY SERVER AUDIT or ALTER ANY DATABASE AUDIT permissions at the server or database level will have those privileges removed.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER ServerAuditorsRole
            The name of the role to be created for managing server level audits. Will be granted ALTER ANY SERVER AUDIT. Defaults to SERVER_AUDIT_MAINTAINERS.
 
        .PARAMETER DatabaseAuditorsRole
            The name of the role to be created for managing database level audits. Will be granted ALTER ANY DATABASE AUDIT on every existing database. Defaults to DATABASE_AUDIT_MAINTAINERS.
 
        .PARAMETER ServerAuditors
            The Windows user or Group that will have a login created and added to the new Server Auditors Role.
 
        .PARMETER DatabaseAuditors
            The Windows user or Group that will have a login created, then have a Database User created on every existing database, and added to the new Database Auditors Role.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Set-SQLInstanceAuditors -ComputerName "SQL2014" -InstanceName "MyInstance" -ServerAuditors "Contoso\UG-SQL-Server-Auditors" -DatabaseAuditors "Contoso\UG-SQL-Database-Auditors"
             
            Creates logins for Contoso\UG-SQL-Server-Auditors and Contoso\UG-SQL-Database-Auditors. Creates the SERVER_AUDIT_MAINTAINERS and DATABASE_AUDIT_MAINTAINERS roles.
            Grants the required permissions to those roles. Adds a database user on each database for Contoso\UG-SQL-Database-Auditors. Remove existing privileges from other accounts and roles.
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-011300
            Rule ID
                SQL4-00-011300_rule
            Vuln ID
                SQL4-00-011300
            Severity
                CAT II
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$ServerAuditorsRole = "SERVER_AUDIT_MAINTAINERS",

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$DatabaseAuditorsRole = "DATABASE_AUDIT_MAINTAINERS",

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$ServerAuditors,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$DatabaseAuditors,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        Write-Verbose -Message "Setting SQL Instance and Database Auditors."
        Write-Verbose -Message "STIG ID: SQL4-00-015350"

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        [System.String[]]$Roles = $SqlConnection.Roles | Where-Object {$_.IsFixedRole -eq $false } | Select-Object -ExpandProperty Name
        [System.String[]]$Logins = $SqlConnection.Logins | Where-NotMatchIn -Matches $script:IgnoreNames -Property "Name" | Select-Object -ExpandProperty Name

        [System.String]$ServerAuditorsTranslated = Get-AccountTranslatedNTName -UserName $ServerAuditors -ComputerName $SqlConnection.NetName -Credential $Credential

        if ([System.String]::IsNullOrEmpty($ServerAuditorsTranslated))
        {
            Write-Verbose -Message "Could not translate server auditor $ServerAuditors"
        }
        else
        {
            $ServerAuditors = $ServerAuditorsTranslated
        }

        Write-Verbose -Message "Server auditor is $ServerAuditors."

        [System.String]$DatabaseAuditorsTranslated = Get-AccountTranslatedNTName -UserName $DatabaseAuditors -ComputerName $SqlConnection.NetName -Credential $Credential

        if ([System.String]::IsNullOrEmpty($DatabaseAuditorsTranslated))
        {
            Write-Verbose -Message "Could not translate database auditor $DatabaseAuditors"
        }
        else
        {
            $DatabaseAuditors = $DatabaseAuditorsTranslated
        }

        Write-Verbose -Message "Database auditor is $DatabaseAuditors."

        [System.String[]]$NewLogins = @($ServerAuditors, $DatabaseAuditors)
        [System.String[]]$NewRoles = @($ServerAuditorsRole, $DatabaseAuditorsRole)

        foreach ($Login in $NewLogins) {
            Add-SqlInstanceLogin -SqlServer $SqlConnection -Name $Login
        }

        Add-SQLInstanceServerRole -SqlServer $SqlConnection -Name $ServerAuditorsRole

        $Principals = @()
        $Principals += $Logins
        $Principals += $Roles

        foreach ($Principal in $Principals) {
            Write-Verbose -Message "Reviewing ALTER ANY SERVER AUDIT permissions for $Principal."
            [Microsoft.SqlServer.Management.Smo.ServerPermissionInfo[]]$Permissions = $SqlConnection.EnumServerPermissions($Principal)
    
            #PermissionType is a ServerPermissionSet object. The ServerPermissionSet object has properties that
            #correspond to the different permissions
            foreach ($ServerPermissionSet in $Permissions | Select-Object -ExpandProperty PermissionType) {
                if ($ServerPermissionSet.AlterAnyServerAudit -eq $true) {
                    Write-Verbose -Message "Revoking ALTER ANY SERVER AUDIT from $Principal."
                    $SqlConnection.Revoke([Microsoft.SqlServer.Management.Smo.ServerPermission]::AlterAnyServerAudit, $Principal)
                }
            }
        }

        Write-Verbose -Message "Granting ALTER ANY SERVER AUDIT to $ServerAuditorsRole."
        $SqlConnection.Grant([Microsoft.SqlServer.Management.Smo.ServerPermission]::AlterAnyServerAudit, $ServerAuditorsRole)

        Write-Verbose -Message "Adding $ServerAuditors to $ServerAuditorsRole."
        [Microsoft.SqlServer.Management.Smo.ServerRole]$SrvAuditorRole = $SqlConnection.Roles | Where-Object {$_.Name -eq $ServerAuditorsRole} | Select-Object -First 1
        $SrvAuditorRole.AddMember($ServerAuditors)

        foreach ($Database in $SqlConnection.Databases) {
            $DatabaseRoles = $Database.Roles | Where-Object { $_.IsFixedRole -eq $false } | Select-Object -ExpandProperty Name 
            $DatabaseUsers = $Database.Users | Where-NotMatchIn -Matches $script:IgnoreNames -Property "Name" | Select-Object -ExpandProperty Name

            [System.String[]]$Principals = @()
            $Principals += $DatabaseRoles
            $Principals += $DatabaseUsers

            #Review all of the instance principals that are not the new roles we've just granted new permissions
            foreach ($Principal in ($Principals | Where-Object {$_ -inotin @($ServerAuditorsRole, $DatabaseAuditorsRole)} )) {

                [Microsoft.SqlServer.Management.Smo.DatabasePermissionInfo[]]$DatabasePermissions = $Database.EnumDatabasePermissions($Principal) 

                foreach ($DatabasePermissionSet in $DatabasePermissions | Select-Object -ExpandProperty PermissionType) {
                    if ($DatabasePermissionSet.AlterAnyDatabaseAudit -eq $true) {
                        Write-Verbose -Message "Revoking ALTER ANY DATABASE AUDIT from $Principal."
                        $Database.Revoke([Microsoft.SqlServer.Management.Smo.DatabasePermission]::AlterAnyDatabaseAudit, $Principal)
                    }
                }
            }

            if ($DatabaseUsers -inotcontains $DatabaseAuditors) {
                Write-Verbose -Message "Creating database user for $DatabaseAuditors in $Database."
                [Microsoft.SqlServer.Management.Smo.User]$NewDBAuditorUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User($Database, $DatabaseAuditors)
                
                try {
                    $NewDBAuditorUser.Create()
                }
                catch [Exception] {
                    if ($_.Exception.InnerException -ne $null) {
                        $Exception = $_.Exception
                        while ($Exception.InnerException -ne $null) {                      
                            $Exception = $Exception.InnerException
                        }

                        Write-Error -Exception $Exception
                    }
                    else {
                        Write-Error -Exception $_.Exception
                    }
                }
            }
            else {
                Write-Verbose -Message "Database $($Database.Name) already contains a user for $DatabaseAuditors."
            }
            
            if ($DatabaseRoles -inotcontains $DatabaseAuditorsRole) {
                Write-Verbose -Message "Creating role $DatabaseAuditorsRole in database $($Database.Name)."
                [Microsoft.SqlServer.Management.Smo.DatabaseRole]$NewRole = New-Object Microsoft.SqlServer.Management.Smo.DatabaseRole($Database, $DatabaseAuditorsRole)
                
                try {
                    $NewRole.Create()
                }
                catch [Exception] {
                    if ($_.Exception.InnerException -ne $null) {
                        $Exception = $_.Exception
                        while ($Exception.InnerException -ne $null) {                      
                            $Exception = $Exception.InnerException
                        }

                        Write-Error -Exception $Exception
                    }
                    else {
                        Write-Error -Exception $_.Exception
                    }
                }
            }
            else {
                Write-Verbose -Message "Database $($Database.Name) already contains the role $DatabaseAuditorsRole."
            }

            Write-Verbose -Message "Granting ALTER ANY DATABASE AUDIT to $DatabaseAuditorsRole on $($Database.Name)."
            $Database.Grant([Microsoft.SqlServer.Management.Smo.DatabasePermission]::AlterAnyDatabaseAudit, $DatabaseAuditorsRole)

            Write-Verbose -Message "Adding $DatabaseAuditors to $DatabaseAuditorsRole in database $($Database.Name)."
            [Microsoft.SqlServer.Management.Smo.DatabaseRole]$DbAuditorsRole = $Database.Roles | Where-Object {$_.Name -eq $DatabaseAuditorsRole} | Select-Object -First 1
            $DbAuditorsRole.AddMember($DatabaseAuditors)
        }
    }

    End {
    }
}

Function Set-SQLInstanceAuditing {
    <#
        .SYNOPSIS
            SQL Server must generate trace or audit records on numerous actions.
 
        .DESCRIPTION
            The Set-SQLInstanceAuditing cmdlet creates an audit and an audit specification to audit all required events. It can optionally be set to include database level auditing, in which case it will also
            audit all SELECT, INSERT, UPDATE, DROP, and EXECUTE commands on every database. Use caution with this option as it will fill up logs quickly and is not required for all data per the STIG.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER AuditName
            The name of the audit to create. This defaults to STIG_Audit
 
        .PARAMETER AuditSpecificationName
            The name of the audit specification to create. This defaults to STIG_Audit_Specification.
 
        .PARAMETER AuditDestination
            The audit events can either be written to the Application Log, Security Log, or a Log File. Specify ApplicationLog, SecurityLog, or File. If the File option is selected, the FilePath parameter
            can also be used. The ApplicationLog is the default destination.
 
            Special considerations should be used when writing to the Security Log, see https://msdn.microsoft.com/en-us/library/cc645889.aspx for more details.
 
        .PARAMETER FilePath
            If the audit events are written to a file, you can specify the file they are written to. This defaults to "$env:SYSTEMROOT\Program Files\Microsoft SQL Server\Logs\AuditLog.trc".
 
            If ApplicationLog or SecurityLog are selected, this parameter is ignored.
 
        .PARMETER IncludeDatabaseLevelAudting
            This parameter specifies that all INSERT, UPDATE, SELECT, DELETE, and EXECUTE actions on every database except the master are audited. Use with caution since this will fill up the logs quickly.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Set-SQLInstanceAuditing -ComputerName "SQL2014" -InstanceName "MyInstance"
             
            Configures the auditing on the SQL Instance and writes the audits to the Application Log.
 
        .INPUTS
            None
 
        .OUTPUTS
            None or Microsoft.SqlServer.Management.Smo.Audit
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-030400
                SQL4-00-011900
                SQL4-00-012000
                SQL4-00-012100
                SQL4-00-012200
                SQL4-00-012300
                SQL4-00-012400
                SQL4-00-030600
                SQL4-00-034000
                SQL4-00-035600
                SQL4-00-037500
                SQL4-00-037600
                SQL4-00-037700
                SQL4-00-037800
                SQL4-00-037900
                SQL4-00-038000
            Rule ID
                SQL4-00-030400_rule
                SQL4-00-011900_rule
                SQL4-00-012000_rule
                SQL4-00-012100_rule
                SQL4-00-012200_rule
                SQL4-00-012300_rule
                SQL4-00-012400_rule
                SQL4-00-030600_rule
                SQL4-00-034000_rule
                SQL4-00-035600_rule
                SQL4-00-037500_rule
                SQL4-00-037600_rule
                SQL4-00-037700_rule
                SQL4-00-037800_rule
                SQL4-00-037900_rule
                SQL4-00-038000_rule
            Vuln ID
                SQL4-00-030400
                SQL4-00-011900
                SQL4-00-012000
                SQL4-00-012100
                SQL4-00-012200
                SQL4-00-012300
                SQL4-00-012400
                SQL4-00-030600
                SQL4-00-034000
                SQL4-00-035600
                SQL4-00-037500
                SQL4-00-037600
                SQL4-00-037700
                SQL4-00-037800
                SQL4-00-037900
                SQL4-00-038000
            Severity
                CAT II
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$AuditName = "STIG_Audit",

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$AuditSpecificationName = "STIG_Audit_Specification",

        [Parameter()]
        [ValidateSet("ApplicationLog", "SecurityLog", "File")]
        [System.String]$AuditDestination = "ApplicationLog",

        [Parameter()]
        [ValidateScript({ if ($AuditDestination -eq "File") { return ![System.String]::IsNullOrEmpty($_) } else {return $true} })]
        [System.String]$FilePath = "$env:SYSTEMROOT\Program Files\Microsoft SQL Server\Logs\AuditLog.trc",

        [Parameter()]
        [switch]$IncludeDatabaseLevelAudting,

        [Parameter()]
        [switch]$PassThru,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin{
    }

    Process {
        Write-Verbose -Message "Setting SQL Instance Auditing."
        Write-Verbose -Message "STIG ID: SQL4-00-030400, SQL4-00-011900, SQL4-00-012000, SQL4-00-012100, SQL4-00-012200, SQL4-00-012300, SQL4-00-012400, SQL4-00-030600, SQL4-00-034000, SQL4-00-035600, SQL4-00-037500, SQL4-00-037600, SQL4-00-037700, SQL4-00-037800, SQL4-00-037900, SQL4-00-038000"

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        if (($SqlConnection.Audits | Where-Object {$_.Name -ieq $AuditName}) -eq $null) {
            Write-Verbose -Message "Creating audit $AuditName."

            [Microsoft.SqlServer.Management.Smo.Audit]$ServerAudit = New-Object Microsoft.SqlServer.Management.Smo.Audit($SqlConnection, $AuditName)
            $ServerAudit.OnFailure = [Microsoft.SqlServer.Management.Smo.OnFailureAction]::Continue
            switch ($AuditDestination) {
                "ApplicationLog" {
                    $ServerAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::ApplicationLog
                    break
                }
                "SecurityLog" {
                    $ServerAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::SecurityLog
                    break
                }
                "File" {
                    $ServerAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
                    $ServerAudit.FilePath = $FilePath
                    break
                }
            }
            
            try {
                $ServerAudit.Create()
                $ServerAudit.Enable()
            }
            catch [Exception] {
                if ($_.Exception.InnerException -ne $null) {
                    $Exception = $_.Exception
                    while ($Exception.InnerException -ne $null) {                      
                        $Exception = $Exception.InnerException
                    }

                    Write-Error -Exception $Exception
                }
                else {
                    Write-Error -Exception $_.Exception
                }
            }
        }
        else {
            Write-Verbose -Message "Server already contains an audit named $AuditName."
            [Microsoft.SqlServer.Management.Smo.Audit]$ServerAudit = $SqlConnection.Audits | Where-Object {$_.Name -ieq $AuditName} | Select-Object -First 1
            $ServerAudit.Enable()
        }

        if (($SqlConnection.ServerAuditSpecifications | Where-Object {$_.Name -ieq $AuditSpecificationName}) -eq $null) {
            Write-Verbose -Message "Creating audit specification $AuditSpecificationName."
            [Microsoft.SqlServer.Management.Smo.ServerAuditSpecification]$ServerAuditSpecification = New-Object -TypeName Microsoft.SqlServer.Management.Smo.ServerAuditSpecification($SqlConnection, $AuditSpecificationName)
            $ServerAuditSpecification.AuditName = $ServerAudit.Name

            try {
                $ServerAuditSpecification.Create()  
            }
            catch [Exception] {
                if ($_.Exception.InnerException -ne $null) {
                    $Exception = $_.Exception
                    while ($Exception.InnerException -ne $null) {                      
                        $Exception = $Exception.InnerException
                    }

                    Write-Error -Exception $Exception
                }
                else {
                    Write-Error -Exception $_.Exception
                }
            }
        }
        else {
            Write-Verbose -Message "Server already contains an audit specification named $AuditSpecificationName. Disabling the specification to modify it."
            [Microsoft.SqlServer.Management.Smo.ServerAuditSpecification]$ServerAuditSpecification = $SqlConnection.ServerAuditSpecifications |  Where-Object {$_.Name -ieq $AuditSpecificationName} | Select-Object -First 1
            $ServerAuditSpecification.Disable()
        }

        foreach ($Action in ([Enum]::GetNames([Microsoft.SqlServer.Management.Smo.AuditActionType]) | Where-Object {$_ -like "*Group"})) {
            [Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail]$SpecificationDetail = New-Object -TypeName Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail($Action)
            $ServerAuditSpecification.AddAuditSpecificationDetail($SpecificationDetail)
        }

        if ($IncludeDatabaseLevelAudting) {
            foreach ($Database in ($SqlConnection.Databases | Where-Object {$_.Name -ine "master" })) {
                Set-SQLDatabaseAuditing -Database $Database -SqlServer $SqlConnection -AuditName $AuditName -DatabaseAuditSpecificationName $AuditSpecificationName -Audits $script:DatabaseAudits
            }
        }
        
        $ServerAuditSpecification.Enable()  

        if ($PassThru) {
            Write-Output -InputObject $ServerAudit
        }
    }

    End {
    }
}

Function Set-SQLDatabaseAuditing {
    <#
        .SYNOPSIS
            SQL Server must generate trace or audit records on numerous actions.
 
        .DESCRIPTION
            The Set-SQLDatabaseAuditing cmdlet creates a database audit specification to audit all selected events. This defaults to audit all SELECT, INSERT, UPDATE, DROP, and EXECUTE commands
     
            Use caution with this option as it will fill up logs quickly and is not required for all data per the STIG.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Database
            An existing database of the type Microsoft.SqlServer.Management.Smo.Database to create the auditing on.
 
        .PARAMETER AuditName
            The name of the audit to use, this should be an existing audit that the new Audit Specification is added to.
 
        .PARAMETER DatabaseAuditSpecificationName
            The name of the database audit specification to create.
 
        .PARAMETER Audits
            The specific audits to include, choose any combination of SELECT, INSERT, UPDATE, DROP, and EXECUTE. This defaults to all.
 
        .PARAMETER Principal
            The principal to audit taking these actions. This parameter defaults to public, which audits actions from all users.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Set-SQLInstanceAuditing -ComputerName "SQL2014" -InstanceName "MyInstance"
             
            Configures the auditing on the SQL Instance and writes the audits to the Application Log.
 
        .INPUTS
            None
 
        .OUTPUTS
            None or Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Database V1R0
            STIG ID
                SQL4-00-011200
                SQL4-00-022900
                SQL4-00-035800
                SQL4-00-035900
                SQL4-00-036200
                SQL4-00-036300
                SQL4-00-036400
                SQL4-00-036500
                SQL4-00-036600
                SQL4-00-036800
                SQL4-00-036650
                SQL4-00-036850
                SQL4-00-037100
                SQL4-00-037200
                SQL4-00-037300
                SQL4-00-037400
                SQL4-00-038100
                SQL4-00-038200
            RULE ID
                SQL4-00-011200_rule
                SQL4-00-022900_rule
                SQL4-00-035800_rule
                SQL4-00-035900_rule
                SQL4-00-036200_rule
                SQL4-00-036300_rule
                SQL4-00-036400_rule
                SQL4-00-036500_rule
                SQL4-00-036600_rule
                SQL4-00-036800_rule
                SQL4-00-036650_rule
                SQL4-00-036850_rule
                SQL4-00-037100_rule
                SQL4-00-037200_rule
                SQL4-00-037300_rule
                SQL4-00-037400_rule
                SQL4-00-038100_rule
                SQL4-00-038200_rule
            VULN ID
                SQL4-00-011200
                SQL4-00-022900
                SQL4-00-035800
                SQL4-00-035900
                SQL4-00-036200
                SQL4-00-036300
                SQL4-00-036400
                SQL4-00-036500
                SQL4-00-036600
                SQL4-00-036800
                SQL4-00-036650
                SQL4-00-036850
                SQL4-00-037100
                SQL4-00-037200
                SQL4-00-037300
                SQL4-00-037400
                SQL4-00-038100
                SQL4-00-038200
            Severity
                CAT II
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        $Database,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$AuditName,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$DatabaseAuditSpecificationName,

        [Parameter()]
        [ValidateSet("SELECT", "INSERT", "UPDATE", "DELETE", "EXECUTE", IgnoreCase = $true)]
        [ValidateScript({$_.Count -ge 1})]
        [System.String[]]$Audits = $script:DatabaseAudits,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$Principal = "public",

        [Parameter()]
        [switch]$PassThru,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        if ($Database.GetType() -ne [Microsoft.SqlServer.Management.Smo.Database] -and $Database.GetType() -ne [System.String]) {
            throw "Database parameter must be of type Microsoft.SqlServer.Management.Smo.Database or System.String."
        }

        Write-Verbose -Message "Setting Database Level Auditing."
        Write-Verbose -Message "STIG ID: SQL4-00-011200, SQL4-00-022900, SQL4-00-035800, SQL4-00-035900, SQL4-00-036200, SQL4-00-036300, SQL4-00-036400, SQL4-00-036500, SQL4-00-036600, SQL4-00-036800, SQL4-00-036650, SQL4-00-036850, SQL4-00-037100, SQL4-00-037200, SQL4-00-037300, SQL4-00-037400, SQL4-00-038100, SQL4-00-038200"

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        if ($Database.GetType().Equals([System.String])) {
            [Microsoft.SqlServer.Management.Smo.Database]$Database = $SqlConnection.Databases | Where-Object {$_.Name -eq $Database.Name } | Select-Object -First 1
        }

        if ($Database -ne $null) {

            if (($Database.DatabaseAuditSpecifications | Where-Object { $_.Name -ieq $DatabaseAuditSpecificationName }) -eq $null) {
                Write-Verbose -Message "Creating database audit specification $DatabaseAuditSpecificationName on $($Database.Name)."
                [Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification]$DatabaseAuditSpecification = New-Object -TypeName Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification($Database, $DatabaseAuditSpecificationName)
                $DatabaseAuditSpecification.AuditName = $AuditName
                
                try {
                    $DatabaseAuditSpecification.Create()
                    Write-Verbose -Message "Specification enabled $($DatabaseAuditSpecification.Enabled)"
                }
                catch [Exception] {
                    if ($_.Exception.InnerException -ne $null) {
                        $Exception = $_.Exception
                        while ($Exception.InnerException -ne $null) {                      
                            $Exception = $Exception.InnerException
                        }

                        Write-Error -Exception $Exception
                    }
                    else {
                        Write-Error -Exception $_.Exception
                    }
                }
            }
            else {
                Write-Verbose -Message "Database $($Database.Name) already contains a database audit specification named $DatabaseAuditSpecificationName. Disabling the specification to modify it."
                [Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification]$DatabaseAuditSpecification = $Database.DatabaseAuditSpecifications | Where-Object { $_.Name -ieq $DatabaseAuditSpecificationName} | Select-Object -First 1
                $DatabaseAuditSpecification.Disable()
            }
         
            foreach($Action in ([Enum]::GetNames([Microsoft.SqlServer.Management.Smo.AuditActionType]) | Where-Object {$_ -iin $Audits } )) {
                $ObjectClass = "Database"
                $ObjectSchema = [System.String]::Empty
                [Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail]$SpecificationDetail = New-Object -TypeName Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail($Action, $ObjectClass, $ObjectSchema, $Database.Name, $Principal)
                $DatabaseAuditSpecification.AddAuditSpecificationDetail($SpecificationDetail)
            }

            $DatabaseAuditSpecification.Enable()
        }
        else {
            throw "Could not find a database named $Database."
        }

        if ($PassThru) {
            Write-Output -InputObject $DatabaseAuditSpecification
        }
    }

    End {
    }
}

Function Set-SQLInstanceManagementRoles {
    <#
        .SYNOPSIS
            SQL Server must be configured to separate user functionality (including user interface services) from database management functionality.
 
            SQL Server must prevent non-privileged users from executing privileged functionality, to include disabling, circumventing, or altering implemented security safeguards/countermeasures.
 
            SQL Server and Windows must enforce access restrictions associated with changes to the configuration of the SQL Server instance or database(s).
 
        .DESCRIPTION
            The Set-SQLInstanceManagementRoles cmdlet creates new roles with the same permissions as the key built in roles: sysadmin, serveradmin, and dbcreator. It also creates a role granted CONNECT SQL.
 
            Then, identified Windows Users, Groups, or SQL Logins are created if they do not already exist and added to these roles.
 
            Any other logins or roles explicitly granted these permissions will have them revoked and will be added to the new roles. Any members of the built in roles will be removed and added to the new
            roles, except for system accounts in the sysadmin role like NT SERVICE, NT AUTHORITY, sys, INFORMATION_SCHEMA or ##*## accounts.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER ConnectSqlRoleName
            The name of the role being granted CONNECT SQL to create. This defaults to CONNECT_SQL_ROLE.
 
        .PARAMETER SysAdminRoleName
            The name of the role being granted CONTROL SERVER to create. This defaults to SYSADMIN_ROLE.
 
        .PARAMETER ServerAdminRoleName
            The name of the role being granted the server admin permissions to create. This defaults to SERVERADMIN_ROLE.
 
        .PARAMETER DBCreatorRoleName
            The name of the role being granted CREATE ANY DATABASE to create. This defaults to DBCREATOR_ROLE.
 
        .PARAMETER SysAdminRoleMembers
            The Server Logins that should belong to the new Server Role. Logins will be created if they don't already exist.
 
        .PARAMETER ServerAdminRoleMembers
            The Server Logins that should belong to the new Server Role. Logins will be created if they don't already exist.
 
        .PARAMETER DBCreatorRoleMembers
            The Server Logins that should belong to the new Server Role. Logins will be created if they don't already exist.
 
        .PARAMETER ConnectSqlRoleMembers
            The Server Logins that should belong to the new Server Role. Logins will be created if they don't already exist.
 
        .PARAMETER DefaultSysAdmins
            The Server Logins in this list will not be removed from the sysadmin role during the execution of this cmdlet to preserve the capability to perform functions that explicitly check for membership in sysadmin.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .PARAMETER DropSysAdmins
            Specify if you actually want members of the sysadmin group removed. Be cautious performing this action, as it will only leave the sa, built in principals, and any users defined in the DefaultSysAdmins parameter.
 
        .PARAMETER Force
            Ignores confirmation for dropping members from sysadmin.
 
        .EXAMPLE
            Set-SQLInstanceManagementRoles -ComputerName "SQL2014" `
                                            -InstanceName "MyInstance" `
                                            -SysAdminRoleMembers @("Contoso\UG-SQL-DEV_001-SysAdmins") `
                                            -ServerAdminRoleMembers @("Contoso\UG-SQL-DEV_001-ServerAdmins") `
                                            -DBCreatorRoleMembers @("Contoso\UG-SQL-DEV_001-DBCreators")
                                             
            Creates the 4 roles with their default names and creates 3 new logins and adds those logins to the corresponding role. Existing privileges are transferred to the new roles.
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-020500
                SQL4-00-032500
                SQL4-00-033900
            Rule ID
                SQL4-00-020500_rule
                SQL4-00-032500_rule
                SQL4-00-033900_rule
            Vuln ID
                SQL4-00-020500
                SQL4-00-032500
                SQL4-00-033900
            Severity
                CAT II
    #>
    [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = "HIGH")]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$ConnectSqlRoleName = "CONNECT_SQL_ROLE",

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$SysAdminRoleName = "SYSADMIN_ROLE",

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$ServerAdminRoleName = "SERVERADMIN_ROLE",

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$DBCreatorRoleName = "DBCREATOR_ROLE",

        [Parameter()]
        [System.String[]]$SysAdminRoleMembers,

        [Parameter()]
        [System.String[]]$ServerAdminRoleMembers,

        [Parameter()]
        [System.String[]]$DBCreatorRoleMembers,

        [Parameter()]
        [System.String[]]$ConnectSqlRoleMembers,

        [Parameter()]
        [System.String[]]$DefaultSysAdmins,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter()]
        [System.Boolean]$DropSysAdmins = $false,

        [Parameter()]
        [switch]$Force
    )

    Begin {
    }

    Process {
        Write-Verbose -Message "Creating SQL Instance Management Roles."
        Write-Verbose -Message "STIG ID: SQL4-00-020500, SQL4-00-032500, SQL4-00-033900"

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        $TranslatedSysAdmin = @()
        $TranslatedServerAdmin = @()
        $TranslatedDBCreator = @()
        $TranslatedConnectSql = @()

        #SysAdmin
        foreach ($Login in ($SysAdminRoleMembers | Where-Object {![System.String]::IsNullOrEmpty($_)}))
        {
            [Microsoft.SqlServer.Management.Smo.Login]$NewLogin = Add-SQLInstanceLogin -SqlServer $SqlConnection -Name $Login -PassThru
            $TranslatedSysAdmin += $NewLogin.Name
        }

        #ServerAdmin
        foreach ($Login in ($ServerAdminRoleMembers | Where-Object {![System.String]::IsNullOrEmpty($_)}))
        {
            [Microsoft.SqlServer.Management.Smo.Login]$NewLogin = Add-SQLInstanceLogin -SqlServer $SqlConnection -Name $Login -PassThru
            $TranslatedServerAdmin += $NewLogin.Name
        }

        #DBCreator
        foreach ($Login in ($DBCreatorRoleMembers | Where-Object {![System.String]::IsNullOrEmpty($_)}))
        {
            [Microsoft.SqlServer.Management.Smo.Login]$NewLogin = Add-SQLInstanceLogin -SqlServer $SqlConnection -Name $Login -PassThru
            $TranslatedDBCreator += $NewLogin.Name
        }

        #ConnectSql
        foreach ($Login in ($ConnectSqlRoleMembers | Where-Object {![System.String]::IsNullOrEmpty($_)}))
        {
            [Microsoft.SqlServer.Management.Smo.Login]$NewLogin = Add-SQLInstanceLogin -SqlServer $SqlConnection -Name $Login -PassThru
            $TranslatedConnectSql += $NewLogin.Name
        }

        #Create new roles
        [Microsoft.SqlServer.Management.Smo.ServerRole]$SysAdminRole = Add-SQLInstanceServerRole -SqlServer $SqlConnection -Name $SysAdminRoleName -PassThru
        [Microsoft.SqlServer.Management.Smo.ServerRole]$ServerAdminRole = Add-SQLInstanceServerRole -SqlServer $SqlConnection -Name $ServerAdminRoleName -PassThru
        [Microsoft.SqlServer.Management.Smo.ServerRole]$DBCreatorRole = Add-SQLInstanceServerRole -SqlServer $SqlConnection -Name $DBCreatorRoleName -PassThru
        [Microsoft.SqlServer.Management.Smo.ServerRole]$ConnectSqlRole = Add-SQLInstanceServerRole -SqlServer $SqlConnection -Name $ConnectSqlRoleName -PassThru

        [Microsoft.SqlServer.Management.Smo.ServerPermissionSet]$ServerAdminPermissionSet = New-Object -TypeName Microsoft.SqlServer.Management.Smo.ServerPermissionSet
        $ServerAdminPermissionSet.AlterAnyEndpoint = $true
        $ServerAdminPermissionSet.AlterResources = $true
        $ServerAdminPermissionSet.AlterServerState = $true
        $ServerAdminPermissionSet.AlterSettings = $true
        $ServerAdminPermissionSet.Shutdown = $true
        $ServerAdminPermissionSet.ViewServerState = $true

        #Assign permissions to roles
        $SqlConnection.Grant([Microsoft.SqlServer.Management.Smo.ServerPermission]::ControlServer, $SysAdminRole.Name, $true)
        $SqlConnection.Grant($ServerAdminPermissionSet, $ServerAdminRole.Name)
        $SqlConnection.Grant([Microsoft.SqlServer.Management.Smo.ServerPermission]::CreateAnyDatabase, $DBCreatorRole.Name)
        $SqlConnection.Grant([Microsoft.SqlServer.Management.Smo.ServerPermission]::ConnectSql, $ConnectSqlRole.Name)
    
        #Assign new logins as members to the new roles
        if ($TranslatedSysAdmin.Count -gt 0) {
            #https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles
            #sysadmin equivalent needs the SQLAgentOperatorRole
            [Microsoft.SqlServer.Management.Smo.Database]$MSDB = $SqlConnection.Databases["msdb"]
            [Microsoft.SqlServer.Management.Smo.DatabaseRole]$SqlAgentOperatorRole = $MSDB.Roles["SQLAgentOperatorRole"]
            
            [System.String[]]$MsdbUsers = $MSDB.Users | Select-Object -ExpandProperty Name

            Write-Verbose -Message "Adding $($TranslatedSysAdmin -join `",`") to $SysAdminRoleName and $($SqlAgentOperatorRole.Name)."
            $TranslatedSysAdmin | ForEach-Object { 
                $SysAdminRole.AddMember($_) 

                if ($MsdbUsers -inotcontains $_)
                {
                    Write-Verbose -Message "Creating database user for $_ in msdb."
                    [Microsoft.SqlServer.Management.Smo.User]$NewSysAdmin = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User($MSDB, $_)
                
                    try {
                        $NewSysAdmin.Create()
                        $SqlAgentOperatorRole.AddMember($_)
                    }
                    catch [Exception] {
                        if ($_.Exception.InnerException -ne $null) {
                            $Exception = $_.Exception
                            while ($Exception.InnerException -ne $null) {                      
                                $Exception = $Exception.InnerException
                            }

                            Write-Error -Exception $Exception
                        }
                        else {
                            Write-Error -Exception $_.Exception
                        }
                    }
                }
                else 
                {
                    Write-Verbose -Message "Msdb already contains a user for $_."
                    $SqlAgentOperatorRole.AddMember($_)
                }
            }
        }
        else {
            Write-Verbose -Message "No members in SysAdminRoleMembers."
        }

        if ($TranslatedServerAdmin.Count -gt 0) {
            Write-Verbose -Message "Adding $($TranslatedServerAdmin -join `",`") to $ServerAdminRoleName."
            $TranslatedServerAdmin | ForEach-Object { $ServerAdminRole.AddMember($_) }
        }
        else {
            Write-Verbose -Message "No members in ServerAdminRoleMembers."
        }

        if ($TranslatedDBCreator.Count -gt 0) {
            Write-Verbose -Message "Adding $($TranslatedDBCreator -join `",`") to $DBCreatorRoleName."
            $TranslatedDBCreator | ForEach-Object { $DBCreatorRole.AddMember($_) }
        }
        else {
            Write-Verbose -Message "No members in DBCreatorRoleMembers."
        }

        if ($TranslatedConnectSql.Count -gt 0) {
            Write-Verbose -Message "Adding $($TranslatedConnectSql -join `",`") to $ConnectSqlRoleName."
            $TranslatedConnectSql | ForEach-Object { $ConnectSqlRole.AddMember($_) }
        }
        else {
            Write-Verbose -Message "No members in ConnectSqlRoleMembers."
        }

        Write-Verbose -Message "Reviewing existing principals and altering permissions."

        [System.String[]]$Logins = $SqlConnection.Logins | Where-Object {[System.Convert]::ToBase64String($_.Sid) -ne $script:SA_SID} | Where-NotMatchIn -Matches $script:IgnoreNames -Property "Name" | Select-Object -ExpandProperty Name
        [System.String[]]$Roles = $SqlConnection.Roles  | Where-Object {$_.IsFixedRole -eq $false -and $_.Name -inotin @($SysAdminRoleName, $ServerAdminRoleName, $ConnectSqlRoleName, $DBCreatorRoleName) } | Select-Object -ExpandProperty Name

        [System.String[]]$Principals = @()
        $Principals += $Logins
        $Principals += $Roles

        Write-Verbose -Message "Reviewing principals $($Principals -join `",`")"

        #Check explicit permissions on each principal
        foreach ($Principal in $Principals) {
            [Microsoft.SqlServer.Management.Smo.ServerPermissionInfo[]]$Permissions = $SqlConnection.EnumServerPermissions($Principal)
            Write-Verbose -Message "Reviewing explicit permissions for $Principal."

            #PermissionType is a ServerPermissionSet object. The ServerPermissionSet object has properties that
            #correspond to the different permissions
            foreach ($ServerPermissionSet in $Permissions | Select-Object -ExpandProperty PermissionType) {
                
                if ($ServerPermissionSet.ControlServer -eq $true) {
                    Write-Verbose -Message "Adding $Principal to $SysAdminRoleName."
                    $SysAdminRole.AddMember($Principal)

                    Write-Verbose -Message "Revoking CONTROL SERVER from $Principal."
                    $SqlConnection.Revoke([Microsoft.SqlServer.Management.Smo.ServerPermission]::ControlServer, $Principal)
                }

                if ($ServerPermissionSet.AlterAnyEndpoint -eq $true -and $ServerPermissionSet.AlterResources -eq $true -and $ServerPermissionSet.AlterServerState -eq $true -and
                    $ServerPermissionSet.AlterSettings -eq $true -and $ServerPermissionSet.Shutdown -eq $true -and $ServerPermissionSet.ViewServerState -eq $true) {
                    Write-Verbose -Message "Adding $Principal to $ServerAdminRoleName."
                    $ServerAdminRole.AddMember($Principal)
                        
                    Write-Verbose -Message "Revoking Server Admin permissions from $Principal."
                    $SqlConnection.Revoke($ServerAdminPermissionSet, $Principal)
                }

                if ($ServerPermissionSet.CreateAnyDatabase -eq $true) {
                    Write-Verbose -Message "Adding $Principal to $DBCreatorRoleName."
                    $DBCreatorRole.AddMember($Principal)

                    Write-Verbose -Message "Revoking CREATE ANY DATABASE from $Principal"
                    $SqlConnection.Revoke([Microsoft.SqlServer.Management.Smo.ServerPermission]::CreateAnyDatabase, $Principal)
                }

                if ($ServerPermissionSet.ConnectSql -eq $true) {
                    Write-Verbose -Message "Adding $Principal to $ConnectSqlRoleName."
                    $ConnectSqlRole.AddMember($Principal)

                    Write-Verbose -Message "Revoking CONNECT SQL from $Principal"
                    $SqlConnection.Revoke([Microsoft.SqlServer.Management.Smo.ServerPermission]::ConnectSql, $Principal)
                }        
            }
        }

        #Translate the default sys admins to Windows NT names to compare against sql logins
        $TempDefaultAdmins = @()

        foreach ($Admin in $DefaultSysAdmins)
        {
            [System.String]$TempAcct = Get-AccountTranslatedNTName -UserName $Admin -ComputerName $SqlConnection.NetName -Credential $Credential

            #This might be a SQL auth login, see if it exists
            if ([System.String]::IsNullOrEmpty($TempAcct))
            {                    
                if (($SqlConnection.Logins | Select-Object -ExpandProperty Name) -inotcontains $Admin)
                {
                    throw "Could not find the provided default sysadmin user $Login"
                }
            }
            else
            {
                $Admin = $TempAcct
            }

            $TempDefaultAdmins += $Admin
        }

        $DefaultSysAdmins = $TempDefaultAdmins

        #Review fixed roles

        Write-Verbose -Message "Reviewing fixed server roles and moving members."

        #Sysadmin SID = 0x03
        [Microsoft.SqlServer.Management.Smo.ServerRole]$SysAdminFixedRole = $SqlConnection.Roles | Where-Object {$_.IsFixedRole -eq $true -and $_.ID -eq 0x03} | Select-Object -First 1

        #Don't modify sa or builtin NT SERVICE logins like SQLSERVERAGENT or MSSQLSERVER
        foreach ($Member in ($SysAdminFixedRole.EnumMemberNames() | Where-NotMatchIn -Matches $script:IgnoreNames)) 
        {        
            #Make sure an instance login exists and it's not the sa account
            if (($SqlConnection.Logins | Where-Object {$_.Name -eq $Member -and [System.Convert]::ToBase64String($_.Sid) -ne $script:SA_SID} ) -ne $null) {
                
                #If the member is part of the default sysadmin definition, ignore them
                if ($Member -inotin $DefaultSysAdmins)
                {
                    #If the member was already added to these groups by being defined in the SysAdminMembers, no need to add them again
                    if ($Member -inotin $TranslatedSysAdmin)
                    {
                        Write-Verbose -Message "Adding $Member to $SysAdminRoleName."
                        $SysAdminRole.AddMember($Member)

                        if ($MsdbUsers -inotcontains $Member)
                        {
                            Write-Verbose -Message "Creating database user for $Member in msdb."
                            [Microsoft.SqlServer.Management.Smo.User]$NewMsdbUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User($MSDB, $Member)
                
                            try {
                                $NewMsdbUser.Create()
                                Write-Verbose -Message "Adding $Member to $($SqlAgentOperatorRole.Name)."
                                $SqlAgentOperatorRole.AddMember($Member)
                            }
                            catch [Exception] {
                                if ($_.Exception.InnerException -ne $null) {
                                    $Exception = $_.Exception
                                    while ($Exception.InnerException -ne $null) {                      
                                        $Exception = $Exception.InnerException
                                    }

                                    Write-Error -Exception $Exception
                                }
                                else {
                                    Write-Error -Exception $_.Exception
                                }
                            }
                        }
                        else 
                        {
                            Write-Verbose -Message "Msdb already contains a user for $Member."
                            $SqlAgentOperatorRole.AddMember($Member)
                        }
                    }
                    else
                    {
                        Write-Verbose -Message "Ignoring $Member, already added to the new sysadmin role."
                    }

                    if ($DropSysAdmins -eq $true)
                    {
                        if ($Force -or $PSCmdlet.ShouldProcess("sysadmin", "Drop $Member"))
                        {
                            Write-Verbose -Message "Removing $Member from $($SysAdminFixedRole.Name)."
                            $SysAdminFixedRole.DropMember($Member)
                        }
                    }
                    else
                    {
                        Write-Warning -Message "--- YOU SHOULD DROP $Member FROM sysadmin ---"
                    }
                }
                else
                {
                    Write-Verbose -Message "Ignoring $Member, part of the defined default sysadmins."
                }
            }
        }

        #ServerAdmin SID = 0x05
        [Microsoft.SqlServer.Management.Smo.ServerRole]$ServerAdminFixedRole = $SqlConnection.Roles | Where-Object {$_.IsFixedRole -eq $true -and $_.ID -eq 0x05} | Select-Object -First 1

        foreach ($Member in $ServerAdminFixedRole.EnumMemberNames()) {
            Write-Verbose -Message "Adding $Member to $ServerAdminRoleName."
            $ServerAdminRole.AddMember($Member)

            Write-Verbose -Message "Removing $Member from $($ServerAdminFixedRole.Name)."
            $ServerAdminFixedRole.DropMember($Member)
        }

        #DBCreator SID = 0x09
        [Microsoft.SqlServer.Management.Smo.ServerRole]$DBCreatorFixedRole =  $SqlConnection.Roles | Where-Object {$_.IsFixedRole -eq $true -and $_.ID -eq 0x09} | Select-Object -First 1

        foreach ($Member in $DBCreatorFixedRole.EnumMemberNames()) {
            Write-Verbose -Message "Adding $Member to $DBCreatorRoleName."
            $DBCreatorRole.AddMember($Member)

            Write-Verbose -Message "Removing $Member from $($DBCreatorFixedRole.Name)."
            $DBCreatorFixedRole.DropMember($Member)
        }
    }

    End {
    }
}

Function Get-SQLInstanceLogin {
    <#
        .SYNOPSIS
            Gets a SQL Login.
 
        .DESCRIPTION
            The Get-SQLInstanceLogin cmdlet gets a login by name or SID.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Name
            The name of the login to find.
 
        .PARAMETER Sid
            The SID of the login to find. This can be a SID string, a Byte[], or the Int value of the byte[], such as 0x0001 for {0, 1}
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Get-SQLInstanceLogin -ComputerName "SQL2014" -Instance "MyInstance" -Sid 0x01
             
            Gets the SQL login with SID 0x01, which is the sa account.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.Login
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithSid")]
        [System.String]$ComputerName = "localhost",
        
        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithSid")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithSid")]
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter(ParameterSetName="ExistingConnectionWithName",Mandatory=$true,Position=0)]
        [Parameter(ParameterSetName="ExistingConnectionWithSid",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Position=1,Mandatory=$true,ParameterSetName="ExistingConnectionWithName")]
        [Parameter(Position=0,Mandatory=$true,ParameterSetName="NewConnectionWithName")]
        [ValidateNotNullOrEmpty()]
        [System.String]$Name,

        [Parameter(Position=1,Mandatory=$true,ParameterSetName="ExistingConnectionWithSid")]
        [Parameter(Position=0,Mandatory=$true,ParameterSetName="NewConnectionWithSid")]
        $Sid
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -ilike "ExistingConnection*") {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        switch -wildcard ($PSCmdlet.ParameterSetName) {
            "*Name" {
                
                $Temp = Get-AccountTranslatedNTName -UserName $Name -ComputerName $SqlConnection.NetName -Credential $Credential
                Write-Verbose -Message "Matching login by name, $Temp."

                [Microsoft.SqlServer.Management.Smo.Login]$Login = $SqlConnection.Logins | Where-Object {$_.Name -ieq $Temp } | Select-Object -First 1
                Write-Verbose -Message "Found login is null: $($Login -eq $null)"
                break
            }
            "*Sid" {
                Write-Verbose -Message "Matching login by sid, $Sid."

                #Get the byte[] value of the SID and assign it to $Value for comparison against existing logins
                if ($Sid.GetType() -eq [System.Byte[]]) {
                    [System.Byte[]]$Value = $Sid
                }
                elseif ($Sid.GetType() -eq [System.String]) {
                    [System.Security.Principal.SecurityIdentifier]$Temp = New-Object System.Security.Principal.SecurityIdentifier($Sid)                   
                    [System.Byte[]]$Value = New-Object System.Byte[]($Temp.BinaryLength)
                    $Temp.GetBinaryForm($Value, 0)
                }
                elseif ($Sid.GetType() -eq [System.Int32]) {
                    [System.Byte[]]$Value = [System.BitConverter]::GetBytes($Sid)
                }
                else
                {
                    throw "The SID value must be a byte[], string, or int."
                }

                [Microsoft.SqlServer.Management.Smo.Login]$Login = $SqlConnection.Logins | Where-Object {
                    
                    #The SID may be less than 4 bytes, so it needs to be 4 bytes to compare equality
                    if ($_.Sid.Length -lt 4) {
                        [System.Byte[]]$TempArr = New-Object System.Byte[](4)
                        $_.Sid.CopyTo($TempArr, 0)
                    }
                    else {
                        [System.Byte[]]$TempArr = New-Object System.Byte[]($_.Sid.Length)
                        $TempArr = $_.Sid
                    }

                    [System.BitConverter]::ToInt32($Value, 0) -eq [BitConverter]::ToInt32($TempArr, 0)
                    
                } | Select-Object -First 1

                break
            }
        }

        Write-Output -InputObject $Login
    }

    End {
    }
}

Function Rename-SQLInstanceAccount {
    <#
        .SYNOPSIS
            Renames a SQL Login.
 
        .DESCRIPTION
            The Rename-SQLInstanceAccount cmdlet renames a provided login.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Name
            The current name of the account to rename.
 
        .PARAMETER Sid
            The SID of the login principal to rename.
 
        .PARAMETER NewName
            The new name of the login.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Rename-SQLInstanceAccount -ComputerName "SQL2014" -Instance "MyInstance" -Name "sa" -NewName "xsa"
             
            Renames the sa account to xsa.
 
        .EXAMPLE
            Rename-SQLInstanceAccount -ComputerName "SQL2014" -Instance "MyInstance" -Sid 0x01 -NewName "xsa"
             
            Renames the account with the SID 0x01 (the sa account) to xsa.
 
        .INPUTS
            None
 
        .OUTPUTS
            None or Microsoft.SqlServer.Management.Smo.Login
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-010200
            Rule ID
                SQL4-00-010200_rule
            Vuln ID
                SQL4-00-010200
            Severity
                CAT III
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithSid")]
        [System.String]$ComputerName = "localhost",
        
        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithSid")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnectionWithName",Mandatory=$true,Position=0)]
        [Parameter(ParameterSetName="ExistingConnectionWithSid",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Position=1,Mandatory=$true,ParameterSetName="ExistingConnectionWithName")]
        [Parameter(Position=0,Mandatory=$true,ParameterSetName="NewConnectionWithName")]
        [ValidateNotNullOrEmpty()]
        [System.String]$Name,

        [Parameter(Position=1,Mandatory=$true,ParameterSetName="ExistingConnectionWithSid")]
        [Parameter(Position=0,Mandatory=$true,ParameterSetName="NewConnectionWithSid")]
        $Sid,

        [Parameter(Position=1,Mandatory=$true,ParameterSetName="NewConnectionWithName")]
        [Parameter(Position=1,Mandatory=$true,ParameterSetName="NewConnectionWithSid")]
        [Parameter(Position=2,Mandatory=$true,ParameterSetName="ExistingConnectionWithSid")]
        [Parameter(Position=2,Mandatory=$true,ParameterSetName="ExistingConnectionWithName")]
        [System.String]$NewName,

        [Parameter()]
        [switch]$PassThru,

        [Parameter(ParameterSetName = "NewConnectionWithName")] 
        [Parameter(ParameterSetName = "NewConnectionWithSid")]
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        Write-Verbose -Message "Renaming SQL Login."
        Write-Verbose -Message "STIG ID: SQL4-00-010200"

        if ($PSCmdlet.ParameterSetName -ilike "ExistingConnection*") {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        switch -wildcard ($PSCmdlet.ParameterSetName)
        {
            "*Name" {
                [Microsoft.SqlServer.Management.Smo.Login]$Login = Get-SQLInstanceLogin -SqlServer $SqlConnection -Name $Name
                break
            }
            "*Sid" {
                [Microsoft.SqlServer.Management.Smo.Login]$Login = Get-SQLInstanceLogin -SqlServer $SqlConnection -Sid $Sid
                break
            }
            default {
                throw "Did not match an expected parameter set name, $($PSCmdlet.ParameterSetName)."
            }
        }

        if ($Login -ne $null) {
            $Login.Rename($NewName)
        }
        else {
            Write-Warning "Could not find a login matching the provided information."
        }

        if ($PassThru) {
            Write-Output -InputObject $Login
        }
    }

    End {       
    }
}

Function Disable-SQLInstanceAccount {
    <#
        .SYNOPSIS
            Disables a SQL Login.
 
        .DESCRIPTION
            The Disable-SQLInstanceAccount cmdlet disables a provided login.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Name
            The current name of the account to disable.
 
        .PARAMETER Sid
            The SID of the login principal to disable.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Disable-SQLInstanceAccount -ComputerName "SQL2014" -InstanceName "MyInstance" -Name "sa"
             
            Disables the sa account
 
        .EXAMPLE
            Rename-SQLInstanceAccount -ComputerName "SQL2014" -InstanceName "MyInstance" -Sid 0x01
             
            Disables the account with the SID 0x01 (the sa account).
 
        .INPUTS
            None
 
        .OUTPUTS
            None or Microsoft.SqlServer.Management.Smo.Login
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-017100
            Rule ID
                SQL4-00-017100_rule
            Vuln ID
                SQL4-00-017100
            Severity
                CAT II
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithSid")]
        [System.String]$ComputerName = "localhost",
        
        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithSid")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnectionWithName",Mandatory=$true,Position=0)]
        [Parameter(ParameterSetName="ExistingConnectionWithSid",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Position=1,Mandatory=$true,ParameterSetName="ExistingConnectionWithName")]
        [Parameter(Position=0,Mandatory=$true,ParameterSetName="NewConnectionWithName")]
        [ValidateNotNullOrEmpty()]
        [System.String]$Name,

        [Parameter(Position=1,Mandatory=$true,ParameterSetName="ExistingConnectionWithSid")]
        [Parameter(Position=0,Mandatory=$true,ParameterSetName="NewConnectionWithSid")]
        $Sid,

        [Parameter()]
        [switch]$PassThru,

        [Parameter(ParameterSetName = "NewConnectionWithSid")]
        [Parameter(ParameterSetName = "NewConnectionWithName")]
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        Write-Verbose -Message "Disabling SQL Login."
        Write-Verbose -Message "STIG ID: SQL4-00-017100"

        if ($PSCmdlet.ParameterSetName -ilike "ExistingConnection*") {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        switch -wildcard ($PSCmdlet.ParameterSetName)
        {
            "*Name" {
                [Microsoft.SqlServer.Management.Smo.Login]$Login = Get-SQLInstanceLogin -SqlServer $SqlConnection -Name $Name
                break
            }
            "*Sid" {
                [Microsoft.SqlServer.Management.Smo.Login]$Login = Get-SQLInstanceLogin -SqlServer $SqlConnection -Sid $Sid
                break
            }
            default {
                throw "Did not match an expected parameter set name, $($PSCmdlet.ParameterSetName)."
            }
        }

        if ($Login -ne $null) {
            $Login.Disable()
        }
        else {
            Write-Warning "Could not find a login matching the provided information."
        }

        if ($PassThru) {
            Write-Output -InputObject $Login
        }
    }

    End {       
    }
}

Function Set-SQLInstanceXPCmdShell{
    <#
        .SYNOPSIS
            Access to xp_cmdshell must be disabled, unless specifically required and approved.
 
        .DESCRIPTION
            The Set-SQLInstanceXPCmdShell cmdlet enables or disables xp_cmdshell.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Enabled
            Specifies whether xp_cmdshell should be enabled or disabled. Defaults to false.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Set-SQLInstanceXPCmdShell -ComputerName "SQL2014" -InstanceName "MyInstance" -Enabled $false
             
            Disables xp_cmdshell on the specified instance.
 
        .INPUTS
            None
 
        .OUTPUTS
            None or System.Boolean
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-017200
            Rule ID
                SQL4-00-017200_rule
            Vuln ID
                SQL4-00-017200
            Severity
                CAT II
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter()]
        [System.Boolean]$Enabled = $false,

        [Parameter()]
        [switch]$PassThru,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        Write-Verbose -Message "Disabling XP_Cmdshell."
        Write-Verbose -Message "STIG ID: SQL4-00-017200"

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        Write-Verbose -Message "Enabling Show Advanced Options."
        $Max = $SqlConnection.Configuration.ShowAdvancedOptions.Maximum
        $Min = $SqlConnection.Configuration.ShowAdvancedOptions.Minimum

        Write-Verbose -Message "ShowAdvancedOptions mininum: $Min maximum: $Max"
        $SqlConnection.Configuration.ShowAdvancedOptions.ConfigValue = $Max
        $SqlConnection.Configuration.Alter()

        Write-Verbose -Message "Setting xp_cmdshell enabled to $Enabled."
        $SqlConnection.Configuration.XPCmdShellEnabled.ConfigValue = $Enabled
        $SqlConnection.Configuration.Alter()

        Write-Verbose -Message "Disabling Show Advanced Options."
        $SqlConnection.Configuration.ShowAdvancedOptions.ConfigValue = $Min
        $SqlConnection.Configuration.Alter()

        if ($PassThru) {
            Write-Output -InputObject $SqlConnection.Configuration.XPCmdShellEnabled
        }
    }

    End {       
    }
}

Function Set-SQLInstanceLoginPasswordPolicies {
    <#
        .SYNOPSIS
            If SQL Server authentication, using passwords, is employed, SQL Server must enforce the DoD standards for password complexity and lifetime.
 
        .DESCRIPTION
            The Set-SQLInstanceLoginPasswordPolicies cmdlet enables the password policy and password expiration options on every SQL Login.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER IncludeSA
            Specifies whether the built in SA account should also have these properties set.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Set-SQLInstanceLoginPasswordPolicies -ComputerName "SQL2014" -InstanceName "MyInstance"
             
            Enables the password policy and expiration on every SQL login on the specified instance.
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Instance V1R0
            STIG ID
                SQL4-00-038900
            Rule ID
                SQL4-00-038900_rule
            Vuln ID
                SQL4-00-038900
            Severity
                CAT II
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter()]
        [switch]$IncludeSA,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        Write-Verbose -Message "Setting SQL Login Password Policy and Expiration."
        Write-Verbose -Message "STIG ID: SQL4-00-038900"

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        if ($IncludeSA) {
            $SqlConnection.Logins | Where-Object {$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin } | ForEach-Object {
                Write-Verbose -Message "Configuring password policy for $($_.Name)."
                $_.PasswordPolicyEnforced = $true
                $_.PasswordExpirationEnabled = $true
                $_.Alter()
            }
        }
        else {           
            $SqlConnection.Logins | Where-Object {$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin -and [System.Convert]::ToBase64String($_.Sid) -ne $script:SA_SID } | ForEach-Object {
                Write-Verbose -Message "Configuring password policy for $($_.Name)."
                $_.PasswordPolicyEnforced = $true
                $_.PasswordExpirationEnabled = $true
                $_.Alter()
            }
        }       
    }

    End {
    }
}

Function Set-SQLInstanceProtocols {
    <#
        SYNOPSIS
            Having unnecessary protocols enabled exposes the system to avoidable threats. In a typical installation, only TCP/IP will be required.
 
        .DESCRIPTION
            The Set-SQLInstanceProtocols cmdlet disables the selected protocols.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .PARAMETER NamedPipesEnabled
            Disables or enables the named pipes protocol.
 
        .PARAMETER SharedMemoryEnabled
            Disables or enables the shared memory protocol.
 
        .PARAMETER TcpEnabled
            Disables or enables the TCP/IP protocol. This is not a typical selection.
 
        .PARAMETER RestartSqlService
            Restarts the SQL service. If this option is not selected, the service must manually be restarted in order for the changes to take effect
 
        .EXAMPLE
            Set-SQLInstanceProtocols -ComputerName "SQL2014" -InstanceName "MyInstance" -NamedPipesEnabled $false -SharedMemoryEnabled $false -RestartSqlService
             
            Disables the shared memory and named pipes protocols on the SQL2014 computer and restarts the SQL service to implement the changes.
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
 
    .FUNCTIONALITY
        STIG ID
            SQL4-00-034200
        Rule ID
            SQL4-00-034200_rule
        Vuln ID
            SQL4-00-034200
        Severity
            CAT II
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter()]
        [System.Boolean]$NamedPipesEnabled,

        [Parameter()]
        [System.Boolean]$SharedMemoryEnabled,

        [Parameter()]
        [System.Boolean]$TcpEnabled,

        [Parameter()]
        [switch]$RestartSqlService,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {

    }

    Process {
        Write-Verbose -Message "Setting SQL Allowed Protocols."
        Write-Verbose -Message "STIG ID: SQL4-00-034200"

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]$Computer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer($SqlConnection.NetName)

        if ($PSBoundParameters.ContainsKey("NamedPipesEnabled"))
        {
            Write-Verbose -Message "Setting named pipes enabled to $NamedPipesEnabled."
            [Microsoft.SqlServer.Management.Smo.Wmi.ClientProtocol]$NP = $Computer.ClientProtocols | Where-Object {$_.Name -eq "np"} | Select-Object -First 1
            $NP.IsEnabled = $NamedPipesEnabled
            $NP.Alter()
        }

        if ($PSBoundParameters.ContainsKey("SharedMemoryEnabled"))
        {
            Write-Verbose -Message "Setting shared memory enabled to $SharedMemoryEnabled."
            [Microsoft.SqlServer.Management.Smo.Wmi.ClientProtocol]$SM = $Computer.ClientProtocols | Where-Object {$_.Name -eq "sm"} | Select-Object -First 1
            $SM.IsEnabled = $SharedMemoryEnabled
            $SM.Alter()
        }

        if ($PSBoundParameters.ContainsKey("TcpEnabled"))
        {
            Write-Verbose -Message "Setting TCP/IP enabled to $TcpEnabled."
            [Microsoft.SqlServer.Management.Smo.Wmi.ClientProtocol]$TCP = $Computer.ClientProtocols | Where-Object {$_.Name -eq "tcp"} | Select-Object -First 1
            $TCP.IsEnabled = $TcpEnabled
            $TCP.Alter()
        }

        if (($DisableNamedPipes -or $DisableSharedMemory -or $DisableTcp) -and -not $RestartSqlService)
        {
            Write-Warning -Message "The SQL service must be restarted for these changes to take effect."
        }
        elseif ($RestartSqlService)
        {
            if ($ComputerName -iin $script:LocalNames)
            {
                [System.String]$Instance = "MSSQLSERVER"

                if (![System.String]::IsNullOrEmpty($SqlConnection.InstanceName))
                {
                    $Instance = "MSSQL`$$($SqlConnection.InstanceName)"
                }

                Write-Verbose -Message "Restarting the SQL service: $Instance"

                Restart-Service -Name $Instance -Force

                Wait-SQLAgentService -SqlServer $SqlConnection | Out-Null
            }
            else {
                $Session = Enter-PSSession -ComputerName $ComputerName -Credential $Credential

                Invoke-Command -Session $Session -ScriptBlock {
                    [System.String]$Instance = "MSSQLSERVER"

                    if (![System.String]::IsNullOrEmpty($args[0]))
                    {
                        $Instance = "MSSQL`$$($args[0])"
                    }

                    Write-Verbose -Message "Restarting the SQL service: $Instance"
                    Restart-Service -Name $args[0] -Force

                } -ArgumentList @($SqlConnection.InstanceName)

                Remove-PSSession -Session $Session
            }
        }
    }

    End {
    }
}

Function Set-SQLInstanceDefaultTrace{
    <#
        .SYNOPSIS
            Enables or disables the default trace.
 
        .DESCRIPTION
            The Set-SQLInstanceDefaultTrace cmdlet enables or disables the default trace on the instance.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Enabled
            Specifies whether to enable or disable the default trace. This defaults to true.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Set-SQLInstanceDefaultTrace -ComputerName "SQL2014" -InstanceName "MyInstance" -Enabled $true
             
            Enables the default trace on the specified instance.
 
        .INPUTS
            None
 
        .OUTPUTS
            System.Boolean
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter()]
        [bool]$Enabled = $true,

        [Parameter()]
        [switch]$PassThru,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        Write-Verbose -Message "Enabling Show Advanced Options."
        $Max = $SqlConnection.Configuration.ShowAdvancedOptions.Maximum
        $Min = $SqlConnection.Configuration.ShowAdvancedOptions.Minimum

        Write-Verbose -Message "ShowAdvancedOptions mininum: $Min maximum: $Max"
        $SqlConnection.Configuration.ShowAdvancedOptions.ConfigValue = $Max
        $SqlConnection.Configuration.Alter()

        Write-Verbose -Message "Setting default trace enabled to $Enabled."
        $SqlConnection.Configuration.DefaultTraceEnabled.ConfigValue = $Enabled
        $SqlConnection.Configuration.Alter()

        Write-Verbose -Message "Disabling Show Advanced Options."
        $SqlConnection.Configuration.ShowAdvancedOptions.ConfigValue = $Min
        $SqlConnection.Configuration.Alter()

        if ($PassThru) {
            Write-Output -InputObject $SqlConnection.Configuration.DefaultTraceEnabled
        }
    }

    End {     
    }
}

Function Add-SQLInstanceLogin {
    <#
        .SYNOPSIS
            Adds a new login to the SQL Instance.
 
        .DESCRIPTION
            The Add-SQLInstanceLogin cmdlet creates a new Windows User, Group, or SQL Login.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Name
            The name of the login to create. This should be specified as Domain\Username. If the user is a local user, specify COMPUTERNAME\Username. If the
 
        .PARAMETER LoginType
            The type of login to create, either WindowsUser (also used for groups) or SqlLogin. This defaults to WindowsUser.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Add-SQLInstanceLogin -ComputerName "SQL2014" -InstanceName "MyInstance" -Name "Contoso\UG-SQL-Users" -LoginType WindowsUser
             
            Creates a new login for the UG-SQL-Users group on the instance.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.Login
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Position=0 , Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$Name,

        [Parameter(Position=1)]
        [ValidateSet("WindowsUser", "SqlLogin")]
        [System.String]$LoginType = "WindowsUser",

        [Parameter()]
        [switch]$PassThru,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) 
        {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else 
        {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        [Microsoft.SqlServer.Management.Smo.Login]$NewLogin = New-Object Microsoft.SqlServer.Management.Smo.Login
        $NewLogin.Parent = $SqlConnection

        if (-not $PSBoundParameters.ContainsKey("LoginType"))
        {
            if ($SqlConnection.Settings.LoginMode -eq [Microsoft.SqlServer.Management.Smo.ServerLoginMode]::Mixed -or `
                $SqlConnection.Settings.LoginMode -eq [Microsoft.SqlServer.Management.Smo.ServerLoginMode]::Normal)
            {
                if ($Name -like "*\*")
                {
                    Write-Verbose -Message "$Name matches domain account structure."
                    $LoginType = "WindowsUser"
                }
                else 
                {
                    #Does the username resolve to an NT Account?
                    $Test = Get-AccountTranslatedNTName -UserName $Name -ComputerName $SqlConnection.NetName -Credential $Credential

                    if ([System.String]::IsNullOrEmpty($Test))
                    {
                        Write-Verbose -Message "$Name will be created as a Sql user."
                        $LoginType = "SqlLogin"
                    }
                    else
                    {
                        $LoginType = "WindowsUser"
                        $Name = $Test
                    }
                }
            }
            else
            {
                Write-Verbose -Message "SQL Authentication is not enabled, $Login being created as a windows user."
                $LoginType = "WindowsUser"
            }
        }

        switch ($LoginType.ToLower()) 
        {
            "windowsuser" {
                [System.String]$NTAccount = Get-AccountTranslatedNTName -UserName $Name -ComputerName $SqlConnection.NetName -Credential $Credential

                if ([System.String]::IsNullOrEmpty($NTAccount))
                {
                    throw "Could not translate $Name to a Windows NT Account."
                }

                $NewLogin.Name = $NTAccount
                $NewLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsUser
                break
            }
            "sqllogin" {
                $NewLogin.Name = $Name
                $NewLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
                break
            }
            default {
                throw "Could not determine login type $LoginType."
            }
        }

        $NewLogin.DefaultDatabase = "master"

        [System.String[]]$Logins = $SqlConnection.Logins | Where-NotMatchIn -Matches $script:IgnoreNames -Property "Name" | Select-Object -ExpandProperty Name

        if ($NewLogin.Name -inotin $Logins) 
        {
            Write-Verbose -Message "Creating login $($NewLogin.Name)."

            try 
            {
                $NewLogin.Create()
            }
            catch [Exception] 
            {
                if ($_.Exception.InnerException -ne $null) 
                {
                    $Exception = $_.Exception
                    while ($Exception.InnerException -ne $null) 
                    {                      
                        $Exception = $Exception.InnerException
                    }

                    Write-Error -Exception $Exception
                }
                else 
                {
                    Write-Error -Exception $_.Exception
                }

                $NewLogin = $null
            }
        }
        else 
        {
            Write-Verbose -Message "$($NewLogin.Name) login already exists."
            [Microsoft.SqlServer.Management.Smo.Login]$NewLogin = $SqlConnection.Logins | Where-Object { $_.Name -ieq $NewLogin.Name } | Select-Object -First 1
        }
    }

    End {
        if ($PassThru) 
        {
            Write-Output -InputObject $NewLogin
        }
    }
}

Function Add-SQLInstanceServerRole {
    <#
        .SYNOPSIS
            Adds a new role to the SQL Instance.
 
        .DESCRIPTION
            The Add-SQLInstanceRole cmdlet creates a Server Role.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Name
            The name of the role to create.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Add-SQLInstanceRole -ComputerName "SQL2014" -InstanceName "MyInstance" -Name "NEW_ROLE"
             
            Creates a new role named NEW_ROLE.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.ServerRole
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Position=0,Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$Name,

        [Parameter()]
        [switch]$PassThru,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        [System.String[]]$Roles = $SqlConnection.Roles | Where-Object {$_.IsFixedRole -eq $false } | Select-Object -ExpandProperty Name

         if ($Name -inotin $Roles) 
         {
            Write-Verbose -Message "Creating role $Name"
            [Microsoft.SqlServer.Management.Smo.ServerRole]$NewRole = New-Object Microsoft.SqlServer.Management.Smo.ServerRole($SqlConnection, $Name)

            #Sets the owner to the built in sa account
            [Microsoft.SqlServer.Management.Smo.Login]$LoggedInUser = $SqlConnection.Logins | Where-Object {$_.Name -eq $SqlConnection.ConnectionContext.TrueLogin}
                
            if ($LoggedInUser -ne $null)
            {
                #The user must be a member of sysadmin in order to make someone else an owner
                if (Test-SQLLoginIsSysAdmin -SqlServer $SqlConnection -UserName $LoggedInUser.Name)
                {
                    $NewRole.Owner = $SqlConnection.Logins | Where-Object {[System.Convert]::ToBase64String($_.Sid) -eq $script:SA_SID} | Select-Object -First 1 -ExpandProperty Name
                }
            }
            #Otherwise, the owner will be the user running the comand

            try {
                $NewRole.Create()
            }
            catch [Exception] {
                if ($_.Exception.InnerException -ne $null) {
                    $Exception = $_.Exception
                    while ($Exception.InnerException -ne $null) {                      
                        $Exception = $Exception.InnerException
                    }

                    Write-Error -Exception $Exception
                }
                else {
                    Write-Error -Exception $_.Exception
                }
            }
        }
        else {
            Write-Verbose -Message "$Name role already exists."
            [Microsoft.SqlServer.Management.Smo.ServerRole]$NewRole = $SqlConnection.Roles | Where-Object {$_.Name -ieq $Name } | Select-Object -First 1
        }

        if ($PassThru) {
            Write-Output -InputObject $NewRole
        }
    }

    End {        
    }
}

Function New-SQLInstanceJobCategory {
    <#
        .SYNOPSIS
            Adds a new job category to the SQL Instance.
 
        .DESCRIPTION
            The Add-SQLInstanceJobCategory cmdlet creates a new job category of use with SQL Agent jobs.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Name
            The name of the job category to create.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            New-SQLInstanceJobCategory -ComputerName "SQL2014" -InstanceName "MyInstance" -Name "STIG Audits"
             
            Creates a new job category named STIG Audits.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.Agent.JobCategory
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Position=0,Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$Name,

        [Parameter()]
        [switch]$PassThru,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        if (($SqlConnection.JobServer.JobCategories | Where-Object {$_.Name -ieq $Name}) -eq $null) {
            Write-Verbose -Message "Job category does not already exist, creating it."
            [Microsoft.SqlServer.Management.Smo.Agent.JobCategory]$Category = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Agent.JobCategory($SqlConnection.JobServer, $Name)
            $Category.CategoryType = [Microsoft.SqlServer.Management.Smo.Agent.CategoryType]::LocalJob
        
            try {
                $Category.Create()
            }
            catch [Exception] {
                if ($_.Exception.InnerException -ne $null) {
                    $Exception = $_.Exception
                    while ($Exception.InnerException -ne $null) {                      
                        $Exception = $Exception.InnerException
                    }

                    Write-Error -Exception $Exception
                }
                else {
                    Write-Error -Exception $_.Exception
                }
            }
        }
        else {
            Write-Verbose -Message "Job category already exists, returning it."
            [Microsoft.SqlServer.Management.Smo.Agent.JobCategory]$Category = $SqlConnection.JobServer.JobCategories | Where-Object {$_.Name -ieq $Name} | Select-Object -First 1
        }

        if ($PassThru) {
            Write-Output -InputObject $Category
        }
    }

    End {        
    }
}

Function New-SQLAgentJob {
    <#
        .SYNOPSIS
            Adds a new SQL Agent Job.
 
        .DESCRIPTION
            The New-SQLAgentJob cmdlet creates a new job.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Name
            The name of the job to create.
 
        .PARAMETER Description
            The description of the new job.
 
        .PARAMETER EmailLevel
            Defines when an operator is notified by email, the options are: Always, Never, OnFailure, OnSuccess. This defaults to Never.
 
        .PARAMETER EventLogLevel
            Defines when an event is written to the event log, the options are: Always, Never, OnFailure, OnSuccess. This defaults to Never.
 
        .PARAMETER NetSendLevel
            Defines when an operator is notified by NetSend, the options are: Always, Never, OnFailure, OnSuccess. This defaults to Never.
 
        .PARAMETER PageLevel
            Defines when an operator is notified by page, the options are: Always, Never, OnFailure, OnSuccess. This defaults to Never.
 
        .PARAMETER DeleteLevel
            Defines is the job is deleted after it runs, the options are: Always, Never, OnFailure, OnSuccess. This defaults to Never.
 
        .PARAMETER OperatorToEmail
            The name of the operator to send emails to.
 
        .PARAMETER OperatorToPage
            The name of the operator to page.
         
        .PARAMETER OperatorToNetSend
            The name of the operator to NetSend.
 
        .PARAMETER Owner
            The name of the SQL Login that should be the owner of the job. This defaults to the sa account.
 
        .PARAMETER JobCategory
            The name of the job category the job should be a part of. This defaults to the job category with ID 0.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            $FunctionCheckJob = New-SQLAgentJob -ComputerName "SQL2014 -InstanceName "MyInstance" `
                -Name "Alter_Function_Audit" `
                -Description "Daily audit to make sure no modifications to functions have been made." `
                -EmailLevel Always `
                -EventLogLevel OnFailure `
                -NetSendLevel Never `
                -PageLevel Never `
                -DeleteLevel Never `
                -OperatorToEmail "Database Administrators" `
                -JobCategory "STIG Audits"
 
            Creates a new job that will be used to check function modifications. This job still requires a job step and schedule to be created.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.Agent.Job
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Position=0,Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$Name,

        [Parameter(Position=1)]
        [System.String]$Description = [System.String]::Empty,

        [Parameter()]
        [ValidateSet("Always", "Never", "OnFailure", "OnSuccess")]
        [System.String]$EmailLevel = "Never",

        [Parameter()]
        [ValidateSet("Always", "Never", "OnFailure", "OnSuccess")]
        [System.String]$EventLogLevel = "Never",

        [Parameter()]
        [ValidateSet("Always", "Never", "OnFailure", "OnSuccess")]
        [System.String]$NetSendLevel = "Never",

        [Parameter()]
        [ValidateSet("Always", "Never", "OnFailure", "OnSuccess")]
        [System.String]$PageLevel = "Never",

        [Parameter()]
        [ValidateSet("Always", "Never", "OnFailure", "OnSuccess")]
        [System.String]$DeleteLevel = "Never",

        [Parameter()]
        [System.String]$OperatorToEmail = [System.String]::Empty,

        [Parameter()]
        [System.String]$OperatorToPage = [System.String]::Empty,

        [Parameter()]
        [System.String]$OperatorToNetSend = [System.String]::Empty,

        [Parameter()]
        [System.String]$Owner = [System.String]::Empty,

        [Parameter()]
        [System.String]$JobCategory = [System.String]::Empty,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        if (($SqlConnection.JobServer.Jobs | Where-Object {$_.Name -ieq $Name}) -eq $null) 
        {
            [Microsoft.SqlServer.Management.Smo.Agent.Job]$Job = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Agent.Job($SqlConnection.JobServer, $Name)
        
            if (![System.String]::IsNullOrEmpty($Description)) {
                $Job.Description = $Description
            }

            [bool]$IgnoreCase = $true

            if (![System.String]::IsNullOrEmpty($OperatorToEmail)) {
                [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]$Action = [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]::Never

                if ([System.Enum]::TryParse($EmailLevel, $IgnoreCase, [ref]$Action)) {
                    $Job.EmailLevel = $Action
                    $Job.OperatorToEmail = $OperatorToEmail
                }
                else {
                    $Job.EmailLevel = [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]::Never
                }
            }
            else {
                $Job.EmailLevel = [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]::Never
            }

            if (![System.String]::IsNullOrEmpty($OperatorToPage)) {

                if ([System.Enum]::TryParse($PageLevel, $IgnoreCase, [ref]$Action)) {
                    $Job.PageLevel = $Action
                    $Job.OperatorToPage = $OperatorToPage
                }
                else {
                    $Job.PageLevel = [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]::Never
                }
            }
            else {
                $Job.PageLevel = [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]::Never
            }

            if (![System.String]::IsNullOrEmpty($OperatorToNetSend)) {

                if ([System.Enum]::TryParse($NetSendLevel, $IgnoreCase, [ref]$Action)) {
                    $Job.NetSendLevel = $Action
                    $Job.OperatorToNetSend = $OperatorToNetSend
                }
                else {
                    $Job.NetSendLevel = [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]::Never
                }
            }
            else {
                $Job.NetSendLevel = [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]::Never
            }

            if ([System.Enum]::TryParse($EventLogLevel, $IgnoreCase, [ref]$Action)) {
                $Job.EventLogLevel = $Action
            }
            else {
                $Job.EventLogLevel = [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]::OnFailure
            }
        
            if ([System.Enum]::TryParse($DeleteLevel, $IgnoreCase, [ref]$Action)) {
                $Job.DeleteLevel = $Action
            }
            else {
                $Job.DeleteLevel = [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]::Never
            }        
        
            if (![System.String]::IsNullOrEmpty($JobCategory)) {
                [Microsoft.SqlServer.Management.Smo.Agent.JobCategory]$Category = $SqlConnection.JobServer.JobCategories | Where-Object {$_.Name -ieq $JobCategory} | Select-Object -First 1
            }

            if ($Category -eq $null -or [System.String]::IsNullOrEmpty($JobCategory)) {
                [Microsoft.SqlServer.Management.Smo.Agent.JobCategory]$Category = $SqlConnection.JobServer.JobCategories | Where-Object {$_.ID -eq 0} | Select-Object -First 1
            }

            $Job.Category = $Category.Name
        
            #Sets the owner to the built in sa account
            [Microsoft.SqlServer.Management.Smo.Login]$LoggedInUser = $SqlConnection.Logins | Where-Object {$_.Name -eq $SqlConnection.ConnectionContext.TrueLogin}
                
            if ($LoggedInUser -ne $null)
            {
                #The user must be a member of sysadmin in order to make someone else an owner
                if (Test-SQLLoginIsSysAdmin -SqlServer $SqlConnection -UserName $LoggedInUser.Name)
                {
                    if (![System.String]::IsNullOrEmpty($Owner)) {
                        $Job.OwnerLoginName = $Owner
                    }
                    else {
                        #Matching this against the SID is not returning the sa account
                        $Job.OwnerLoginName = $SqlConnection.Logins | Where-Object {[System.Convert]::ToBase64String($_.Sid) -eq $script:SA_SID} | Select-Object -First 1 -ExpandProperty Name
                    }
                }
            }            
            #Otherwise, the owner will be the user running the comand

            try {
                $Job.Create()
            }
            catch [Exception] {
                if ($_.Exception.InnerException -ne $null) {
                    $Exception = $_.Exception
                    while ($Exception.InnerException -ne $null) {                      
                        $Exception = $Exception.InnerException
                    }

                    Write-Error -Exception $Exception
                }
                else {
                    Write-Error -Exception $_.Exception
                }
            }
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Agent.Job]$Job = $SqlConnection.JobServer.Jobs | Where-Object {$_.Name -ieq $Name} | Select-Object -First 1
        }

        Write-Output -InputObject $Job
    }

    End {        
    }
}

Function New-SQLAgentJobStep {
    <#
        .SYNOPSIS
            Adds a new job step to a specified job.
 
        .DESCRIPTION
            The Add-SQLAgentJobStep cmdlet creates a new job step as part of an existing job.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Job
            The Job this step is being created for.
 
        .PARAMETER Name
            The name of the job step to create.
 
        .PARAMETER SubSystem
            The subsystem that will be used to run the step, the options are: "ActiveScripting", "AnalysisCommand", "AnalysisQuery", "CmdExec", "Distribution", "LogReader", "Merge", "PowerShell", "QueueReader", "Snapshot", "Ssis", "TransactSql".
 
            This defaults to TransactSql.
 
        .PARAMETER RetryAttempts
            The maximum number of times a job step is retried before it is returned with a completion status of failure. This defaults to 0.
 
        .PARAMETER RetryInterval
            The number of minutes that Microsoft SQL Server Agent waits before trying to execute a job step that has previously failed. This defaults to 0.
 
        .PARAMETER OSRunPriority
            The execution thread scheduling for job steps executing operating system tasks. This defaults to 0.
 
        .PARAMETER DatabaseName
            The name of the database to which the job step command execution is confined. This defaults to master.
 
        .PARAMETER DatabaseUser
            The database user account that the job step assumes when executing the command string.
     
        .PARAMETER OnFailAction
            The action to take when the job step finishes execution with failure. The options are "GoToNextStep", "GoToStep", "QuitWithFailure", "QuitWithSuccess". This defaults to QuitWithFailure.
 
        .PARAMETER OnSuccessAction
            The action to take when the job step finishes execution with success. The options are "GoToNextStep", "GoToStep", "QuitWithFailure", "QuitWithSuccess". This defaults to QuitWithSuccess.
 
        .PARAMETER Command
            The command execution string for the job step.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            $FunctionCheckJobStep = New-SQLAgentJobStep -ComputerName "SQL2014" -InstanceName "MyInstance" `
                -Name "Alter_Function_Audit_Step" `
                -Job $FunctionCheckJob `
                -DatabaseName "master" `
                -OnFailAction QuitWithFailure `
                -OnSuccessAction QuitWithSuccess `
                -RetryAttempts 0 `
                -RetryInterval 0 `
                -OSRunPriority 0 `
                -SubSystem TransactSql `
                -Command "PRINT('Ran job');"
             
            Creates a job step that prints the text "Ran job".
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.Agent.JobStep
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithJob")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithJob")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName = "NewConnectionWithName")] 
        [Parameter(ParameterSetName = "NewConnectionWithJob")]
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter(ParameterSetName="ExistingConnectionWithName",Mandatory=$true,Position=0)]
        [Parameter(ParameterSetName="ExistingConnectionWithJob",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName="ExistingConnectionWithName",Mandatory=$true,Position=1)]
        [Parameter(ParameterSetName="NewConnectionWithName",Mandatory=$true,Position=1)]
        [ValidateNotNullOrEmpty()]
        [System.String]$JobName,

        [Parameter(ParameterSetName="NewConnectionWithJob",Mandatory=$true,Position=1)]
        [Parameter(ParameterSetName="ExistingConnectionWithJob",Mandatory=$true,Position=1)]
        [Microsoft.SqlServer.Management.Smo.Agent.Job]$Job,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$Name,

        [Parameter()]
        [ValidateSet("ActiveScripting", "AnalysisCommand", "AnalysisQuery", "CmdExec", "Distribution", "LogReader", "Merge", "PowerShell", "QueueReader", "Snapshot", "Ssis", "TransactSql")]
        [System.String]$SubSystem = "TransactSql",

        [Parameter()]
        [ValidateScript({$_ -ge 0})]
        [System.Int32]$RetryAttempts = 0,

        [Parameter()]
        [ValidateScript({$_ -ge 0})]
        [System.Int32]$RetryInterval = 0,

        [Parameter()]
        [ValidateScript({$_ -ge 0})]
        [System.Int32]$OSRunPriority = 0,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$DatabaseName = "master",

        [Parameter()]
        [System.String]$DatabaseUser,

        [Parameter()]
        [ValidateSet("GoToNextStep", "GoToStep", "QuitWithFailure", "QuitWithSuccess")]
        [System.String]$OnFailAction = "QuitWithFailure",

        [Parameter()]
        [ValidateSet("GoToNextStep", "GoToStep", "QuitWithFailure", "QuitWithSuccess")]
        [System.String]$OnSuccessAction = "QuitWithSuccess",

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$Command
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -like "ExistingConnection*") {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        if ($PSCmdlet.ParameterSetName -like "*WithName") {
            [Microsoft.SqlServer.Management.Smo.Job]$Job = $SqlConnection.JobServer.Jobs | Where-Object {$_.Name -ieq $JobName} | Select-Object -First 1
            
            if ($Job -eq $null) {
                throw "$JobName does not match any existing jobs."
            }
        }

        [Microsoft.SqlServer.Management.Smo.Agent.JobStep]$JobStep = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Agent.JobStep($Job, $Name)

        [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]$SubSystemObject = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::TransactSql

        if ([System.Enum]::TryParse($SubSystem, [ref]$SubSystemObject)) {
            $JobStep.SubSystem = $SubSystemObject
        }
        else {
            $JobStep.SubSystem = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::TransactSql
        }

        [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]$Action = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithFailure

        if ([System.Enum]::TryParse($OnFailAction, [ref]$Action)) {
            $JobStep.OnFailAction = $Action
        }
        else {
            $JobStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithFailure
        }

        if ([System.Enum]::TryParse($OnSuccessAction, [ref]$Action)) {
            $JobStep.OnSuccessAction = $Action
        }
        else {
            $JobStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithSuccess
        }

        $JobStep.DatabaseName = $DatabaseName

        if (![System.String]::IsNullOrEmpty($DatabaseUser)) {
            $JobStep.DatabaseUser = $DatabaseUser
        }

        $JobStep.OSRunPriority = $OSRunPriority
        $JobStep.RetryAttempts = $RetryAttempts
        $JobStep.RetryInterval = $RetryInterval
        $JobStep.Command = $Command

        try {
            $JobStep.Create()
        }
        catch [Exception] {
            if ($_.Exception.InnerException -ne $null) {
                $Exception = $_.Exception
                while ($Exception.InnerException -ne $null) {                      
                    $Exception = $Exception.InnerException
                }

                Write-Error -Exception $Exception
            }
            else {
                Write-Error -Exception $_.Exception
            }
        }

        Write-Output -InputObject $JobStep
    }

    End {        
    }
}

<#
    WeekDays.Sunday = 1
    WeekDays.Monday = 2
    WeekDays.Tuesday = 4
    WeekDays.Wednesday = 8
    WeekDays.Thursday = 16
    WeekDays.Friday = 32
    WeekDays.Saturday = 64
    WeekDays.WeekDays = 62
    WeekDays.WeekEnds = 65
    WeekDays.EveryDay = 127
#>

Function New-SQLAgentJobSchedule {
    <#
        .SYNOPSIS
            Adds a new schedule to a specified job.
 
        .DESCRIPTION
            The Add-SQLAgentJobSchedule cmdlet creates a new schedule to run a specified job.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Job
            The Job this schedule is being created for.
 
        .PARAMETER JobName
            The name of the job this schedule is being created for.
 
        .PARAMETER Name
            The name of the schedule to be created.
 
        .PARAMETER ActiveEndDate
            The date and time when the schedule ends.
 
        .PARAMETER ActiveEndTimeOfDay
            The time when the job schedule stops for the day.
 
        .PARAMETER ActiveStartDate
            The date and time when the schedule starts. This defaults to now.
 
        .PARAMETER ActiveStartTimeOfDay
            The time when the job schedule starts for the day.
 
        .PARAMETER FrequencyInterval
            The frequency interval, which determines how often the job is scheduled to run. https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.jobschedule.frequencyinterval.aspx
 
        .PARAMETER FrequencyType
            The way in which frequency is evaluated for the job schedule, whether it's one time only, or weekly, or when the processor is idle, for example. The options are: "AutoStart", "Daily", "Monthly", "MonthlyRelative", "OneTime", "OnIdle", "Unknown", "Weekly".
 
            This defaults to Unknown.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
             
        .EXAMPLE
            $FunctionCheckSchedule = New-SQLAgentJobSchedule -ComputerName "SQL2014" -InstanceName "MyInstance" `
                -Name "Daily" `
                -Job $FunctionCheckJob `
                -ActiveStartTimeOfDay (New-Object System.TimeSpan(2, 0, 0)) `
                -FrequencyType Daily `
                -FrequencyInterval 1
             
            Creates a schedule that runs daily at 2AM.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.Agent.JobSchedule
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithJob")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithJob")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithJob")]
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter(ParameterSetName="ExistingConnectionWithName",Mandatory=$true,Position=0)]
        [Parameter(ParameterSetName="ExistingConnectionWithJob",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Position=2,Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$Name,

        [Parameter(ParameterSetName="ExistingConnectionWithName",Mandatory=$true,Position=1)]
        [Parameter(ParameterSetName="NewConnectionWithName",Mandatory=$true,Position=1)]
        [ValidateNotNullOrEmpty()]
        [System.String]$JobName,

        [Parameter(ParameterSetName="NewConnectionWithJob",Mandatory=$true,Position=1)]
        [Parameter(ParameterSetName="ExistingConnectionWithJob",Mandatory=$true,Position=1)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Agent.Job]$Job,

        [Parameter()]
        [System.DateTime]$ActiveEndDate = [System.DateTime]::MinValue,

        [Parameter()]
        [System.TimeSpan]$ActiveEndTimeOfDay = [System.TimeSpan]::Zero,

        [Parameter()]
        [System.DateTime]$ActiveStartDate = [System.DateTime]::Now,

        [Parameter(Mandatory=$true)]
        [System.TimeSpan]$ActiveStartTimeOfDay,

        [Parameter()]
        [System.Int32]$FrequencyInterval,

        [Parameter()]
        [ValidateSet("AutoStart", "Daily", "Monthly", "MonthlyRelative", "OneTime", "OnIdle", "Unknown", "Weekly")]
        [System.String]$FrequencyType = "Unknown"        
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -like "ExistingConnection*") {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        if ($PSCmdlet.ParameterSetName -like "*WithName") {
            [Microsoft.SqlServer.Management.Smo.Agent.Job]$Job = $SqlConnection.JobServer.Jobs | Where-Object {$_.Name -ieq $JobName} | Select-Object -First 1
            
            if ($Job -eq $null) {
                throw "$JobName does not match any existing jobs."
            }
        }

        [Microsoft.SqlServer.Management.Smo.Agent.JobSchedule]$Schedule = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Agent.JobSchedule($Job, $Name)

        [Microsoft.SqlServer.Management.Smo.Agent.FrequencyTypes]$Freq = [Microsoft.SqlServer.Management.Smo.Agent.FrequencyTypes]::Unknown

        if ([System.Enum]::TryParse($FrequencyType, [ref]$Freq)) {
            $Schedule.FrequencyTypes = $Freq
        }
        else {
            throw "Could not determine the frequency type given by $FrequencyType."
        }

        $Schedule.ActiveStartDate = $ActiveStartDate
        $Schedule.ActiveStartTimeOfDay = $ActiveStartTimeOfDay
        if ($ActiveEndDate -gt [System.DateTime]::MinValue -and $ActiveEndTimeOfDay -ne $null) {
            $Schedule.ActiveEndDate = $ActiveEndDate
            $Schedule.ActiveEndTimeOfDay = $ActiveEndTimeOfDay
        }
            
        $Schedule.FrequencyInterval = $FrequencyInterval

        try {
            $Schedule.Create()
        }
        catch [Exception] {
            if ($_.Exception.InnerException -ne $null) {
                $Exception = $_.Exception
                while ($Exception.InnerException -ne $null) {                      
                    $Exception = $Exception.InnerException
                }

                Write-Error -Exception $Exception
            }
            else {
                Write-Error -Exception $_.Exception
            }
        }

        Write-Output -InputObject $Schedule
    }

    End {    
    }
}

Function Set-SQLDatabaseTrustworthy {
    <#
        .SYNOPSIS
            Sets the trustworthy setting on a database.
 
        .DESCRIPTION
            The Set-SQLDatabaseTrustworthy cmdlet modifies the trustworthy setting. The SQL Server instance uses each database's TRUSTWORTHY property to guard against tampering that could enable
            unwarranted privilege escalation. When TRUSTWORTHY is 0/False/Off, SQL Server prevents the database from accessing resources in other databases.
            When TRUSTWORTHY is 1/True/On, SQL Server permits access to other databases (subject to other protections). SQL Server sets TRUSTWORTHY OFF when it creates a new database.
            SQL Server forces TRUSTWORTHY OFF, irrespective of its prior value, when an existing database is attached to it, to address the possibility that an adversary may have tampered with the database, introducing malicious code.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER DatabaseName
            The name of the database to modify.
 
        .PARAMETER Enabled
            Specifies whether this setting should be enabled or disabled.
 
        .PARAMTER PassThru
            Returns an object representing the item property. By default, this cmdlet does not generate any output.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Set-SQLDatabaseTrustworthy -ComputerName "SQL2014" -InstanceName "MyInstance" -DatabaseName "master" -Enabled $false
             
            Sets the trustworthy setting on the master database to disabled.
 
        .INPUTS
            None
 
        .OUTPUTS
            None or System.Boolean
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
 
        .FUNCTIONALITY
            STIG
                Microsoft SQL Server 2014 Datavase V1R0
            STIG ID
                SQL4-00-015610
            Rule ID
                SQL4-00-015610_rule
            Vuln ID
                SQL4-00-015610
            Severity
                CAT II
 
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$DatabaseName,

        [Parameter()]
        [System.Boolean]$Enabled = $false,

        [Parameter()]
        [switch]$PassThru,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        Write-Verbose -Message "Setting Database Trustworthy."
        Write-Verbose -Message "STIG ID: SQL4-00-015610"

        if ($DB.Name -eq "msdb" -and $Enabled -eq $false) {
            Write-Warning -Message "msdb database is required to be set to trustworthy, this cmdlet will not modify this setting."
        }
        else {
            if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
                [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
            }
            else {
                [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
            }

            [Microsoft.SqlServer.Management.Smo.Database]$DB = $SqlConnection.Databases | Where-Object {$_.Name -eq $DatabaseName} | Select-Object -First 1
            $DB.Trustworthy = $Enabled
        }

        if ($PassThru) {
            Write-Output -InputObject $DB.Trustworthy
        }
    }

    End {
    }
}

Function Get-SQLInstanceServerRoleMembership {
    <#
        .SYNOPSIS
            Gets the Server Role membership of a specified principal.
 
        .DESCRIPTION
            The Get-SQLInstanceServerRoleMembership cmdlet gets all of the Server Roles that a principal is a member of.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER PrincipalName
            The name of the principal to get role membership of.
 
        .PARAMETER OnlyFixedRoles
            Specifies is only the membership in fixed roles should be returned as a result.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Get-SQLInstanceServerRoleMembership -ComputerName "SQL2014" -InstanceName "MyInstance" -PrincipalName "sa"
             
            Gets all of the server role membership for the sa account.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.ServerRole[]
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$PrincipalName,

        [Parameter()]
        [switch]$OnlyFixedRoles,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        Write-Verbose -Message "Getting roles for $PrincipalName."

        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        [Microsoft.SqlServer.Management.Smo.ServerRole[]]$Roles = @()

        if ($OnlyFixedRoles) {
            foreach($Role in ($SqlConnection.Roles | Where-Object { $_.IsFixedRole -eq $true } )) {
                Write-Verbose "Checking role $($Role.Name)."
                if ($Role.EnumMemberNames() -icontains $PrincipalName) {
                    $Roles += $Role
                }
            }
        }
        else {
            foreach($Role in ($SqlConnection.Roles | Where-Object { $_.IsFixedRole -eq $true } )) {
                Write-Verbose -Message "Checking role $($Role.Name)."
                if ($Role.EnumMemberNames() -icontains $PrincipalName) {
                    $Roles += $Role
                }
            }
        }

        Write-Output -InputObject $Roles
    }

    End {     
    }
}

Function New-SQLDatabaseDDLTrigger {
    <#
        .SYNOPSIS
            Creates a new database DDL trigger.
 
        .DESCRIPTION
            The New-SQLDatabaseDDLTrigger cmdlet creates a new DDL trigger on a specified database.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Name
             Specifies the name of the DDL trigger
 
        .PARAMETER DatabaseName
            Specifies the database on which the database DDL trigger is created.
 
        .PARAMETER CommandText
            Specifies the text body that is used to define the DDL trigger.
 
        .PARAMETER EventSet
            A Microsoft.SqlServer.Management.Smo.DatabaseDdlTriggerEventSet object specifying the events that should be triggered on.
 
        .PARAMETER ImplementationType
            The implementation type for the database data definition language (DDL) trigger. The options are SqlClr or TransactSql. This defaults to TransactSql.
 
        .PARAMETER Owner
            The database user that is the context used when the database DDL trigger executes. This defaults to the Sql Agent service account.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            [Microsoft.SqlServer.Management.Smo.DatabaseDdlTriggerEventSet]$EventSet = New-Object -TypeName Microsoft.SqlServer.Management.Smo.DatabaseDdlTriggerEventSet
            $EventSet.CreateProcedure = $true
 
            New-SQLDatabaseDDLTrigger -ComputerName "SQL2014" -InstanceName "MyInstance" `
                                    -Name "AuditModificationsTrigger" `
                                    -DatabaseName "master" `
                                    -CommandText "PRINT('Procedure created.')" `
                                    -EventSet $EventSet `
                                    -ImplementationType TransactSql
             
            Creates a DDL trigger on the master database that fires anytime a stored procedure is created on that database.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.DatabaseDdlTrigger
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$Name,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$DatabaseName,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$CommandText,

        [Parameter(Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.DatabaseDdlTriggerEventSet]$EventSet,

        [Parameter()]
        [Validateset("SqlClr", "TransactSql")]
        [System.String]$ImplementationType = "TransactSql",

        [Parameter()]
        [System.String]$Owner,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        [Microsoft.SqlServer.Management.Smo.Database]$DB = $SqlConnection.Databases | Where-Object {$_.Name -ieq $DatabaseName} | Select-Object -First 1

        if ($DB -ne $null) {
            if (($DB.Triggers | Where-Object {$_.Name -ieq $Name}) -eq $null) {
                Write-Verbose -Message "Trigger does not already exist, creating it."

                [Microsoft.SqlServer.Management.Smo.DatabaseDdlTrigger]$Trigger = New-Object -TypeName Microsoft.SqlServer.Management.Smo.DatabaseDdlTrigger($DB, $Name, $EventSet, $CommandText)
                [Microsoft.SqlServer.Management.Smo.ImplementationType]$Impl = [Microsoft.SqlServer.Management.Smo.ImplementationType]::TransactSql

                if([System.Enum]::TryParse($ImplementationType, [ref]$Impl)) {
                    $Trigger.ImplementationType = $Impl
                }
                else {
                    $Trigger.ImplementationType = [Microsoft.SqlServer.Management.Smo.ImplementationType]::TransactSql
                }

                if ([System.String]::IsNullOrEmpty($Owner)) {
                    $Owner = $SqlConnection.JobServer.ServiceAccount
                }

                $Trigger.TextMode = $false
                $Trigger.ExecutionContext = [Microsoft.SqlServer.Management.Smo.ExecutionContext]::Owner
                $Trigger.ExecutionContextUser = $Owner

                try {
                    $Trigger.Create()
                }
                catch [Exception] {
                    if ($_.Exception.InnerException -ne $null) {
                        $Exception = $_.Exception
                        while ($Exception.InnerException -ne $null) {                      
                            $Exception = $Exception.InnerException
                        }

                        Write-Error -Exception $Exception
                    }
                    else {
                        Write-Error -Exception $_.Exception
                    }
                }
            }
            else {
                Write-Verbose -Message "Trigger already exists in $($DB.Name), returning it."
                $Trigger = $DB.Triggers | Where-Object {$_.Name -ieq $Name} | Select-Object -First 1
            }
        }
        else {
            Write-Error -Message "Database $DatabaseName could not be found."
        }

        if ($Trigger -ne $null) {
            Write-Output -InputObject $Trigger
        }
    }

    End {
    }
}

Function Get-SQLInstanceErrorLogPath {
    <#
        .SYNOPSIS
            Gets the SQL Instance error log path.
 
        .DESCRIPTION
            The Get-SQLInstanceErrorLogPath cmdlet gets the default error log path for the instance.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Get-SQLInstanceErrorLogPath -ComputerName "SQL2014" -InstanceName "MyInstance"
             
            Gets the error log path for the MyInstance SQL Instance.
 
        .INPUTS
            None
 
        .OUTPUTS
            System.String
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true,Position=0)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {        
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        Write-Output -InputObject $SqlConnection.ErrorLogPath
    }

    End {        
    }
}

#endregion

#region Utility Functions

Function Get-SQLServer {
    <#
        .SYNOPSIS
            Connects to a provided SQL instance with SMO.
 
        .DESCRIPTION
            The Get-SQLServer cmdlet connects to the specified SQL Server and Instance and provides a Microsoft.SqlServer.Management.Smo.Server object back to use for futher actions.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER Port
            The port the instance is running on. If this parameter is not defined, the cmdlet uses the SQL Browser service to identify the correct port.
 
        .PARAMETER InitialCatalog
            The initial database to connect to. This defaults to master.
 
        .PARAMETER Credential
            The credential to use to connect to the database if SQL Authentication is being used. Otherwise Windows Integrated Authentication with SSPI is used.
     
        .EXAMPLE
            $Server = Get-SQLServer -ComputerName "SQL2014" -InstanceName "MyInstance"
             
            Connects to the specified SQL Instance and provides back a server object.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.Server
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(Position=0)]
        [System.String]$ComputerName = "localhost",

        [Parameter(Position=1)]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(Position=2)]
        [System.Int32]$Port = -1,

        [Parameter(Position=3)]
        [ValidateNotNullOrEmpty()]
        [System.String]$InitialCatalog = "master",

        [Parameter(Position=4)]
        [ValidateNotNull()]
        [System.Management.Automation.Credential()]
        [System.Management.Automation.PSCredential]$Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {        
    }

    Process {
        if ([System.String]::IsNullOrEmpty($ComputerName)) {
            $ComputerName = "localhost"
        }

        if([System.String]::IsNullOrEmpty($InstanceName)) {
            $InstanceName = "MSSQLSERVER"
        }

        [Microsoft.SqlServer.Management.Common.ServerConnection]$Connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection

        if ($InstanceName -ieq "MSSQLSERVER") {
            Write-Verbose -Message "Using the default SQL Instance."
            $Connection.ServerInstance = $ComputerName
            $ConnectionString = "Data Source = $ComputerName"
        }
        else {
            Write-Verbose -Message "Using a named SQL Instance, $InstanceName."
            $Connection.ServerInstance = "$ComputerName\$InstanceName"
            $ConnectionString = "Data Source = $ComputerName\$InstanceName"
        }

        if ($Port -gt 0) {
            Write-Verbose -Message "Using a defined port, $Port."
            $Connection.ServerInstance += ",$Port"
            $ConnectionString += ",$Port;"
        }
        else {
            Write-Verbose -Message "Using the SQL Browser service to determine the port."
            $ConnectionString += ";"
        }
        
        $Connection.DatabaseName = $InitialCatalog
        $ConnectionString += " Initial Catalog=$InitialCatalog;"

        if ($Credential -eq [System.Management.Automation.PSCredential]::Empty) {
            Write-Verbose -Message "Using SSPI Integrated Security."
            $ConnectionString += " Integrated Security=SSPI;"
            $Connection.LoginSecure = $true
        }
        else {
            Write-Verbose -Message "Using UserName and Password to connect."
            $Connection.LoginSecure = $false
            $Connection.Login = $Credential.UserName
            $Connection.SecurePassword = $Credential.Password

            $ConnectionString += " Integrated Security = false; User ID = $($Credential.UserName); Password=$(New-Object -TypeName System.String('*',$Credential.Password.Length));"
        }
        
        Write-Verbose -Message "Connection String:`n`t$ConnectionString"

        [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = New-Object Microsoft.SqlServer.Management.Smo.Server($Connection)

        Write-Output -InputObject $SqlConnection
    }

    End {      
    }
}

Function Get-SQLInstanceVersion {
    <#
        .SYNOPSIS
            Gets version information about the provided SQL instance.
 
        .DESCRIPTION
            The cmdlet gets version information about the provided SQL instance including version number, edition, and patch level.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER ShortVersionOnly
            Just returns the 2 digit number corresponding to the major SQL version.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
             
        .INPUTS
            None
 
        .OUTPUTS
            System.Int
 
            System.Management.Automation.PSCustomObject
 
        .EXAMPLE
            Get-SQLInstanceVersion
 
            Gets the instance version for the MSSQLSERVER instance on localhost.
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter()]
        [switch]$ShortVersionOnly,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {        
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        if ($ShortVersionOnly) {
            $Result = $SqlConnection.VersionMajor
        }
        else {        
            $Result = [PSCustomObject]@{
                Version = $SqlConnection.VersionString;
                ShortVersion = $SqlConnection.VersionMajor;
                Edition = $SqlConnection.Edition;
                ProductLevel = $SqlConnection.ProductLevel;
                OSVersion = $SqlConnection.OSVersion;
            }
        }

        Write-Output -InputObject $Result
    }

    End {        
    }
}

Function Get-SQLInstanceDetails {
    <#
        .SYNOPSIS
            Gets a core set of details about the provided SQL instance.
 
        .DESCRIPTION
            The cmdlet gets details about data directories, the instance version, the db enginer and sql agent service accounts and SIDs, and the installation path.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
             
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .INPUTS
            None
 
        .OUTPUTS
            System.Management.Automation.PSCustomObject
 
        .EXAMPLE
            Get-SQLInstanceDetails
 
            Gets the instance details for the MSSQLSERVER instance on localhost.
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        $Version = Get-SQLInstanceVersion -ShortVersionOnly -SqlServer $SqlConnection
        $SqlServiceAccount = $SqlConnection.ServiceAccount
        $SqlAgentServiceAccount = $SqlConnection.JobServer.ServiceAccount
        $ServiceAccountSid = Get-AccountSid -UserName $SqlServiceAccount -ComputerName $SqlConnection.NetName -Credential $Credential
        $SqlAgentAccountSid = Get-AccountSid -UserName $SqlAgentServiceAccount -ComputerName $SqlConnection.NetName -Credential $Credential

        $Directories = Get-SQLInstanceDataDirectories -SqlServer $SqlConnection

        $Result = [PSCustomObject]@{
            Version = $Version;
            DBEngineServiceAccount = $SqlServiceAccount;
            DBEngineServiceAccountSid = $ServiceAccountSid;
            SqlAgentServiceAccount = $SqlAgentServiceAccount;
            SqlAgentServiceAccountSid = $SqlAgentAccountSid;
        }

        $DataDirectories = @()
        foreach ($Directory in $Directories) {
            Add-Member -InputObject $Result -MemberType NoteProperty -Name $Directory -Value $Directories.$Directory
            $DataDirectories += $Directories.$Directory
        }

        Add-Member -InputObject $Result -MemberType NoteProperty -Name "DataDirectories" -Value $DataDirectories

        Write-Output -InputObject $Result
    }

    End {        
    }
}

Function Get-SQLInstanceDataDirectories {
    <#
        .SYNOPSIS
            Gets the data directories used by the SQL instance.
 
        .DESCRIPTION
            The cmdlet gets the default data path, default log path, the tempDB path (if different than the default data path), the backup directory, and the SQL root directory.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
 
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
             
        .INPUTS
            None
 
        .OUTPUTS
            System.Management.Automation.PSCustomObject
 
        .EXAMPLE
            Set-SQLInstanceDataDirectories
 
            Gets the data directories for the MSSQLSERVER instance on localhost.
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {
    }
    
    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        $DataRoot = $SqlConnection.RootDirectory
        $DefaultData = $SqlConnection.DefaultFile
        $DefaultLog = $SqlConnection.DefaultLog
        $BackupDirectory = $SqlConnection.BackupDirectory
        $InstallationDirectory = $SqlConnection.InstallDataDirectory
        $TempDBPath = $SqlConnection.Databases | Where-Object {$_.Name -eq "tempdb" } | Select-Object -ExpandProperty PrimaryFilePath
        $MasterDBPath = $SqlConnection.MasterDBPath
        $MasterDBLogPath = $SqlConnection.MasterDBLogPath
        $SqlProgramDir = [System.IO.Directory]::GetParent([System.IO.Directory]::GetParent($SqlConnection.InstallDataDirectory)).FullName

        $Directories = [PSCustomObject]@{SqlProgramDir = $SqlProgramDir; DefaultDataPath = $DefaultData; DefaultLogPath = $DefaultLog; BackupDirectory = $BackupDirectory; DataRootPath = $DataRoot; TempDBPath = $TempDBPath; MasterDBPath = $MasterDBPath; MasterDBLogPath = $MasterDBLogPath;}

        Write-Output -InputObject $Directories
    }

    End {        
    }
}

Function Enable-SQLInstanceDatabaseMail {
    <#
        .SYNOPSIS
            Enables database mail.
 
        .DESCRIPTION
            Enables database mail.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .PARAMETER EnableLogging
            Enables database mail logging.
 
        .EXAMPLE
            Enable-SQLInstanceDatabaseMail -ComputerName "SQL2014" -InstanceName "MyInstance"
             
            Enables database mail.
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter()]
        [switch]$EnableLogging
    )

    Begin {       
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        $SqlConnection.Configuration.DatabaseMailEnabled.ConfigValue = 1
        $SqlConnection.Configuration.Alter()

        if ($EnableLogging)
        {
            $SqlConnection.Mail.ConfigurationValues.Item("LoggingLevel").Value = 1
            $SqlConnection.Mail.ConfigurationValues.Item("LoggingLevel").Alter()
        }
    }

    End {        
    }
}

Function New-SQLInstanceDatabaseMailSmtpAccount {
    <#
        .SYNOPSIS
            Creates a database mail smtp account.
 
        .DESCRIPTION
            Creates a new database mail account with a single SMTP server.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .PARAMETER OriginatingAddress
            The email address used as the origin for database emails.
 
        .PARAMETER ReplyToAddress
            The email address used for replies from database emails.
 
        .PARAMETER SmtpServer
            The smtp server address used to send emails. This defaults to the local server.
 
        .PARAMETER AccountDescription
            The description for the account, this defaults to "The default smtp mail account.".
 
        .PARAMETER AccountName
            The name for the account. This default to "<The smtp server name> Mail Account".
 
        .PARAMETER EnableSSL
            Enables SSL for the SMTP connections.
 
        .PARAMETER Port
            The port to use to connect to the smtp server, this defaults to 25. 587 is the port for SMTPS.
 
        .PARAMETER SmtpCredential
            The credentials to use to make authenticated SMTP connections.
 
        .EXAMPLE
            New-SQLInstanceDatabaseMailSmtpAccount -ComputerName "SQL2014" -InstanceName "MyInstance" -SmtpServer "smtp.google.com" -ReplyToAddress "noreply@contoso.com" -OriginatingAddress "sql@contoso.com"
             
            Creates a new smtp account to use with database mail pointing to smtp.google.com using the default port of 25 and anonymous authentication.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.Mail.MailAccount
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter()]
        [ValidateNotNull()]
        [System.String]$OriginatingAddress = [System.String]::Empty,

        [Parameter()]
        [ValidateNotNull()]
        [System.String]$ReplyToAddress = [System.String]::Empty,

        [Parameter()]
        [ValidateNotNull()]
        [System.String]$SmtpServer = [System.String]::Empty,

        [Parameter()]
        [ValidateNotNull()]
        [System.String]$AccountDescription = "The default smtp mail account.",

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$AccountName = "$SmtpServer Mail Account",

        [Parameter()]
        [switch]$EnableSSL,

        [Parameter()]
        [ValidateRange(0, 65534)]
        [System.Int32]$Port = 25,

        [Parameter()] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $SmtpCredential = [System.Management.Automation.PSCredential]::Empty

    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        $AccountName = $AccountName.Trim()
        
        if (-not $SqlConnection.Mail.Accounts.Contains($AccountName))
        {
            if (![System.String]::IsNullOrEmpty($SmtpServer))
            {
                $DisplayName = $SmtpServer
            }
            else
            {
                $DisplayName = $SqlConnection.NetName
            }
        
            if ([System.String]::IsNullOrEmpty($OriginatingAddress))
            {
                $OriginatingAddress = "noreply@$env:COMPUTERNAME.com"
            }

            [Microsoft.SqlServer.Management.Smo.Mail.MailAccount]$MailAccount = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Mail.MailAccount($SqlConnection.Mail, $AccountName, $Description, $DisplayName, $OriginatingAddress)

            if (![System.String]::IsNullOrEmpty($ReplyToAddress)) 
            {
                $MailAccount.ReplyToAddress = $ReplyToAddress
            }

            $MailAccount.Create()

            [Microsoft.SqlServer.Management.Smo.Mail.MailServer]$Server = $MailAccount.MailServers[0]

            if (![System.String]::IsNullOrEmpty($SmtpServer))
            {
                $Server.Rename($SmtpServer)
            }
        
            if ($EnableSSL)
            {
                $Server.EnableSsl = $true
            }

            $Server.Port = $Port

            if ($SmtpCredential -ne [System.Management.Automation.PSCredential]::Empty)
            {
                $Server.SetAccount($SmtpCredential.UserName, $SmtpCredential.Password)
            }

            $Server.Alter()
            $MailAccount.Alter()
        }
        else
        {
            Write-Verbose -Message "The smtp account $MailAccount already exists."
            [Microsoft.SqlServer.Management.Smo.Mail.MailAccount]$MailAccount = $SqlConnection.Mail.Accounts | Where-Object {$_.Name -eq $AccountName} | Select-Object -First 1
        }

        Write-Output -InputObject $MailAccount
    }

    End {
    }
}

Function New-SQLInstanceDatabaseMailProfile {
    <#
        .SYNOPSIS
            Creates a new database mail profile.
 
        .DESCRIPTION
            Creates a public, default database mail profile and associates the provided database mail smtp account with it.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .PARAMETER InputObject
            The mail account object to associate with the new profile.
 
        .PARAMETER MailAccount
            The name of the mail account to associate with the new profile.
 
        .PARAMETER ProfileName
            The name of the profile, this defaults to "Default Mail Profile".
 
        .PARAMETER ProfileDescription
            The description for the profile, this defaults to "The default mail profile.".
 
        .EXAMPLE
            New-SQLInstanceDatabaseMailProfile -ComputerName "SQL2014" -InstanceName "MyInstance" -MailAccount "Default Mail Account"
             
            Creates a new public mail profile associated with the "Default Mail Account" smtp account.
 
        .INPUTS
            None
 
        .OUTPUTS
            Microsoft.SqlServer.Management.Smo.Mail.MailProfile
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithInput")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithInput")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnectionWithName",Mandatory=$true)]
        [Parameter(ParameterSetName="ExistingConnectionWithInput",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName="NewConnectionWithName")]
        [Parameter(ParameterSetName="NewConnectionWithInput")]
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,
        
        [Parameter(ParameterSetName = "NewConnectionWithInput", Mandatory = $true, ValueFromPipeline = $true, Position = 0)]
        [Parameter(ParameterSetName = "ExistingConnectionWithInput", Mandatory = $true, ValueFromPipeline = $true, Position = 0)]
        [Microsoft.SqlServer.Management.Smo.Mail.MailAccount]$InputObject,

        [Parameter(ParameterSetName = "NewConnectionWithName", Mandatory = $true, ValueFromPipeline = $true, Position = 0)]
        [Parameter(ParameterSetName = "ExistingConnectionWithName", Mandatory = $true, ValueFromPipeline = $true, Position = 0)]
        [System.String]$MailAccount,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]$ProfileName = "Default Mail Profile",

        [Parameter()]
        [ValidateNotNull()]
        [System.String]$ProfileDescription = "The default mail profile."
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -like "ExistingConnection*") {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        if ($PSCmdlet.ParameterSetName -like "*WithInput")
        {
            $MailAccount = $InputObject.Name
        }

        if (-not $SqlConnection.Mail.Profiles.Contains($ProfileName))
        {
            Write-Verbose -Message "Creating a new mail profile for $ProfileName."
            [Microsoft.SqlServer.Management.Smo.Mail.MailProfile]$Profile = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Mail.MailProfile($SqlConnection.Mail, $ProfileName, $ProfileDescription)
            $Profile.Create()
            
            [System.Boolean]$IsDefaultProfile = $true
            $Profile.AddPrincipal("public", $IsDefaultProfile)        
        }
        else
        {
            Write-Verbose -Message "Mail profile already exists."
            [Microsoft.SqlServer.Management.Smo.Mail.MailProfile]$Profile = $SqlConnection.Mail.Profiles | Where-Object {$_.Name -eq $ProfileName} | Select-Object -First 1
        }

        if (($Profile.EnumAccounts() | Select-Object -ExpandProperty AccountName) -notcontains $MailAccount)
        {
            Write-Verbose -Message "Adding the mail account $MailAccount to the profile."
            $Profile.AddAccount($MailAccount, 0)
            $Profile.Alter()
        }
        else
        {
            Write-Verbose -Message "The profile already has the mail account associated with it."
        }
        
        Write-Output -InputObject $Profile
    }

    End {
    }
}

Function Set-SQLInstanceAgentMail {
    <#
        .SYNOPSIS
            Configures the SQL agent to use database mail.
 
        .DESCRIPTION
            Configures the SQL agent to use database mail and provides it a profile to use.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .PARAMETER ProfileName
            The name of the profile for the SQL agent to use.
 
        .EXAMPLE
            Set-SQLInstanceAgentMail -ComputerName "SQL2014" -InstanceName "MyInstance" -ProfileName "Default Mail Profile"
             
            Sets the SQL Agent to use the "Default Mail Profile"
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter(Mandatory = $true)]
        $ProfileName
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        Write-Verbose -Message "Setting SQL Agent to use mail profile $ProfileName."

        Wait-SQLAgentService -SqlServer $SqlConnection | Out-Null

        $SqlConnection.JobServer.AgentMailType = [Microsoft.SqlServer.Management.Smo.Agent.AgentMailType]::DatabaseMail
        $SqlConnection.JobServer.DatabaseMailProfile = $ProfileName
        $SqlConnection.JobServer.Alter()
    }

    End {
    }
}

Function Wait-SQLAgentService {
    <#
        .SYNOPSIS
            Waits the for the SQL agent service to start.
 
        .DESCRIPTION
            Test the SQL Agent service to see if it is running, starts it if it is not, and waits for it to enter a running state.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .PARAMETER Timeout
            The amount of time to wait for the service to start. This defaults to 30 seconds.
 
        .PARAMETER DoNotStart
            Specify to only wait for a pending start and not initiate a start service request.
 
        .EXAMPLE
            Wait-SQLAgentService -ComputerName "SQL2014" -InstanceName "MyInstance"
             
            Waits for the agent service to start on the SQL2014 computer
 
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter()]
        [System.Int32]$Timeout = 30,

        [Parameter()]
        [switch]$DoNotStart
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        [System.String]$Instance = "MSSQLSERVER"

        if (-not [System.String]::IsNullOrEmpty($SqlConnection.InstanceName))
        {
            $Instance = $SqlConnection.InstanceName
        }

        [System.String]$SqlAgent = [System.String]::Empty

        if ($Instance -eq "MSSQLSERVER")
        {
            $SqlAgent = "SQLSERVERAGENT"
        }
        else
        {
            $SqlAgent = "SQLAgent`$$Instance"
        }

        $SqlAgentService = Get-Service -Name $SqlAgent

        if ($SqlAgentService -ne $null)
        {
            if ($SqlAgentService.Status -ne [System.ServiceProcess.ServiceControllerStatus]::Running)
            {
                Write-Verbose -Message "SQL Agent service not running, it is $($SqlAgentService.Status)"

                if ($SqlAgentService.Status -eq [System.ServiceProcess.ServiceControllerStatus]::StartPending)
                {
                    Write-Verbose -Message "SQL Agent service account is start pending, will wait up to 30 seconds for this to complete"

                    $Counter = 0

                    while ($Counter -lt $Timeout)
                    {
                        Start-Sleep -Seconds 1

                        $SqlAgentService = Get-Service -Name $SqlAgent

                        if ($SqlAgentService.Status -eq [System.ServiceProcess.ServiceControllerStatus]::Running)
                        {
                            break
                        }
                    }

                    if ($Counter -ge $Timeout)
                    {
                        throw "Timeout waiting for the SQL agent service to start"
                    }
                }
                else
                {
                    if (-not $DoNotStart)
                    {
                        Start-Service -Name $SqlAgent
                    }
                }
            }
        }
        else
        {
            Write-Warning -Message "SQL Agent service could not be found."
        }

        Write-Output -InputObject (Get-Service -Name $SqlAgent)
    }

    End {
    }
}

Function Get-SQLInstanceDefaultTraceFile {
    <#
        .SYNOPSIS
            Gets the most recent default trace file for the SQL Instance.
 
        .DESCRIPTION
            The Get-SQLInstanceDefaultTraceFile cmdlet gets the most recent default trace file path for the instance.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .EXAMPLE
            Get-SQLInstanceDefaultTraceFile -ComputerName "SQL2014" -InstanceName "MyInstance"
             
            Gets the default trace file path for the MyInstance SQL Instance.
 
        .INPUTS
            None
 
        .OUTPUTS
            System.String
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty
    )

    Begin {       
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        [Microsoft.SqlServer.Management.Smo.Database]$Master = $SqlConnection.Databases | Where-Object {$_.Name -eq "master"} | Select-Object -First 1
        $Data = $Master.ExecuteWithResults("SELECT TOP 1 path FROM sys.traces WHERE is_default = 1 ORDER BY last_event_time DESC")
        $TraceFilePath = $Data.Tables[0] | Select-Object -ExpandProperty Path

        Write-Output -InputObject $TraceFilePath
    }

    End {        
    }
}

Function Get-SQLInstanceAuditCommandText {
    <#
        .SYNOPSIS
            Gets the command text to be used with creating Agent jobs to audit modifications to Functions, Triggers, and Stored Procedures.
 
        .DESCRIPTION
            The Get-SQLInstanceAuditCommandText cmdlet creates the T-SQL commands run by a job to audit CREATE, ALTER, and DROP commands issued against Functions, Triggers, and Stored Procedures.
 
            The text reviews the default trace log reviewing specific event Ids for these actions. It collects the information and sends an HTML formatted email to the email addresses in the specified Operator name.
 
        .PARAMETER DaysDiff
            The number of days in the trace file to be reviewed. This defaults to 1.
 
        .PARAMETER JobName
            The name of the job that will be running the command. This is only used as information in the HTML body.
 
        .PARAMETER OperatorToEmail
            The name of the Operator whose email addresses will be used with sp_send_dbmail for notifications.
     
        .PARAMETER EmailProfile
            The database mail profile used to send emails.
 
        .PARAMETER EmailSubject
            The subject line of the email. The SQL Instance name will be prepended to the subject for easy identification.
 
        .PARAMETER ObjectToAudit
            Specifies which audit the command text is being generated for. The options are Function, StoredProcedure, and Trigger.
 
        .EXAMPLE
            Get-SQLInstanceAuditCommandText -DaysDiff 1 -JobName "Alter_Function_Audit" -OperatorToEmail "Database Administrators" -EmailProfile "MSSQLSERVER Profile" -EmailSubject " - *Alert* Function Modification" -ObjectToAudit Function
             
            Creates the command text to be run by a job to audit CREATE, ALTER, and DROP actions on functions in the SQL Instance.
 
        .INPUTS
            None
 
        .OUTPUTS
            System.String
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter()]
        [System.Int32]$DaysDiff = 1,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$JobName,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$OperatorToEmail,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$EmailProfile,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$EmailSubject,

        [Parameter(Mandatory=$true)]
        [ValidateSet("Function", "StoredProcedure", "Trigger")]
        [System.String]$ObjectToAudit
    )

    Begin {
    }

    Process {
        [System.String]$ObjectTypes = ""
        [System.String]$ObjectName = ""
        [System.String]$Events = "(46, 47, 164)" #ALTER, CREATE, DROP

        switch ($ObjectToAudit) {
            "Function" {
                $ObjectName = "FunctionName"
                $ObjectTypes = "(17985, 17993, 18000, 18002, 18004, 20038, 21318, 21321, 21574)"
            }
            "StoredProcedure" {
                $ObjectName = "StoredProcedureName"
                $ObjectTypes = "(8276, 16724, 21076)"
            }
            "Trigger" {
                $ObjectName = "TriggerName"
                $ObjectTypes = "(8272, 8280, 17232)"
            }
        }

        [System.String]$Command = @"
USE [master];
 
DECLARE @filename varchar(255) = (SELECT TOP 1 path FROM master.sys.traces WHERE is_default = 1 ORDER BY last_event_time DESC);
 
CREATE TABLE #temp
    (
    DatabaseName varchar(255),
    $ObjectName varchar(255),
    ObjectId varchar(255),
    StartTime datetime,
    NTUserName varchar(255),
    LoginName varchar(255),
    HostName varchar(255),
    ApplicationName varchar(255),
    DBUserName varchar(255),
    SPID int,
    EventClass int,
    EventName varchar(255),
    EndTime datetime,
    MethodName varchar(255),
    TransactionID int,
    FileName varchar(255),
    IsSystem tinyint,
    ObjectType int
    )
 
INSERT INTO #temp
SELECT DISTINCT
    gt.DatabaseName,
    gt.ObjectName AS $ObjectName,
    gt.ObjectID,
    gt.StartTime,
    gt.HostName,
    gt.NTUserName,
    gt.LoginName,
    gt.ApplicationName,
    gt.DBUserName,
    gt.SPID,
    gt.EventClass,
    te.name AS EventName,
    gt.EndTime,
    gt.MethodName,
    gt.TransactionID,
    gt.FileName,
    gt.IsSystem,
    gt.ObjectType
FROM [fn_trace_gettable](@filename, DEFAULT) gt
    JOIN sys.trace_events te
    ON gt.EventClass = te.trace_event_id
WHERE
    gt.EventClass in $Events AND
    gt.ObjectType in $ObjectTypes AND
    DATEDIFF(dd, gt.StartTime, GETDATE()) < '$DaysDiff'
ORDER BY gt.StartTime DESC;
 
IF (SELECT count(*) FROM #temp) > 1
BEGIN
DECLARE @Html varchar(max) = '';
DECLARE @HtmlHeaders varchar(max) = '';
DECLARE @HtmlRows varchar(max) = '';
 
--get header, columns name
SELECT @HtmlHeaders = @HtmlHeaders + '<th>' + name + '</th>' FROM tempdb.sys.columns WHERE object_id = object_id('tempdb.dbo.#temp');
 
--convert table to XML PATH, ELEMENTS XSINIL is used to include NULL values
SET @HtmlRows = (SELECT * FROM #temp FOR XML PATH('tr'), ELEMENTS XSINIL)
 
--convert the way ELEMENTS XSINIL display NULL to display word NULL
SET @HtmlRows = REPLACE(@HtmlRows, 'xsi:nil="true"/>', '>NULL</td>');
SET @HtmlRows = REPLACE(@HtmlRows, '<tr xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">', '<tr>')
 
--FOR XML PATH will set tags for each column name, <columnName1>abc</columnName1><columnName2>def</columnName2>
--this will replace all the column names with TD (html table data tag)
SELECT @HtmlRows = REPLACE(REPLACE(@HtmlRows, '<' + name + '>', '<td>'), '</' + name + '>', '</td>')
FROM tempdb.sys.columns WHERE object_id = object_id('tempdb.dbo.#temp')
 
SET @Html = '<!DOCTYPE html>
<html>
    <head>
        <meta name="viewport" content="width=device-width" />
        <title>$JobName</title>
    </head>
    <style>
        .table {
            width:100%;
            table-layout:fixed;
            border:1px solid black;
        }
         
        .table td {
            word-break:break-all;
            word-wrap:break-word;
            vertical-align:top;
            text-align:left;
        }
 
        .table th {
            text-align:center;
        }
    </style>
        <body style="width:1200px;margin-left:auto;margin-right:auto;">
            <h1 style="text-align:center;">$JobName</h1>
                <div>
                    <table class="table"><thead>'
                    + '<tr>' + @HtmlHeaders + '</tr>'
                    + '</thead><tbody>'
                    + @HtmlRows
                    + '</tbody></table></div></body></html>';
 
DECLARE @Recipients varchar(max) = (SELECT email_address FROM msdb.dbo.sysoperators WHERE name ='$OperatorToEmail');
DECLARE @Subject varchar(255) = @@ServiceName + '$EmailSubject';
 
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '$EmailProfile',
@body_format = 'HTML',
@body = @Html,
@recipients = @Recipients,
@subject = @Subject
 
END
 
DROP TABLE #temp;
"@

        Write-Output -InputObject $Command
    }

    End {      
    }
}

Function Get-SQLDatabaseDdlTriggerCommandText {
    <#
        .SYNOPSIS
            Gets the command text to be used with creating Database DDL triggers to audit modifications to Functions, Triggers, and Stored Procedures.
 
        .DESCRIPTION
            The Get-SQLDatabaseDdlTriggerCommandText cmdlet creates the T-SQL commands run by a trigger to audit CREATE, ALTER, and DROP commands issued against Functions, Triggers, and Stored Procedures.
 
            The gets the event notification of the action. It collects the information and sends an HTML formatted email to the email addresses in the specified Operator name.
 
        .PARAMETER Subject
            The subject line of the email. The SQL Instance name will be prepended to the subject for easy identification.
 
        .PARAMETER OperatorToEmail
            The name of the Operator whose email addresses will be used with sp_send_dbmail for notifications.
     
        .PARAMETER EmailProfile
            The database mail profile used to send emails.
 
        .EXAMPLE
            Get-SQLDatabaseDdlTriggerCommandText -OperatorToEmail "Database Administrators" -EmailProfile "MSSQLSERVER Profile" -Subject " $DBName - *Alert* SQL Modification"
             
            Creates the command text to be run by a trigger to audit CREATE, ALTER, and DROP actions on functions on a specific SQL database. The database name that the trigger is being created for is also included as part of the subject line.
 
        .INPUTS
            None
 
        .OUTPUTS
            System.String
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$OperatorToEmail,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$Subject,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [System.String]$EmailProfile
    )

    Begin {
        <#
            Event Data Object
 
            <EVENT_INSTANCE>
                <EventType>event </EventType>
 
                <PostTime>date-time</PostTime>
                <SPID>spid</SPID>
                <ServerName>name </ServerName>
 
                <LoginName>login </LoginName>
                <UserName>name</UserName>
                <DatabaseName>name</DatabaseName>
 
                <SchemaName>name</SchemaName>
                <ObjectName>name</ObjectName>
                <ObjectType>type</ObjectType>
 
                <TSQLCommand>command</TSQLCommand>
            </EVENT_INSTANCE>
        #>
    }

    Process {
        $Command = @"
SET NOCOUNT ON;
                 
DECLARE @EventData XML = EVENTDATA()
DECLARE @IpAddress varchar(32) = (SELECT client_net_address FROM master.sys.dm_exec_connections WHERE session_id = @@SPID);
         
CREATE TABLE #temp (
    EventDate datetime,
    EventType varchar(255),
    UserName varchar(255),
    EventDDL varchar(max),
    DatabaseName varchar(255),
    SchemaName varchar(255),
    ObjectName varchar(255),
    HostName varchar(255),
    IPAddress varchar(32),
    ProgramName varchar(255)
);
 
INSERT INTO #temp
SELECT
    @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
    @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(255)'),
    @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(255)'),
    @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
    @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(255)'),
    @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(255)'),
    @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(255)'),
    HOST_NAME(),
    @IpAddress,
    PROGRAM_NAME()
 
DECLARE @Html varchar(max) = '';
DECLARE @HtmlHeaders varchar(max) = '';
DECLARE @HtmlRows varchar(max) = '';
 
--get header, columns name
SELECT @HtmlHeaders = @HtmlHeaders + '<th>' + name + '</th>' FROM tempdb.sys.columns WHERE object_id = object_id('tempdb.dbo.#temp');
 
--convert table to XML PATH, ELEMENTS XSINIL is used to include NULL values
SET @HtmlRows = (SELECT * FROM #temp FOR XML PATH('tr'), ELEMENTS XSINIL)
 
--convert the way ELEMENTS XSINIL display NULL to display word NULL
SET @HtmlRows = REPLACE(@HtmlRows, 'xsi:nil="true"/>', '>NULL</td>');
SET @HtmlRows = REPLACE(@HtmlRows, '<tr xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">', '<tr>')
 
--FOR XML PATH will set tags for each column name, <columnName1>abc</columnName1><columnName2>def</columnName2>
--this will replace all the column names with TD (html table data tag)
SELECT @HtmlRows = REPLACE(REPLACE(@HtmlRows, '<' + name + '>', '<td>'), '</' + name + '>', '</td>')
FROM tempdb.sys.columns WHERE object_id = object_id('tempdb.dbo.#temp')
 
SET @Html = '<!DOCTYPE html>
                <html>
                    <head>
                        <meta name="viewport" content="width=device-width" />
                        <title>Modified Stored Procedure/Function/Trigger</title>
                    </head>
                    <style>
                        .table {
                            width:100%;
                            table-layout:fixed;
                            border:1px solid black;
                        }
         
                        .table td {
                            word-break:break-all;
                            word-wrap:break-word;
                            vertical-align:top;
                            text-align:left;
                        }
 
                        .table th {
                            text-align:center;
                        }
                    </style>
                        <body style="width:1200px;margin-left:auto;margin-right:auto;">
                            <h1 style="text-align:center;">Modified Stored Procedure/Function/Trigger</h1>
                                <div>
                                    <table class="table"><thead>'
                                    + '<tr>' + @HtmlHeaders + '</tr>'
                                    + '</thead><tbody>'
                                    + @HtmlRows
                                    + '</tbody></table></div></body></html>';
                 
DECLARE @Recipients varchar(max) = (SELECT email_address FROM msdb.dbo.sysoperators WHERE name = '$OperatorToEmail');
DECLARE @Subject varchar(255) = @@ServiceName + '$Subject'
 
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '$EmailProfile',
    @body_format = 'HTML',
    @body = @Html,
    @recipients = @Recipients,
    @subject = @Subject
                 
 
DROP TABLE #temp;
"@
    
        Write-Output -InputObject $Command
    }

    End {       
    }
}

Function New-SQLInstanceDatabaseDirectoryAccessRuleSet {
    <#
        .SYNOPSIS
            Creates the access rule set for the SQL Database Files and Folders.
 
        .DESCRIPTION
            The New-DatabaseDirectoryRuleSet cmdlet creates the access rules.
 
        .EXAMPLE
            New-DatabaseDirectoryRuleSet
 
            Creates the access rules.
 
        .INPUTS
            None
 
        .OUTPUTS
            System.Security.AccessControl.FileSystemAccessRule[]
 
        .NOTES
            AUTHOR: Michael Haken
            LASTEDIT: 2/23/2017
    #>

    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$true)]
        [ValidateNotNull()]
        [System.Security.Principal.SecurityIdentifier]$SqlServiceAccountSid,

        [Parameter(Mandatory=$true)]
        [ValidateNotNull()]
        [System.Security.Principal.SecurityIdentifier]$AdministratorsSid,

        [Parameter()]
        [System.Security.Principal.SecurityIdentifier]$SqlAgentSid = $null,

        [Parameter()]
        [switch]$IncludeLocalAdministrators
    )

    Begin {}

    Process
    {
        $SqlServiceAce = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule($SqlServiceAccountSid.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow
        )

        $AdministratorsAce1 = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule($AdministratorsSid.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow
        )

        $AdministratorsAce2 = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule($AdministratorsSid.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            [System.Security.AccessControl.InheritanceFlags]::None,
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow
        )

        [System.Security.AccessControl.FileSystemAccessRule[]] $Rules = @($AdministratorsAce1, $AdministratorsAce2, $SqlServiceAce)

        if ($SqlAgentSid -ne $null) {
            $SqlAgentAce = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule($SqlAgentSid.Translate([System.Security.Principal.NTAccount]),
                @([System.Security.AccessControl.FileSystemRights]::ReadAndExecute, [System.Security.AccessControl.FileSystemRights]::Write),
                ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
                [System.Security.AccessControl.PropagationFlags]::None,
                [System.Security.AccessControl.AccessControlType]::Allow
            )

            $Rules += $SqlAgentAce
        }

        if ($IncludeLocalAdministrators) {
            $BuiltinAdministrators = New-Object -TypeName System.Security.Principal.SecurityIdentifier([System.Security.Principal.WellKnownSidType]::BuiltinAdministratorsSid, $null)

            $LocalAdministratorsAce1 = New-Object System.Security.AccessControl.FileSystemAccessRule($BuiltinAdministrators.Translate([System.Security.Principal.NTAccount]),
                [System.Security.AccessControl.FileSystemRights]::FullControl,
                ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
                [System.Security.AccessControl.PropagationFlags]::InheritOnly,
                [System.Security.AccessControl.AccessControlType]::Allow)  

            $Rules += $LocalAdministratorsAce1  

            $LocalAdministratorsAce2 = New-Object System.Security.AccessControl.FileSystemAccessRule($BuiltinAdministrators.Translate([System.Security.Principal.NTAccount]),
                [System.Security.AccessControl.FileSystemRights]::FullControl,
                [System.Security.AccessControl.InheritanceFlags]::None,
                [System.Security.AccessControl.PropagationFlags]::None,
                [System.Security.AccessControl.AccessControlType]::Allow)

            $Rules += $LocalAdministratorsAce2 
        }

        Write-Output -InputObject $Rules
    }

    End{        
    }
}

Function New-SQLInstanceAuditLogAccessRuleSet {
    <#
        .SYNOPSIS
            Creates the access rule set for the SQL Audit Logs.
 
        .DESCRIPTION
            The New-AuditLogRuleSet cmdlet creates the access rules.
 
        .EXAMPLE
            New-AuditLogRuleSet
 
            Creates the access rules.
 
        .INPUTS
            None
 
        .OUTPUTS
            System.Security.AccessControl.FileSystemAccessRule[]
 
        .NOTES
            AUTHOR: Michael Haken
            LASTEDIT: 2/23/2017
    #>

    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$true)]
        [ValidateNotNull()]
        [System.Security.Principal.SecurityIdentifier]$SqlServiceAccountSid,

        [Parameter(Mandatory=$true)]
        [ValidateNotNull()]
        [System.Security.Principal.SecurityIdentifier]$AuditAdministratorSid,

        [Parameter(Mandatory=$true)]
        [ValidateNotNull()]
        [System.Security.Principal.SecurityIdentifier]$AuditorsSid,

        [Parameter()]
        [System.Security.Principal.SecurityIdentifier]$SqlAgentSid = $null

    )

    Begin {}

    Process
    {
        <#
            Administrator(read)
 
            Users (none)
 
            Audit Administrator(Full Control)
 
            Auditors group (Read)
 
            SQL Server Service SID OR Service Account (Full Control)
 
            SQL Server SQL Agent Service SID OR Service Account, if SQL Server Agent is in use. (Read, Execute, Write)
        #>

        $BuiltinAdministrators = New-Object -TypeName System.Security.Principal.SecurityIdentifier([System.Security.Principal.WellKnownSidType]::BuiltinAdministratorsSid, $null)

        $AdministratorsAce1 = New-Object System.Security.AccessControl.FileSystemAccessRule($BuiltinAdministrators.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::Read,
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $AdministratorsAce2 = New-Object System.Security.AccessControl.FileSystemAccessRule($BuiltinAdministrators.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::Read,
            [System.Security.AccessControl.InheritanceFlags]::None,
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $AuditAdminAce1 = New-Object System.Security.AccessControl.FileSystemAccessRule($AuditAdministratorSid.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow
        )

        $AuditAdminAce2 = New-Object System.Security.AccessControl.FileSystemAccessRule($AuditAdministratorSid.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            [System.Security.AccessControl.InheritanceFlags]::None,
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow
        )

        $AuditorsAce1 = New-Object System.Security.AccessControl.FileSystemAccessRule($AuditorsSid.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::Read,
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow
        )

        $AuditorsAce2 = New-Object System.Security.AccessControl.FileSystemAccessRule($AuditorsSid.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::Read,
            [System.Security.AccessControl.InheritanceFlags]::None,
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow
        )

        $SqlServiceAce = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule($SqlServiceAccountSid.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow
        )

        [System.Security.AccessControl.FileSystemAccessRule[]] $Rules = @($AdministratorsAce1, $AdministratorsAce2, $AuditAdminAce1, $AuditAdminAce2, $AuditorsAce1, $AuditorsAce2, $SqlServiceAce)

        if ($SqlAgentSid -ne $null) {
            $SqlAgentAce = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule($SqlAgentSid.Translate([System.Security.Principal.NTAccount]),
                @([System.Security.AccessControl.FileSystemRights]::ReadAndExecute, [System.Security.AccessControl.FileSystemRights]::Write),
                ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
                [System.Security.AccessControl.PropagationFlags]::None,
                [System.Security.AccessControl.AccessControlType]::Allow
            )

            $Rules += $SqlAgentAce
        }

        Write-Output -InputObject $Rules
    }

    End{        
    }
}

Function New-SQLInstanceInstallationDirectoryAccessRuleSet {
    <#
        .SYNOPSIS
            Creates the access rule set for the SQL installation directory.
 
        .DESCRIPTION
            The New-InstallationDirectoryRuleSet cmdlet creates the access rules.
 
        .EXAMPLE
            New-InstallationDirectoryRuleSet
 
            Creates the access rules.
 
        .INPUTS
            None
 
        .OUTPUTS
            System.DirectoryServices.FileSystemAccessRule[]
 
        .NOTES
            AUTHOR: Michael Haken
            LASTEDIT: 2/23/2017
    #>

    [CmdletBinding()]
    Param()

    Begin {

    }

    Process {
        [System.Security.AccessControl.FileSystemAccessRule[]]$Rules = @()

        [System.Security.Principal.SecurityIdentifier]$BuiltinAdministrators = New-Object -TypeName System.Security.Principal.SecurityIdentifier([System.Security.Principal.WellKnownSidType]::BuiltinAdministratorsSid, $null)

        [System.Security.AccessControl.FileSystemAccessRule]$AdministratorsAce1 = New-Object System.Security.AccessControl.FileSystemAccessRule($BuiltinAdministrators.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        [System.Security.AccessControl.FileSystemAccessRule]$AdministratorsAce2 = New-Object System.Security.AccessControl.FileSystemAccessRule($BuiltinAdministrators.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            [System.Security.AccessControl.InheritanceFlags]::None,
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $TrustedInstallerObj = New-Object -TypeName System.Security.Principal.NTAccount("NT Service", "TrustedInstaller")
        $TrustedInstaller = ($TrustedInstallerObj.Translate([System.Security.Principal.SecurityIdentifier]))

        $TrustedInstallerAce1 = New-Object System.Security.AccessControl.FileSystemAccessRule($TrustedInstaller.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            [System.Security.AccessControl.InheritanceFlags]::ContainerInherit,
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $TrustedInstallerAce2 = New-Object System.Security.AccessControl.FileSystemAccessRule($TrustedInstaller.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            [System.Security.AccessControl.InheritanceFlags]::None,
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $System = New-Object -TypeName System.Security.Principal.SecurityIdentifier([System.Security.Principal.WellKnownSidType]::LocalSystemSid, $null)

        $SystemAce1 = New-Object System.Security.AccessControl.FileSystemAccessRule($System.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $SystemAce2 = New-Object System.Security.AccessControl.FileSystemAccessRule($System.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::FullControl,
            [System.Security.AccessControl.InheritanceFlags]::None,
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $Users = New-Object -TypeName System.Security.Principal.SecurityIdentifier([System.Security.Principal.WellKnownSidType]::BuiltinUsersSid, $null)

        $UsersAce1 = New-Object System.Security.AccessControl.FileSystemAccessRule($Users.Translate([System.Security.Principal.NTAccount]),
            @([System.Security.AccessControl.FileSystemRights]::Synchronize, [System.Security.AccessControl.FileSystemRights]::ReadAndExecute),
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $UsersAce2 = New-Object System.Security.AccessControl.FileSystemAccessRule($Users.Translate([System.Security.Principal.NTAccount]),
            @([System.Security.AccessControl.FileSystemRights]::Synchronize, [System.Security.AccessControl.FileSystemRights]::ReadAndExecute),
            [System.Security.AccessControl.InheritanceFlags]::None,
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $CreatorOwner = New-Object -TypeName System.Security.Principal.SecurityIdentifier([System.Security.Principal.WellKnownSidType]::CreatorOwnerSid, $null)

        $CreatorOwnerAce = New-Object System.Security.AccessControl.FileSystemAccessRule($CreatorOwner.Translate([System.Security.Principal.NTAccount]),
            @([System.Security.AccessControl.FileSystemRights]::FullControl),
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $AllAppPackages = New-Object -TypeName System.Security.Principal.SecurityIdentifier("S-1-15-2-1")

        $AllAppPackagesAce1 = New-Object System.Security.AccessControl.FileSystemAccessRule($AllAppPackages,
            @([System.Security.AccessControl.FileSystemRights]::ReadAndExecute, [System.Security.AccessControl.FileSystemRights]::Synchronize),
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $AllAppPackagesAce2 = New-Object System.Security.AccessControl.FileSystemAccessRule($AllAppPackages,
            @([System.Security.AccessControl.FileSystemRights]::ReadAndExecute, [System.Security.AccessControl.FileSystemRights]::Synchronize),
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $AllResrictedAppPackages = New-Object -TypeName System.Security.Principal.SecurityIdentifier("S-1-15-2-2")

        $AllRestrictedAppPackagesAce1 = New-Object System.Security.AccessControl.FileSystemAccessRule($AllResrictedAppPackages,
            @([System.Security.AccessControl.FileSystemRights]::ReadAndExecute, [System.Security.AccessControl.FileSystemRights]::Synchronize),
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::InheritOnly,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        $AllRestricedAppPackagesAce2 = New-Object System.Security.AccessControl.FileSystemAccessRule($AllResrictedAppPackages,
            @([System.Security.AccessControl.FileSystemRights]::ReadAndExecute, [System.Security.AccessControl.FileSystemRights]::Synchronize),
            ([System.Security.AccessControl.InheritanceFlags]::ObjectInherit -bor [System.Security.AccessControl.InheritanceFlags]::ContainerInherit),
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow       
        )

        [System.Security.AccessControl.FileSystemAccessRule[]]$Rules = @($AdministratorsAce1, `
            $AdministratorsAce2, `
            $TrustedInstallerAce1, `
            $TrustedInstallerAce1, `
            $SystemAce1, `
            $SystemAce2, `
            $UsersAce1, `
            $UsersAce2, `
            $CreatorOwnerAce, `
            $AllAppPackagesAce1, `
            $AllAppPackagesAce2, `
            $AllRestrictedAppPackagesAce1, `
            $AllRestrictedAppPackagesAce1)

        Write-Output -InputObject $Rules
    }

    End {        
    }
}

Function New-SQLInstanceInstallationDirectoryAuditRuleSet {
    <#
        .SYNOPSIS
            Creates the audit rule set for the SQL installation directory.
 
        .DESCRIPTION
            The New-InstallationDirectoryAuditRuleSet cmdlet builds the required audit rule for auditing the SQL server installation directory.
 
        .EXAMPLE
            New-InstallationDirectoryAuditRuleSet
 
            Creates the audit rules.
 
        .INPUTS
            None
 
        .OUTPUTS
            System.Security.AccessControl.FileSystemAuditRule[]
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATED: 2/23/2017
    #>

    [CmdletBinding()]
    Param()

    Begin{        
    }
    
    Process
    {
        $Everyone = New-Object System.Security.Principal.SecurityIdentifier([System.Security.Principal.WellKnownSidType]::WorldSid, $null)

        $EveryoneFail = New-Object System.Security.AccessControl.FileSystemAuditRule($Everyone.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::Modify, 
            ([System.Security.AccessControl.InheritanceFlags]::ContainerInherit -bor [System.Security.AccessControl.InheritanceFlags]::ObjectInherit),
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AuditFlags]::Failure)

        $EveryoneSuccess = New-Object System.Security.AccessControl.FileSystemAuditRule($Everyone.Translate([System.Security.Principal.NTAccount]),
            [System.Security.AccessControl.FileSystemRights]::Modify, 
            ([System.Security.AccessControl.InheritanceFlags]::ContainerInherit -bor [System.Security.AccessControl.InheritanceFlags]::ObjectInherit),
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AuditFlags]::Success)

        [System.Security.AccessControl.FileSystemAuditRule[]]$Rules = @($EveryoneFail, $EveryoneSuccess)

        Write-Output -InputObject $Rules
    }

    End{        
    }
}

Function Get-SQLAuditObjectTypes {
    [CmdletBinding()]
    Param()

    Begin {
    }

    Process {
        Write-Output -InputObject $script:AuditObjectTypes
    }

    End {        
    }
}

Function Import-SqlModule {
    <#
        .SYNOPSIS
            Imports the SQLPS module and snapins.
 
        .DESCRIPTION
            The cmdlet checks for the SQLPS, Microsoft.SqlServer.Management.PSSnapins, and Microsoft.SqlServer.Management.PSProvider modules and loads them. It optionally adds the SQL Server Management Objects .NET assembly.
 
            At the end of the cmdlet, the PSDrive is set to SQLSERVER:\.
 
        .PARAMETER Version
            The version of SQL being configured, select from either 10 (SQL 2005), 11 (SQL 2008), 12 (SQL 2014), or 13 (SQL 2016). This defaults to 12. It is used to locate the correct modules in the filesystem.
 
        .INPUTS
            System.Int
 
        .OUTPUTS
            None
 
        .EXAMPLE
            Import-SqlModule
 
            Imports the SQL modules.
 
        .EXAMPLE
            Import-SqlModule -LoadSMO
 
            Imports the SQL modules and loads the SMO .NET assembly.
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(Position=0,ValueFromPipeline=$true)]
        [ValidateSet(10,11,12,13)]
        [int]$Version,

        [Parameter(Position=1)]
        [switch]$LoadSMO
    )

    Begin {
    }

    Process {
        Write-Verbose -Message "Loading SQLPS PowerShell Modules."

        $BasePath = [System.String]::Empty

        if (-not $PSBoundParameters.ContainsKey("Version"))
        {
            Write-Verbose -Message "Calculating path of SQL PS files"
            $Paths = Get-ChildItem -Path "${env:ProgramFiles(x86)}\Microsoft SQL Server" | Where-Object {$_.Name -match "[0-9]{2,3}"} | Sort-Object -Property @{Expression = {[System.Int32]($_.Name)}; Descending = $true } | Select -ExpandProperty FullName
            foreach ($Path in $Paths)
            {
                $Temp = "$Path\Tools\PowerShell\Modules\SQLPS"

                if (Test-Path -Path $Temp)
                {
                    $BasePath = $Temp
                    Write-Verbose -Message "Found path at $Temp"
                    break
                }
            }

            if ([System.String]::IsNullOrEmpty($BasePath))
            {
                throw "Could not locate the SQL PowerShell directory, please specify a version specifically."
            }
        }
        else
        {
            $BasePath = "${env:ProgramFiles(x86)}\Microsoft SQL Server\$($Version)0\Tools\PowerShell\Modules\SQLPS"
            Write-Verbose -Message "Using default base path from provided version, $BasePath"
        }


        if (!(Get-Module -Name SQLPS)) {

            Import-Module -Name "$BasePath\SQLPS.PS1" -ErrorAction Stop -DisableNameChecking
            Write-Verbose -Message "Successfully loaded SQLPS Module."
            Write-Debug -Message "Successfully loaded SQLPS Module."
        }
        else {
            Write-Debug "SQLPS module already loaded"
        }
        
        if (!(Get-Module -Name Microsoft.SqlServer.Management.PSSnapins)) {                                                                                                                                                     
            Import-Module -Name "$BasePath\Microsoft.SqlServer.Management.PSSnapins.dll" -ErrorAction Stop -DisableNameChecking
            Write-Verbose -Message "Successfully added SQL PS Snapin module."
            Write-Debug -Message "Successfully added SQL PS Snapin module."
        }
        else {
            Write-Debug -Message "Microsoft.SqlServer.Management.PSSnapins module already loaded."
        }

        if(!(Get-Module -Name Microsoft.SqlServer.Management.PSProvider)) {
            Import-Module -Name "$BasePath\Microsoft.SqlServer.Management.PSProvider.dll" -ErrorAction Stop -DisableNameChecking
            Write-Verbose -Message "Successfully added SQL PS Provider module."
            Write-Debug -Message "Successfully added SQL PS Provider module."
        }
        else {
            Write-Debug -Message "Microsoft.SqlServer.Management.PSProvider module already loaded."
        }

        if ($LoadSMO) {
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
            [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfoExtended") | Out-Null
            [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
            [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
            [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
            [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
            Write-Debug -Message 'SQL Server Management Objects .NET assembly successfully loaded'
        }

        Push-Location -Path "SQLSERVER:"
    }

    End {      
    }
}

Function Test-SQLLoginIsSysAdmin {
    <#
        .SYNOPSIS
            Tests if the specified login has sysadmin privileges.
 
        .DESCRIPTION
            The Test-SQLLoginIsSysAdmin cmdlet uses the IS_SRVROLEMEMBER sp to evaluate the permissions.
 
        .PARAMETER ComputerName
            The server running the SQL instance to be configured or the virtual resource name of a clustered SQL instance. This defaults to localhost.
 
        .PARAMETER InstanceName
            The name of the instance to be configured, this defaults to the default instance name of MSSQLSERVER.
     
        .PARAMETER SqlServer
            An existing connection to a SQL Instance using a Microsoft.SqlServer.Management.Smo.Server object.
 
        .PARAMETER Credential
            The credentials to use to connect to the SQL host node.
 
        .PARAMETER UserName
            The username of the login to test for sysadmin privileges.
 
        .EXAMPLE
            Test-SQLLoginIsSysAdmin -Computer "SQL2014" -Instance "MyInstance" -UserName "Administrator"
 
            Tests to see if the builtin Administrator account has sysadmin privileges.
        .INPUTS
            None
 
        .OUTPUTS
            None
 
        .NOTES
            AUTHOR: Michael Haken
            LAST UPDATE: 2/23/2017
    #>
    [CmdletBinding()]
    Param(
        [Parameter(ParameterSetName="NewConnection")]
        [System.String]$ComputerName = "localhost",

        [Parameter(ParameterSetName="NewConnection")]
        [ValidateNotNullOrEmpty()]
        [System.String]$InstanceName = "MSSQLSERVER",

        [Parameter(ParameterSetName="ExistingConnection",Mandatory=$true)]
        [ValidateNotNull()]
        [Microsoft.SqlServer.Management.Smo.Server]$SqlServer,

        [Parameter(ParameterSetName = "NewConnection")] 
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]    
        $Credential = [System.Management.Automation.PSCredential]::Empty,

        [Parameter(Mandatory = $true)]
        [System.String]$UserName
    )

    Begin {
    }

    Process {
        if ($PSCmdlet.ParameterSetName -eq $EXISTING_CONNECTION) {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = $SqlServer
        }
        else {
            [Microsoft.SqlServer.Management.Smo.Server]$SqlConnection = Get-SQLServer -ComputerName $ComputerName -InstanceName $InstanceName -Credential $Credential
        }

        [System.String]$Login = Get-AccountTranslatedNTName -UserName $UserName -ComputerName $SqlConnection.NetName -Credential $Credential
        [Microsoft.SqlServer.Management.Smo.ServerRole]$SysAdminFixedRole = $SqlConnection.Roles | Where-Object {$_.IsFixedRole -eq $true -and $_.ID -eq 0x03} | Select-Object -First 1

        $Sql = "SELECT IS_SRVROLEMEMBER('$($SysAdminFixedRole.Name)', '$Login') as IsSysAdmin"

        $Result = $SqlConnection.Databases["master"].ExecuteWithResults($Sql).Tables[0] | Select-Object -ExpandProperty IsSysAdmin

        Write-Output -InputObject ($Result -eq 1)   
    }

    End {
    }
}

#endregion

$script:AuditObjectTypes = @(
    [PSCustomObject]@{Id = 8259; Name = "Check Constraint"},
    [PSCustomObject]@{Id = 8260; Name = "Default (constraint or standalone)"},
    [PSCustomObject]@{Id = 8262; Name = "Foreign-key Constraint" },
    [PSCustomObject]@{Id = 8272; Name = "Stored Procedure" },
    [PSCustomObject]@{Id = 8274; Name = "Rule" },
    [PSCustomObject]@{Id = 8275; Name = "System Table"},
    [PSCustomObject]@{Id = 8276; Name = "Trigger on Server"},
    [PSCustomObject]@{Id = 8277; Name = "(User-defined) Table"},
    [PSCustomObject]@{Id = 8278; Name = "View"},
    [PSCustomObject]@{Id = 8280; Name = "Extended Stored Procedure"},
    [PSCustomObject]@{Id = 16724; Name = "CLR Trigger"},
    [PSCustomObject]@{Id = 16964; Name = "Database"},
    [PSCustomObject]@{Id = 16975; Name = "Object"},
    [PSCustomObject]@{Id = 17222; Name = "FullText Catalog"},
    [PSCustomObject]@{Id = 17232; Name = "CLR Stored Procedure"},
    [PSCustomObject]@{Id = 17235; Name = "Schema"},
    [PSCustomObject]@{Id = 17475; Name = "Credential"},
    [PSCustomObject]@{Id = 17491; Name = "DDL Event"},
    [PSCustomObject]@{Id = 17741; Name = "Management Event"},
    [PSCustomObject]@{Id = 17747; Name = "Security Event"},
    [PSCustomObject]@{Id = 17749; Name = "User Event"},
    [PSCustomObject]@{Id = 17985; Name= "CLR Aggregate Function"},
    [PSCustomObject]@{Id = 17993; Name = "Inline Table-valued SQL Function"},
    [PSCustomObject]@{Id = 18000; Name = "Partition Function"},
    [PSCustomObject]@{Id = 18002; Name = "Replication Filter Procedure"},
    [PSCustomObject]@{Id = 18004; Name = "Table-valued SQL Function"},
    [PSCustomObject]@{Id = 18259; Name = "Server Role"},
    [PSCustomObject]@{Id = 18263; Name = "Microsoft Windows Group"},
    [PSCustomObject]@{Id = 19265; Name = "Asymmetric Key"},
    [PSCustomObject]@{Id = 19277; Name = "Master Key"},
    [PSCustomObject]@{Id = 19280; Name = "Primary Key"},
    [PSCustomObject]@{Id = 19283; Name = "ObfusKey"},
    [PSCustomObject]@{Id = 19521; Name = "Asymmetric Key Login"},
    [PSCustomObject]@{Id = 19523; Name = "Certificate Login"},
    [PSCustomObject]@{Id = 19538; Name = "Role"},
    [PSCustomObject]@{Id = 19539; Name = "SQL Login"},
    [PSCustomObject]@{Id = 19543; Name = "Windows Login"},
    [PSCustomObject]@{Id = 20034; Name = "Remote Service Binding"},
    [PSCustomObject]@{Id = 20036; Name = "Event Notification on Database"},
    [PSCustomObject]@{Id = 20037; Name = "Event Notification"},
    [PSCustomObject]@{Id = 20038; Name = "Scalar SQL Function"},
    [PSCustomObject]@{Id = 20047; Name = "Event Notification on Object"},
    [PSCustomObject]@{Id = 20051; Name = "Synonym"},
    [PSCustomObject]@{Id = 20307; Name = "Sequence"},
    [PSCustomObject]@{Id = 20549; Name = "End Point"},
    [PSCustomObject]@{Id = 20801; Name = "Adhoc Queries which may be cached"},
    [PSCustomObject]@{Id = 20816; Name = "Prepared Queries which may be cached"},
    [PSCustomObject]@{Id = 20819; Name = "Service Broker Service Queue"},
    [PSCustomObject]@{Id = 20821; Name = "Unique Constraint"},
    [PSCustomObject]@{Id = 21057; Name = "Application Role"},
    [PSCustomObject]@{Id = 21059; Name = "Certificate"},
    [PSCustomObject]@{Id = 21075; Name = "Server"},
    [PSCustomObject]@{Id = 21076; Name = "Transact-SQL Trigger"},
    [PSCustomObject]@{Id = 21313; Name = "Assembly"},
    [PSCustomObject]@{Id = 21318; Name = "CLR Scalar Function"},
    [PSCustomObject]@{Id = 21321; Name = "Inline scalar SQL Function"},
    [PSCustomObject]@{Id = 21328; Name = "Partition Scheme"},
    [PSCustomObject]@{Id = 21333; Name = "User"},
    [PSCustomObject]@{Id = 21571; Name = "Service Broker Service Contract"},
    [PSCustomObject]@{Id = 21572; Name = "Trigger on Database"},
    [PSCustomObject]@{Id = 21574; Name = "CLR Table-valued Function"},
    [PSCustomObject]@{Id = 21577; Name = "Internal Table (For example, XML Node Table, Queue Table.)"},
    [PSCustomObject]@{Id = 21581; Name = "Service Broker Message Type"},
    [PSCustomObject]@{Id = 21586; Name = "Service Broker Route"},
    [PSCustomObject]@{Id = 21587; Name = "Statistics"}
    [PSCustomObject]@{Id = 21825; Name = "User"},
    [PSCustomObject]@{Id = 21827; Name = "User"},
    [PSCustomObject]@{Id = 21831; Name = "User"},
    [PSCustomObject]@{Id = 21843; Name = "User"},
    [PSCustomObject]@{Id = 21847; Name = "User"},
    [PSCustomObject]@{Id = 22099; Name = "Service Broker Service"},
    [PSCustomObject]@{Id = 22601; Name = "Index"},
    [PSCustomObject]@{Id = 22604; Name = "Certificate Login"},
    [PSCustomObject]@{Id = 22611; Name = "XMLSchema"},
    [PSCustomObject]@{Id = 22868; Name = "Type"}
)