powershell-scripts/mssql-server.ps1 at master · Codeholics/powershell-scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
# This needs to be ran by a user with Admin credentials on the target database

#requires -version 4.0

# Imports
Import-Module SQLPS -DisableNameChecking

<#
 # Global Vars
 #>

$pkg = $PWD

$instanceName = "yourdb.server.com"

$loginName = "dbuser"
$password = "dbuser"
$databaseNames = "yourDBName1", "yourDBName2"
$bakPath = "G:\Path\To\Backup.bak"
$role = "db_owner"

$setSQLFile = "$pkg\SQL\tsqlFile.sql"

# Create Server Object
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

<#
 # Functions
 #>

Function addSQLUser($user, $password)
{
    if($server.Logins.Contains($user))
    {
        Write-Host("Login: $user Exists")
        return
    }
    else
    {
        $newUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.login -ArgumentList $server, $loginName
        $newUser.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
        $newUser.PasswordExpirationEnabled = $false
        $newUser.PasswordPolicyEnforced = $false ## not recommended ##
        $newUser.Create($password)
        Write-Host("Login: $user Created")
    }
}

Function mapSQLUser($user, $role, [string[]]$databaseNames)
{
    foreach($databaseToMap in $databaseNames)
    {
        $database = $server.Databases[$databaseToMap]
        if($database.Users[$user])
        {
            Write-Host("Database user $user exists on $database.")
        }
        else
        {
            $dbUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User -ArgumentList $database, $user
            $dbUser.Login = $user
            $dbUser.Create()
            Write-Host("$user Mapped to $database")
            # Assign role: might be good in a new function
            $dbrole = $database.Roles[$role]
            $dbrole.AddMember($user)
            $dbrole.Alter()
            Write-Host("$user added as $role")
        }
    }
}

Function createDB($server, $db)
{
    try
    {
        Write-Host("Creating Database: $db")
        $db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database($server, $db)
        $db.Create()
    }
    catch
    {
        $_
    }
}

Function restoreDB($server, $db, $path)
{
    try
    {
        Write-Host("Restoring Database: $db")
        Restore-SqlDatabase -ServerInstance $server -Database $db -BackupFile $path -ReplaceDatabase
    }
    catch
    {
        $_
    }
}

Function tsql($queryFile, $server)
{
    Invoke-Sqlcmd -InputFile $queryFile -Server $server
}

<#
 # Main
 #>

Write-Host("Installing...")
createDB $server $databaseNames
restoreDB $instanceName $databaseNames $bakPath
tsql $setSQLFile $server
addSQLUser $loginName $password
mapSQLUser $loginName $role $databaseNames