Last active
October 21, 2016 14:31
-
-
Save wvankuipers/9732ba8b06986f61b0f4e540f5200ff2 to your computer and use it in GitHub Desktop.
Sonarcube MSSQL DB installer script (Powershell)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | |
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | |
cls | |
$loginName = "sonarcube" | |
$loginPassword = "*************" | |
$dbName = "sonarcube" | |
$roleName = "db_owner" | |
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("${env:ComputerName}\SQLEXPRESS") | |
# Enable TCP/IP mode | |
$smo = 'Microsoft.SqlServer.Management.Smo.' | |
$wmi = new-object ($smo + 'Wmi.ManagedComputer') | |
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='SQLEXPRESS']/ServerProtocol[@Name='Tcp']" | |
$Tcp = $wmi.GetSmoObject($uri) | |
$Tcp.IsEnabled = $true | |
$Tcp.Alter() | |
if ($srv.Logins.Contains($loginName)) | |
{ | |
Write-Host("Deleting the existing login $loginName.") | |
$srv.Logins[$loginName].Drop() | |
} | |
$DBObject = $srv.Databases[$dbName] | |
#check database exists on server | |
if ($DBObject) | |
{ | |
Write-Host("Dropping the existing database $dbName.") | |
#instead of drop we will use KillDatabase | |
#KillDatabase drops all active connections before dropping the database. | |
$srv.KillDatabase($dbName) | |
} | |
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, $dbName) | |
# set collation mode to SQL_Latin1_General_CP1_CS_AS | |
$db.Create() | |
$db.Collation = "SQL_Latin1_General_CP1_CS_AS" | |
$db.Alter() | |
Write-Host $db.CreateDate | |
$database = $srv.Databases[$dbName] | |
if ($database.Users[$loginName]) | |
{ | |
Write-Host("Dropping user $loginName on $database.") | |
$database.Users[$loginName].Drop() | |
} | |
$Login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $srv, $loginName | |
$Login.LoginType = 'SqlLogin' | |
$login.PasswordExpirationEnabled = $false | |
$login.Create($loginPassword) | |
$dbUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User -ArgumentList $database, $loginName | |
$dbUser.Login = $loginName | |
$dbUser.Create() | |
Write-Host("User $loginName created successfully.") | |
#assign database role for a new user | |
$dbrole = $database.Roles[$roleName] | |
$dbrole.AddMember($loginName) | |
$dbrole.Alter() | |
Write-Host("User $dbUser successfully added to $roleName role.") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment