Wednesday, 25 September 2013

PowerShell creating database and associating login

I have been working on droping new database as part of my continuous deployment implementation.
For this I have needed to create couple powerhsell scripts.

Here is one example (prototype how it works)

Note: it needs tidy up.







$Instance   = ".\SQLEXPRESS"
$LoginName  = "Login name"
$Password   = "loginPassword"
$DBName     = "Database"

#Get the server object
$srv = New-Object ("Microsoft.SqlServer.Management.SMO.Server") $instance

        $varDBUser = "Usern"
        $varDBPassword = "password"
        $srv.ConnectionContext.LoginSecure = $false
        $srv.ConnectionContext.Login = $varDBUser
        $srv.ConnectionContext.Password = $varDBPassword

#Get the login object if it exists
$Login = $srv.Logins.Item($LoginName)

IF (!($Login))  #check to see if login already exists
{
   Write-Host " login does not exists, creating"

 #it doesn't, so instantiate a new login object
    $Login = New-Object ("Microsoft.SqlServer.Management.SMO.Login") ($Server, $LoginName)

    #make it a SQL Login
    $Login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin

    #Create it on the server with the specified password
    $Login.Create($Password)



}

#Get the database object
$DB = $srv.Databases[$DBName]
 
#Get the user object if it exists
$User = $DB.Users[$LoginName]

if (!($User)) # check to see if the user is already in the database
{
    #it doesn't, so add it
    $User = New-Object ("Microsoft.SqlServer.Management.SMO.User") ($DB, $LoginName)
    $User.Login = $LoginName
    $User.Create()
}