[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.")