cSQLConfig

1.4

DSCResources/TAMZ_cSQLCheckSA/TAMZ_cSQLCheckSA.psm1

function Get-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [parameter(Mandatory = $true)]
        [System.Boolean]
        $IncludeDisabled,

        [parameter(Mandatory = $true)]
        [System.String[]]
        $AccountstoPass
    )

    $server ="."
    $DatabaseName = "master"
    $Query = "SELECT Count(1) as Count
            FROM sys.server_principals p
                    JOIN sys.syslogins s ON p.sid = s.sid
            WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
                    -- Logins that are not process logins
                    AND p.name NOT IN ($AccountstoPass)
                    AND p.name not like ('NT SERVICE%')
                    AND p.name <> ('sa')
                    AND s.sysadmin = 1"
    
    if ($IncludeDisabled -eq $false)
    {    
        $Query = $Query +" AND is_disabled <> 1"
    }
    #Timeout parameters
    $QueryTimeout = 120
    $ConnectionTimeout = 30

    #Action of connecting to the Database and executing the query and returning results if there were any.
    $conn=New-Object -TypeName System.Data.SqlClient.SQLConnection
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $server,$DatabaseName,$ConnectionTimeout
    $conn.ConnectionString=$ConnectionString
    $conn.Open()
    $cmd=New-Object -TypeName system.Data.SqlClient.SqlCommand -Argumentlist $Query,$conn
    $cmd.CommandTimeout=$QueryTimeout

    $sqlReader = $cmd.ExecuteReader()
    while ($sqlReader.Read()) 
    { 
        $Count = $sqlReader["count"] 
    }
    $conn.Close()
    If ($Count -ge 1)
    {
        $Ensure = $false
    }
    else 
    {
        $Ensure = $true
    }

    $returnValue = @{
        IncludeDisabled = $IncludeDisabled
        AccountstoPass = $AccountstoPass
        Ensure = $Ensure
    }

    $returnValue
    
}


function Set-TargetResource
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory = $true)]
        [System.Boolean]
        $IncludeDisabled,

        [parameter(Mandatory = $true)]
        [System.String[]]
        $AccountstoPass,

        [ValidateSet("Present","Absent")]
        [System.String]
        $Ensure
    )
    $server ="."
    $DatabaseName = "master"
    $Query = "Set Nocount on;
            SELECT 'ALTER SERVER ROLE [sysadmin] DROP MEMBER ['+s.name +'];'
            FROM sys.server_principals p
                    JOIN sys.syslogins s ON p.sid = s.sid
            WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
                    -- Logins that are not process logins
                    AND p.name NOT IN ($AccountstoPass)
                    AND p.name not like ('NT SERVICE%')
                    AND p.name not like ('sa')
                    AND s.sysadmin = 1"
    if ($IncludeDisabled -eq $false)
    {    
        $Query = $Query +" AND is_disabled <> 1"
    }

    $cn = New-object -TypeName system.data.SqlClient.SqlConnection -ArgumentList "Data Source=$server;Integrated Security=SSPI;Initial Catalog=$DatabaseName ;Connect Timeout = 5"
    $cn.Open()
    $sqlcmd = New-Object -TypeName System.Data.SqlClient.SqlCommand
    $sqlcmd.CommandText = $Query
    $sqlcmd.Connection = $cn
    
    $sqlAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter
    $sqlAdapter.selectCommand = $sqlcmd
    $DataSet = New-Object -TypeName System.Data.DataSet
    $sqlAdapter.Fill($DataSet)
    
    $updatequery = $null
    for($i=0;$i -lt $DataSet.Tables.Count;$i++) {
        $updatequery = $updatequery + $DataSet.Tables[$i].Rows.column1
    }   

    If ($updatequery)
    {
        $sqlcmd.CommandText =$updatequery
        $sqlcmd.Connection =$cn

        $result = $sqlcmd.ExecuteNonQuery();
        Write-Verbose -Message "Users not needing SA have been removed."
    }
    $cn.Close()  

}



function Test-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [parameter(Mandatory = $true)]
        [System.Boolean]
        $IncludeDisabled,

        [parameter(Mandatory = $true)]
        [System.String[]]
        $AccountstoPass,

        [ValidateSet("Present","Absent")]
        [System.String]
        $Ensure
    )

    $server ="."
    $DatabaseName = "master"
    $Query = "SELECT Count(1) as Count
            FROM sys.server_principals p
                    JOIN sys.syslogins s ON p.sid = s.sid
            WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
                    -- Logins that are not process logins
                    AND p.name NOT IN ($AccountstoPass)
                    AND p.name not like ('NT SERVICE%')
                    AND p.name <> ('sa')
                    AND s.sysadmin = 1"
    if ($IncludeDisabled -eq $false)
    {    
        $Query = $Query +" AND is_disabled <> 1"
    }

    #Timeout parameters
    $QueryTimeout = 120
    $ConnectionTimeout = 30

    #Action of connecting to the Database and executing the query and returning results if there were any.
    $conn=New-Object -TypeName System.Data.SqlClient.SQLConnection
    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $server ,$DatabaseName,$ConnectionTimeout
    $conn.ConnectionString=$ConnectionString
    $conn.Open()
    $cmd=New-Object -TypeName system.Data.SqlClient.SqlCommand -ArgumentList $Query,$conn
    $cmd.CommandTimeout=$QueryTimeout
    $sqlReader = $cmd.ExecuteReader()
    while ($sqlReader.Read()) 
    { 
        $Count = $sqlReader["count"] 
    }
    $conn.Close()

    If ($Count -ge 1)
    {
        Write-Verbose -Message "SA's exist on the box Test Fails."
        return $false
    }
    else 
    {
        return $true
    }
}



Export-ModuleMember -Function *-TargetResource