Core/SQL-Module.psm1
|
$ErrorActionPreference = "Stop" Get-Module "SqlServer" | Remove-Module -Force Import-Module "SqlServer" LoadLatestPSModule -Name "SqlServer" -Repository "PSGallery" # Private functions function DatabaseExists { [CmdletBinding()] Param ( [string]$SqlServer, [string]$DatabaseName, [string]$Username, [string]$Password ) $exists = $false # Load the SMO assembly and create a Server object [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SqlServer # Set credentials $server.ConnectionContext.LoginSecure = $false $server.ConnectionContext.set_Login($Username) $securedPassword = ConvertTo-SecureString $Password -AsPlainText -Force $server.ConnectionContext.set_SecurePassword($securedPassword) foreach ($db in $server.databases) { if ($db.name -eq $DatabaseName) { $exists = $true } } return $exists } function SqlLoginExists { [CmdletBinding()] Param ( [string]$SqlServer, [string]$SqlLogin, [string]$Username, [string]$Password ) # Load the SMO assembly and create a Server object [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SqlServer # Set credentials $server.ConnectionContext.LoginSecure = $false $server.ConnectionContext.set_Login($Username) $securedPassword = ConvertTo-SecureString $Password -AsPlainText -Force $server.ConnectionContext.set_SecurePassword($securedPassword) if (($server.logins).Name -Contains $SqlLogin) { return $true } else { return $false } } function DeleteDb { [CmdletBinding()] Param ( [string]$SqlServer, [string]$DatabaseName, [string]$Username, [string]$Password ) $cmd = @" IF DB_ID('$DatabaseName') IS NOT NULL BEGIN ALTER DATABASE [$DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'$DatabaseName' DROP DATABASE [$DatabaseName] END "@ if (!(DatabaseExists -SqlServer $SqlServer -DatabaseName $DatabaseName -Username $Username -Password $Password)) { Write-Output "'$DatabaseName' doesn't exist. Skipping delete..." } else { Push-Location Write-Output "Deleting SQL Database '$DatabaseName'..." Invoke-Sqlcmd $cmd -QueryTimeout 3600 -ServerInstance $SqlServer -Username $Username -Password $Password Write-Output "Done." Pop-Location } } function LoadDacfx { $versions = @("140", "130", "120") foreach ($version in $versions) { $dacfxPath = "C:\Program Files (x86)\Microsoft SQL Server\$version\DAC\bin\Microsoft.SqlServer.Dac.dll" if (Test-Path $dacfxPath) { Add-Type -Path $dacfxPath return } } throw "Microsoft.SqlServer.Dac.dll doesn't exist..." } function CreateDbUser { [CmdletBinding()] Param ( [string]$SqlServer, [string]$Username, [string]$Password, [string]$DbUser, [string]$DbPassword, [string]$DatabaseName ) $cmd = @" Use [$DatabaseName] Go IF NOT EXISTS (SELECT name FROM [sys].[database_principals] WHERE [type] = 'S' AND name = N'$DbUser') BEGIN ALTER DATABASE [$DatabaseName] SET CONTAINMENT = partial CREATE USER [$DbUser] WITH PASSWORD = '$DbPassword'; EXEC sp_addrolemember 'db_datareader', [$DbUser]; EXEC sp_addrolemember 'db_datawriter', [$DbUser]; GRANT EXECUTE TO [$DbUser]; END "@ Write-Output "Creating database user '$DbUser' for '$DatabaseName' database..." Push-Location Invoke-Sqlcmd $cmd -QueryTimeout 3600 -ServerInstance $SqlServer -Username $Username -Password $Password Pop-Location Write-Output "Creating database user '$DbUser' done." } function SetDbOwner { [CmdletBinding()] Param ( [string]$SqlServer, [string]$SqlLogin, [string]$DatabaseName, [string]$Username, [string]$Password ) if (!(DatabaseExists -SqlServer $SqlServer -DatabaseName $DatabaseName -Username $Username -Password $Password)) { Write-Output "'$DatabaseName' doesn't exist. Skipping set db_owner..." } else { Write-Output "Setting db_owner = '$SqlLogin' for '$DatabaseName' database..." $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SqlServer foreach ($db in $server.databases) { if ($db.name -eq $DatabaseName) { Invoke-Sqlcmd -ServerInstance $SqlServer -Database $DatabaseName -Query "EXEC sp_changedbowner '$SqlLogin'" } } Write-Output "Setting db_owner = '$SqlLogin' for '$DatabaseName' database done." } } # Private functions function EnableContainedDatabaseAuth { [CmdletBinding()] Param ( [string]$SqlServer, [string]$Username, [string]$Password ) Write-Output "Enable contained database authentication started..." $cmd = @" sp_configure 'contained database authentication', 1; GO RECONFIGURE; GO "@ Push-Location Invoke-Sqlcmd $cmd -QueryTimeout 3600 -ServerInstance $SqlServer -Username $Username -Password $Password Pop-Location Write-Output "Enable contained database authentication done." } function DeleteDatabases { [CmdletBinding()] Param ( [string]$SqlServer, [string[]]$Databases, [string]$Username, [string]$Password ) Write-Output "Delete existing databases started..." foreach ($db in $Databases) { DeleteDb -SqlServer $SqlServer -DatabaseName $db -Username $Username -Password $Password } Write-Output "Delete existing databases done." } function DeleteLogin { [CmdletBinding()] Param ( [string]$SqlServer, [string]$SqlLogin, [string]$Username, [string]$Password ) Write-Output "Delete SQL Login '$SqlLogin' started..." $cmd = @" USE [master] GO DROP LOGIN [$SqlLogin] GO "@ if (!(SqlLoginExists -SqlServer $SqlServer -SqlLogin $SqlLogin -Username $Username -Password $Password)) { Write-Output "'$SqlLogin' doesn't exist. Skipping delete..." } else { Push-Location Write-Output "Deleting SQL Login '$SqlLogin'..." Invoke-Sqlcmd $cmd -QueryTimeout 3600 -ServerInstance $SqlServer -Username $Username -Password $Password Write-Output "Done." Pop-Location } Write-Output "Delete SQL Login '$SqlLogin' done." } function DeployDacpac { [CmdletBinding()] Param ( [string]$SqlServer, [string]$Username, [string]$Password, [string]$LocalDbUsername, [string]$LocalDbPassword, [string]$Dacpac, [string]$TargetDatabaseName ) if (DatabaseExists -SqlServer $SqlServer -DatabaseName $TargetDatabaseName -Username $Username -Password $Password) { Write-Output "'$TargetDatabaseName' database exists. Dacpac deployement won't be executed." } else { LoadDacfx # Load Dac $dacpackage = [Microsoft.SqlServer.Dac.DacPackage]::Load($Dacpac) # Setup DacServices $connectionString = "server=$SqlServer;User ID=$Username;Password=$Password;" $dacServices = New-Object Microsoft.SqlServer.Dac.DacServices $connectionString $deployOptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions $deployOptions.AllowIncompatiblePlatform = $true # Deploy package Write-Output "Starting Dacpac deployment for '$TargetDatabaseName'..." $null = $dacServices.GenerateDeployScript($dacpackage, $TargetDatabaseName, $deployOptions) $null = $dacServices.Deploy($dacpackage, $TargetDatabaseName, $true, $deployOptions, $null) Write-Output "Dacpac deployed '$TargetDatabaseName' successfully." } CreateDbUser -SqlServer $SqlServer -Username $Username -Password $Password -DbUser $LocalDbUsername -DbPassword $LocalDbPassword -DatabaseName $TargetDatabaseName } Export-ModuleMember -Function "DeleteDatabases" Export-ModuleMember -Function "DeleteLogin" Export-ModuleMember -Function "DeployDacpac" Export-ModuleMember -Function "EnableContainedDatabaseAuth" |