DSCResources/MSFT_xDatabaseLogin/MSFT_xDatabaseLogin (withDefaultDB).psm1
|
data LocalizedData { # culture="en-US" ConvertFrom-StringData @' CreateDatabaseLoginError=Failed to create SQL Login '{0}'. TestDatabaseLoginError=Failed to test SQL Login '{0}'. CreateDatabaseLoginSuccess=Success: SQL Login '{0}' either already existed or has been successfully created. AlterDefaultDatabaseLoginSuccess=Successfully set the default database for '{0}' to '{1}'. RemoveDatabaseLoginError=Failed to remove SQL Login '{0}'. RemoveDatabaseLoginSuccess=Success: SQL Login '{0}' either does not existed or has been successfully removed. '@ } Import-Module $PSScriptRoot\..\xDatabase_Common function Get-TargetResource #Not yet working { [CmdletBinding()] [OutputType([System.Collections.Hashtable])] param ( [parameter(Mandatory = $true)] [System.String] $LoginName, [parameter(Mandatory = $true)] [System.Management.Automation.PSCredential] $SqlConnectionCredentials ) $Connection = Construct-SqlConnection -credentials $SqlConnectionCredentials [string]$loginNameQuery = "SELECT * from sys.sql_logins where name='$LoginName'" [string]$DBNameQuery = "Select default_database_name from sys.sql_logins where name='$LoginName'" $PresentValue = $false if((ReturnSqlQuery -sqlConnection $connection -SqlQuery $loginNameQuery)[0] -gt 0) { $PresentValue = $true } $returnValue = @{ Ensure = $PresentValue LoginName = $LoginName DefaultDatabaseName = (ReturnSqlQuery -sqlConnection $connection -SqlQuery $DBNameQuery).default_database_name } $returnValue } #TODO: handle absent case. example "DROP Login Toothy" function Set-TargetResource { [CmdletBinding()] param ( [ValidateSet("Present","Absent")] [System.String] $Ensure = "Present", [parameter(Mandatory = $true)] [System.String] $LoginName, [System.String] $LoginPassword, [System.String] $DefaultDatabaseName, [parameter(Mandatory = $true)] [System.Management.Automation.PSCredential] $SqlConnectionCredentials ) $Connection = Construct-SqlConnection -Credentials $SqlConnectionCredentials if($Ensure -eq "Present") { try { # Create login if it does not already exist. [string]$SqlQuery = "if not exists(SELECT name FROM sys.sql_logins WHERE name='$LoginName') Begin create login $LoginName with password='$LoginPassword' END" $supressReturn = ExecuteSqlQuery -sqlConnection $connection -SqlQuery $SqlQuery Write-Verbose $($LocalizedData.CreateDatabaseLoginSuccess -f ${LoginName}) # Set the default database if it is not already set correctly. if($PSBoundParameters.ContainsKey("DefaultDatabaseName")) { [string]$SqlQuery = "if not exists(SELECT name FROM sys.sql_logins WHERE name='$LoginName' AND default_database_name='$DefaultDatabaseName') BEGIN ALTER LOGIN $LoginName with default_database=$DefaultDatabaseName End" $supressReturn = ExecuteSqlQuery -sqlConnection $connection -SqlQuery $SqlQuery Write-Verbose $($LocalizedData.AlterDefaultDatabaseLoginSuccess -f ${LoginName}, ${DefaultDatabaseName}) } } catch { $errorId = "CreateDatabaseLogin"; $errorCategory = [System.Management.Automation.ErrorCategory]::InvalidResult $errorMessage = $($LocalizedData.CreateDatabaseLoginError -f ${LoginName}) $exception = New-Object System.InvalidOperationException $errorMessage $errorRecord = New-Object System.Management.Automation.ErrorRecord $exception, $errorId, $errorCategory, $null $PSCmdlet.ThrowTerminatingError($errorRecord); } } else # Ensure is absent so remove login. { try { # Create login if it does not already exist. [string]$SqlQuery = "if exists(SELECT name FROM sys.sql_logins WHERE name='$LoginName') Begin DROP LOGIN $LoginName END" $supressReturn = ExecuteSqlQuery -sqlConnection $connection -SqlQuery $SqlQuery Write-Verbose $($LocalizedData.RemoveDatabaseLoginSuccess -f ${LoginName}) } catch { $errorId = "RemoveDatabaseLogin"; $errorCategory = [System.Management.Automation.ErrorCategory]::InvalidResult $errorMessage = $($LocalizedData.RemoveDatabaseLoginError -f ${LoginName}) $exception = New-Object System.InvalidOperationException $errorMessage $errorRecord = New-Object System.Management.Automation.ErrorRecord $exception, $errorId, $errorCategory, $null $PSCmdlet.ThrowTerminatingError($errorRecord); } } } function Test-TargetResource #Not yet working { [CmdletBinding()] [OutputType([System.Boolean])] param ( [ValidateSet("Present","Absent")] [System.String] $Ensure = "Present", [parameter(Mandatory = $true)] [System.String] $LoginName, [System.String] $LoginPassword, [System.String] $DefaultDatabaseName, [parameter(Mandatory = $true)] [System.Management.Automation.PSCredential] $SqlConnectionCredentials ) try { $Connection = Construct-SqlConnection -Credentials $SqlConnectionCredentials [string]$SqlLoginQuery = "SELECT * from sys.sql_logins where name='$LoginName'" if($PSBoundParameters.ContainsKey("DefaultDatabaseName")) { $SqlDBQuery = " AND default_database_name='$DefaultDatabaseName'" } [string]$SqlQuery = "$SqlLoginQuery$SqlDBQuery" $LoginsReturnedByQuery = (ReturnSqlQuery -sqlConnection $connection -SqlQuery $SqlQuery)[0] if((($LoginsReturnedByQuery -gt 0) -and ($Ensure -eq "Present")) -or (($LoginsReturnedByQuery -eq 0) -and ($Ensure -eq "absent"))) { $result = $true } else { $result = $false } return $result } catch { $errorId = "TestDatabaseLogin"; $errorCategory = [System.Management.Automation.ErrorCategory]::InvalidResult $errorMessage = $($LocalizedData.TestDatabaseLoginError -f ${LoginName}) $exception = New-Object System.InvalidOperationException $errorMessage $errorRecord = New-Object System.Management.Automation.ErrorRecord $exception, $errorId, $errorCategory, $null $PSCmdlet.ThrowTerminatingError($errorRecord); } } Export-ModuleMember -Function *-TargetResource |