# encoding: ascii
# api: powershell
# title: SqlProxy.psm1
# description: Based on functions in SQLPSX. SqlProxy.psm1 module is used for administering SQL Server logins, users, and roles. Designed to be used with PS Remoting.  All actions audited to a custom Eventlog.  See Write-SqlProxyLog for log setup details
# version: 0.1
# type: script
# author: Chad Miller
# license: CC0
# function: Get-SqlConnection
# x-poshcode-id: 3040
# x-derived-from-id: 3077
# x-archived: 2011-12-16T19:37:06
# x-published: 2011-11-07T07:42:00
#
#
# ---------------------------------------------------------------------------
### <Author>
### Chad Miller 
### </Author>
### <Description>
### Based on functions in SQLPSX. SqlProxy.psm1 module is used for administering
### SQL Server logins, users, and roles. Designed to be used with PS Remoting.
### All actions audited to a custom Eventlog. 
### See Write-SqlProxyLog for log setup details
### </Description>
# ---------------------------------------------------------------------------
try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

try {add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.Smo"; $smoVersion = 9}

try {add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName "Microsoft.SqlServer.SqlEnum"; $smoVersion = 9}

try
{
    try {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
    catch {add-type -AssemblyName "Microsoft.SqlServer.SMOExtended" -EA Stop}
}
catch {Write-Warning "SMOExtended not available"}

$EventID = @{
"Add-SqlDatabaseRoleMember"=0
"Add-SqlLogin"=1
"Add-SqlServerRoleMember"=2
"Add-SqlUser"=3
"Remove-SqlDatabaseRoleMember"=4
"Remove-SqlLogin"=5
"Remove-SqlServerRoleMember"=6
"Remove-SqlUser"=7
"Rename-SqlLogin"=8
"Set-SqlLogin"=9
"Set-SqlLoginDefaultDatabase"=10
}

#######################
<#
.SYNOPSIS
Gets a ServerConnection.
.DESCRIPTION
The Get-SqlConnection function  gets a ServerConnection to the specified SQL Server.
.INPUTS
None
    You cannot pipe objects to Get-SqlConnection 
.OUTPUTS
Microsoft.SqlServer.Management.Common.ServerConnection
    Get-SqlConnection returns a Microsoft.SqlServer.Management.Common.ServerConnection object.
.EXAMPLE
Get-SqlConnection "Z002\sql2K8"
This command gets a ServerConnection to SQL Server Z002\SQL2K8.
.EXAMPLE
Get-SqlConnection "Z002\sql2K8" "sa" "Passw0rd"
This command gets a ServerConnection to SQL Server Z002\SQL2K8 using SQL authentication.
.LINK
Get-SqlConnection 
#>
function Get-SqlConnection
{
    param(
    [Parameter(Position=0, Mandatory=$true)] [string]$sqlserver,
    [Parameter(Position=1, Mandatory=$false)] [string]$username, 
    [Parameter(Position=2, Mandatory=$false)] [string]$password
    )

    Write-Verbose "Get-SqlConnection $sqlserver"
    
    try {
        if($Username -and $Password)
        { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver,$username,$password }
        else
        { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver }
            
        $con.Connect()
        Write-Output $con
    }
    catch { $message = $_.Exception.GetBaseException().Message
            write-error $message
    }
    
} #Get-ServerConnection

#######################
<#
.SYNOPSIS
Gets an SMO Server object.
.DESCRIPTION
The Get-SqlServer function  gets a SMO Server object for the specified SQL Server.
.INPUTS
None
    You cannot pipe objects to Get-SqlServer 
.OUTPUTS
Microsoft.SqlServer.Management.Smo.Server
    Get-SqlServer returns a Microsoft.SqlServer.Management.Smo.Server object.
.EXAMPLE
Get-SqlServer "Z002\sql2K8"
This command gets an SMO Server object for SQL Server Z002\SQL2K8.
.EXAMPLE
Get-SqlServer "Z002\sql2K8" "sa" "Passw0rd"
This command gets a SMO Server object for SQL Server Z002\SQL2K8 using SQL authentication.
.LINK
Get-SqlServer 
#>
function Get-SqlServer
{
    param(
    [Parameter(Position=0, Mandatory=$true)] [string]$sqlserver,
    [Parameter(Position=1, Mandatory=$false)] [string]$username, 
    [Parameter(Position=2, Mandatory=$false)] [string]$password,
    [Parameter(Position=3, Mandatory=$false)] [string]$StatementTimeout=0
    )
    #When $sqlserver passed in from the SMO Name property, brackets
    #are automatically inserted which then need to be removed
    $sqlserver = $sqlserver -replace "\[|\]"

    Write-Verbose "Get-SqlServer $sqlserver"

    $con = Get-SqlConnection $sqlserver $Username $Password

    $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $con
    #Some operations might take longer than the default timeout of 600 seconnds (10 minutes). Set new default to unlimited
    $server.ConnectionContext.StatementTimeout = $StatementTimeout
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject")
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction], "IsSystemObject")
    #trap { "Check $SqlServer Name"; continue} $server.ConnectionContext.Connect() 
    Write-Output $server
    
} #Get-SqlServer

#######################
<#
.SYNOPSIS
Gets an SMO Database object.
.DESCRIPTION
The Get-SqlDatabase function  gets an SMO Database object for the specified SQL Database or collection of Database objects.
.INPUTS
None
    You cannot pipe objects to Get-SqlDatabase 
.OUTPUTS
Microsoft.SqlServer.Management.Smo.Database
    Get-SqlDatabase returns a Microsoft.SqlServer.Management.Smo.Database object.
.EXAMPLE
Get-SqlDatabase "Z002\sql2K8"
This command gets a collection of SMO Database objects for SQL Server Z002\SQL2K8.
.EXAMPLE
Get-SqlDatabase $(Get-SqlServer "Z002\sql2K8" "sa" "Passw0rd") "pubs"
This command gets a SMO Database object for SQL database pubs on the SQL Server Z002\SQL2K8 using SQL authentication.
.LINK
Get-SqlDatabase 
#>
function Get-SqlDatabase
{ 
    param(
    [Parameter(Position=0, Mandatory=$true)] $sqlserver,
    [Parameter(Position=1, Mandatory=$false)] [string]$dbname
    )

    switch ($sqlserver.GetType().Name)
    {
        'String' { $server = Get-SqlServer $sqlserver }
        'Server' { $server = $sqlserver }
        default { throw 'Get-SqlDatabase:Param `$sqlserver must be a String or Server object.' }
    }

    Write-Verbose "Get-SqlDatabase $($server.Name) $dbname"

    if ($dbname)
    { if ($server.Databases.Contains("$dbname") -and $server.Databases[$dbname].IsAccessible)
        {$server.Databases[$dbname]} 
      else
        {throw "Database $dname does not exist or is not accessible."}
    }
    else
    { $server.Databases | where {$_.IsAccessible -eq $true} }

} # Get-SqlDatabase

#######################
<#
.SYNOPSIS
Executes a query and returns an array of System.Data.DataRow.
.DESCRIPTION
The Get-SqlData function executes a query and returns an array of System.Data.DataRow.
.INPUTS
None
    You cannot pipe objects to Get-SqlData 
.OUTPUTS
System.Data.DataRow
    Get-SqlData returns an array of System.Data.DataRow.
.EXAMPLE
Get-SqlData "Z002\sql2K8" "pubs" "select * from authors"
This command executes the specified SQL query using Windows authentication.
.EXAMPLE
$server = Get-SqlServer "Z002\sql2K8" "sa" "Passw0rd"
Get-SqlData $server "pubs" "select * from authors"
This command executes the specified SQL query using SQL authentication.
.LINK
Get-SqlData 
#>
function Get-SqlData
{
    param(
    [Parameter(Position=0, Mandatory=$false)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] $dbname,
    [Parameter(Position=2, Mandatory=$true)] [string]$qry
    )

    switch ($dbname.GetType().Name)
    {
        'String' { $database = Get-SqlDatabase $sqlserver $dbname }
        'Database' { $database = $dbname }
        default { throw "Get-SqlData:Param '`$dbname' must be a String or Database object." }
    }

    #Write-Verbose "Get-SqlData $($database.Parent.Name) $($database.Name) $qry"
    Write-Verbose "Get-SqlData $($database.Parent.Name) $($database.Name)"

    $ds = $database.ExecuteWithResults("$qry")
    $ds.Tables | foreach { $_.Rows}    

}# Get-SqlData

#######################
<#
.SYNOPSIS
 Executes a query that does not return a result set.
.DESCRIPTION
The Set-SqlData function executes a query that does not return a result set.
.INPUTS
None
    You cannot pipe objects to Set-SqlData 
.OUTPUTS
None
    Set-SqlData does not produce any output.
.EXAMPLE
Set-SqlData "Z002\sql2K8" "pubs" "Update authors set au_lname = 'Brown' WHERE au_lname = 'White'"
This command executes the specified SQL query using Windows authentication.
.EXAMPLE
$server = Set-SqlServer "Z002\sql2K8" "sa" "Passw0rd"
Set-SqlData $server "pubs" "Update authors set au_lname = 'Brown' WHERE au_lname = 'White'"
This command executes the specified SQL query using SQL authentication.
.LINK
Set-SqlData 
#>
function Set-SqlData
{
    param(
    [Parameter(Position=0, Mandatory=$false)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] [string]$dbname,
    [Parameter(Position=2, Mandatory=$true)] [string]$qry
    )

    switch ($dbname.GetType().Name)
    {
        'String' { $database = Get-SqlDatabase $sqlserver $dbname }
        'Database' { $database = $dbname }
        default { throw "Set-SqlData:Param '`$dbname' must be a String or Database object." }
    } 
    
    #Write-Verbose "Set-SqlData $($database.Parent.Name) $($database.Name) $qry"
    Write-Verbose "Set-SqlData $($database.Parent.Name) $($database.Name)"
    
    $database.ExecuteNonQuery("$qry")

}# Set-SqlData

#######################
<#
.SYNOPSIS
Adds a login to a SQL Server.
.DESCRIPTION
The Add-SqlLogin function adds a new login to the specified SQL server.
.INPUTS
None
    You cannot pipe objects to Add-SqlLogin
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
Add-SqlLogin "Z002\sql2k8" "TestPSUnit" "SQLPSXTesting" 'SqlLogin' 
This command adds the TestPSUnit login to the Z002\sql2k8 server.
.EXAMPLE
$server = Get-SqlServer "Z002\sql2k8"
Add-SqlLogin $server "TestPSUnit" "SQLPSXTesting" 'SqlLogin'
This command adds the TestPSUnit login to the Z002\sql2k8 server.
.LINK
Add-SqlLogin 
#>
function Add-SqlLogin
{
    param(
    [Parameter(Position=0, Mandatory=$true)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] [string]$name,
    [Parameter(Position=2, Mandatory=$false)] [System.Security.SecureString]$password,
    [Parameter(Position=3, Mandatory=$false)] [Microsoft.SqlServer.Management.Smo.LoginType]$logintype='WindowsUser',
    [Parameter(Position=4, Mandatory=$false)] [string]$DefaultDatabase='master',
    [Parameter(Position=5, Mandatory=$false)] [switch]$PasswordExpirationEnabled,
    [Parameter(Position=6, Mandatory=$false)] [switch]$PasswordPolicyEnforced,
    [Parameter(Position=7, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=8, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($sqlserver.GetType().Name)
    {
        'String' { $server = Get-SqlServer $sqlserver }
        'Server' { $server = $sqlserver }
        default { throw 'Add-SqlLogin:Param `$sqlserver must be a String or Server object.' }
    }

    Write-Verbose "Add-SqlLogin $($server.Name) $name"

    try { 
        $login = new-object ('Microsoft.SqlServer.Management.Smo.Login') $server, $name
        $login.DefaultDatabase = $defaultDatabase
        $login.LoginType = $logintype

        if ($logintype -eq 'SqlLogin')
        {
            if ($server.Information.Version.Major -ne '8')
            {
                $login.PasswordExpirationEnabled = $($PasswordExpirationEnabled.IsPresent)
                $login.PasswordPolicyEnforced = $($PasswordPolicyEnforced.IsPresent)
            }
            $login.Create($password)
        }
        else {
            $login.Create()
        }

        $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
        write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
    }
    catch {
        $message = $_.Exception.GetBaseException().Message
        $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
        write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
        Write-Error $message
    }

} #Add-SqlLogin

#######################
<#
.SYNOPSIS
Removes a login from a SQL Server.
.DESCRIPTION
The Remove-SqlLogin function removes a login from the specified SQL server.
.INPUTS
None
    You cannot pipe objects to Remove-SqlLogin
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
Remove-SqlLogin "Z002\sql2k8" "TestPSUnit"
This command removes the TestPSUnit login from the Z002\sql2k8 server.
.EXAMPLE
$server = Get-SqlServer "Z002\sql2k8"
Remove-SqlLogin $server "TestPSUnit"
This command removes the TestPSUnit login from the Z002\sql2k8 server.
.LINK
Remove-SqlLogin 
#>
function Remove-SqlLogin
{

    param(
    [Parameter(Position=0, Mandatory=$true)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] [string]$name,
    [Parameter(Position=2, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=3, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($sqlserver.GetType().Name)
    {
        'String' { $server = Get-SqlServer $sqlserver }
        'Server' { $server = $sqlserver }
        default { throw 'Remove-SqlLogin:Param `$sqlserver must be a String or Server object.' }
    }

    Write-Verbose "Remove-SqlLogin $($server.Name) $name"

    try {
        $login = $server.Logins["$name"]
        if ($login) {
            $login.Drop()
            $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
            write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
        }
        else
        { throw "Login $name does not exist on server $($server.Name)." }
    }
    catch {
        $message = $_.Exception.GetBaseException().Message
        $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
        write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
        Write-Error $message
    }

} #Remove-SqlLogin

#######################
<#
.SYNOPSIS
Removes a login from a SQL Server.
.DESCRIPTION
The Set-SqlLogin function changes a SQL Login Password.
.INPUTS
None
    You cannot pipe objects to Set-SqlLogin
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
$pwd_secure_string = read-host "Enter a Password:" -assecurestring
Set-SqlLogin "Z002\sql2k8" "TestPSUnit" $pwd_secure_string
This command sets the password for TestPSUnit login.
.LINK
Set-SqlLogin 
#>
function Set-SqlLogin
{

    param(
    [Parameter(Position=0, Mandatory=$true)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] [string]$name,
    [Parameter(Position=2, Mandatory=$true)] [string]$password,
    [Parameter(Position=3, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=4, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($sqlserver.GetType().Name)
    {
        'String' { $server = Get-SqlServer $sqlserver }
        'Server' { $server = $sqlserver }
        default { throw 'Set-SqlLogin:Param `$sqlserver must be a String or Server object.' }
    }

    Write-Verbose "Set-SqlLogin $($server.Name) $name"

    try {
        $login = $server.Logins["$name"]
        if ($login) {
	    if ($server.Information.Version.Major -eq '8') {
                $login.ChangePassword($password)
            }
            else {
                $login.ChangePassword($password,$true,$false)
            }
            $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | ?{$_.Key -ne 'password'} | %{"`n$($_.Key)=$($_.Value)"})
            write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
        }
        else
        { throw "Login $name does not exist on server $($server.Name)." }
    }
    catch {
        $message = $_.Exception.GetBaseException().Message
        $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | ?{$_.Key -ne 'password'} | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
        write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
        Write-Error $message
    }

} #Set-SqlLogin

#######################
<#
.SYNOPSIS
Removes a login from a SQL Server.
.DESCRIPTION
The Set-SqlLoginDefaultDatabase function changes a SQL Login default database.
.INPUTS
None
    You cannot pipe objects to Set-SqlLoginDefaultDatabase
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
Set-SqlLoginDefaultDatabase "Z002\sql2k8" "TestPSUnit" 'master'
This command sets the default database for TestPSUnit login.
.LINK
Set-SqlLoginDefaultDatabase 
#>
function Set-SqlLoginDefaultDatabase
{

    param(
    [Parameter(Position=0, Mandatory=$true)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] [string]$name,
    [Parameter(Position=2, Mandatory=$true)] [string]$DefaultDatabase,
    [Parameter(Position=3, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=4, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($sqlserver.GetType().Name)
    {
        'String' { $server = Get-SqlServer $sqlserver }
        'Server' { $server = $sqlserver }
        default { throw 'Set-SqlLoginDefaultDatabase:Param `$sqlserver must be a String or Server object.' }
    }

    Write-Verbose "Set-SqlLoginDefaultDatabase $($server.Name) $name"

    try {
        $login = $server.Logins["$name"]
        if ($login) {
            $login.DefaultDatabase = $DefaultDatabase
            $login.Alter()
            $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
            write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
        }
        else
        { throw "Login $name does not exist on server $($server.Name)." }
    }
    catch {
        $message = $_.Exception.GetBaseException().Message
        $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
        write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
        Write-Error $message
    }

} #Set-SqlLoginDefaultDatabase

#######################
<#
.SYNOPSIS
Removes a login from a SQL Server.
.DESCRIPTION
The Rename-SqlLogin function renames a SQL Login.
.INPUTS
None
    You cannot pipe objects to Rename-SqlLogin
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
Rename-SqlLogin "Z002\sql2k8" "TestPSUnit" "CheckPSUnit"
This command renames the login TestPSUnit.
.LINK
Rename-SqlLogin 
#>
function Rename-SqlLogin
{

    param(
    [Parameter(Position=0, Mandatory=$true)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] [string]$name,
    [Parameter(Position=2, Mandatory=$true)] [string]$newname,
    [Parameter(Position=3, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=4, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($sqlserver.GetType().Name)
    {
        'String' { $server = Get-SqlServer $sqlserver }
        'Server' { $server = $sqlserver }
        default { throw 'Rename-SqlLogin:Param `$sqlserver must be a String or Server object.' }
    }

    Write-Verbose "Rename-SqlLogin $($server.Name) $name $newname"

    try {
        $login = $server.Logins["$name"] 
        if ($login) {
            $login.Rename("$newName")
            $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
            write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
        }
        else
        { throw "Login $name does not exist on server $($server.Name)." }
    }
    catch {
        $message = $_.Exception.GetBaseException().Message
        $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
        write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
        Write-Error $message
    }

} #Rename-SqlLogin

#######################
<#
.SYNOPSIS
Adds a new user to a database.
.DESCRIPTION
The Add-SqlUser function adds a new user to the specified database.
.INPUTS
None
    You cannot pipe objects to Add-SqlUser
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
Add-SqlUser "Z002\sql2k8" "pubs" "TestPSUnit"
This command adds the TestPSUnit login to the pubs database.
.EXAMPLE
$database = Get-SqlDatabase "Z002\sql2k8" "pubs"
Add-SqlUser -dbname $database "TestPSUnit"
This command adds the TestPSUnit login to the pubs database.
.LINK
Add-SqlUser 
#>
function Add-SqlUser
{
    param(
    [Parameter(Position=0, Mandatory=$false)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] $dbname,
    [Parameter(Position=2, Mandatory=$true)] [string]$name,
    [Parameter(Position=3, Mandatory=$false)] [string]$login=$name,
    [Parameter(Position=4, Mandatory=$false)] [string]$defaultSchema='dbo',
    [Parameter(Position=5, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=6, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($dbname.GetType().Name)
    {
        'String' { $database = Get-SqlDatabase $sqlserver $dbname }
        'Database' { $database = $dbname }
        default { throw "Add-SqlUser:Param '`$dbname' must be a String or Database object." }
    }

    Write-Verbose "Add-SqlUser $($database.Name) $name"

    try {
        $user = new-object ('Microsoft.SqlServer.Management.Smo.User') $database, $name
        $user.Login = $login
        $loginObject = $sqlserver.Logins["$login"]
	if ($database.parent.Information.Version.Major -ne '8' -and $loginObject.LoginType -ne "WindowsGroup" )
	{ $user.DefaultSchema = $defaultschema }

        $user.Create()
        $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
        write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
    }
    catch {
        $message = $_.Exception.GetBaseException().Message
        $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
        write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
        Write-Error $message
    }

} #Add-SqlUser

#######################
<#
.SYNOPSIS
Removes a user from a database.
.DESCRIPTION
The Remove-SqlUser function removes a user from the specified database.
.INPUTS
None
    You cannot pipe objects to Remove-SqlUser
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
Remove-SqlUser "Z002\sql2k8" "pubs" "TestPSUnit"
This command Removes the TestPSUnit user from the pubs database.
.EXAMPLE
$database = Get-SqlDatabase "Z002\sql2k8" "pubs"
Remove-SqlUser -dbname $database "TestPSUnit"
This command Removes the TestPSUnit user from the pubs database.
.LINK
Remove-SqlUser 
#>
function Remove-SqlUser
{
    param(
    [Parameter(Position=0, Mandatory=$false)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] $dbname,
    [Parameter(Position=2, Mandatory=$true)] [string]$name,
    [Parameter(Position=3, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=4, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($dbname.GetType().Name)
    {
        'String' { $database = Get-SqlDatabase $sqlserver $dbname }
        'Database' { $database = $dbname }
        default { throw "Remove-SqlUser:Param '`$dbname' must be a String or Database object." }
    }

    Write-Verbose "Remove-SqlUser $($database.Name) $name"

    try {
        $user = $database.Users[$name]
        if ($user) {
            $user.Drop()
            $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
            write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
        }
        else
        { throw "User $name does not exist in database $($database.Name)." }
    }
    catch {
        $message = $_.Exception.GetBaseException().Message
        $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
        write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
        Write-Error $message
    }

} #Remove-SqlUser

#######################
<#
.SYNOPSIS
Adds a login to a server role.
.DESCRIPTION
The Add-SqlServerRoleMember function adds a login to the specified server role.
.INPUTS
None
    You cannot pipe objects to Add-SqlServerRoleMember
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
Add-SqlServerRoleMember "Z002\sql2k8" "TestPSUnit" "bulkadmin"
This command adds the TestPSUnit login to the bulkadmin server role.
.EXAMPLE
$server = Get-SqlServer "Z002\sql2k8"
Add-SqlServerRoleMember $server "TestPSUnit" "bulkadmin"
This command adds the TestPSUnit login to the bulkadmin server role.
.LINK
Add-SqlServerRoleMember 
#>
function Add-SqlServerRoleMember
{
    param(
    [Parameter(Position=0, Mandatory=$true)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] [string]$loginame,
    [Parameter(Position=2, Mandatory=$true)] [string]$rolename,
    [Parameter(Position=3, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=4, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($sqlserver.GetType().Name)
    {
        'String' { $server = Get-SqlServer $sqlserver }
        'Server' { $server = $sqlserver }
        default { throw 'Add-SqlServerRoleMember:Param `$sqlserver must be a String or Server object.' }
    }

    Write-Verbose "Add-SqlServerRoleMember $($server.Name) $name"

    if($server.Logins["$loginame"])
    {
        try {
            $svrole = $server.Roles["$rolename"]
            if ($svrole) {
                $svrole.AddMember($loginame)
                $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
                write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
            }
            else
            { throw "ServerRole $rolename does not exist on server $($server.Name)." }
        }
        catch {
            $message = $_.Exception.GetBaseException().Message
            $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
            write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
            Write-Error $message
            }
    }
    else
    { throw "Login $loginame does not exist on server $($server.Name)." }

} #Add-SqlServerRoleMember

#######################
<#
.SYNOPSIS
Removes a login from a server role.
.DESCRIPTION
The Remove-SqlServerRoleMember function removes a login from the specified server role.
.INPUTS
None
    You cannot pipe objects to Remove-SqlServerRoleMember
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
Remove-SqlServerRoleMember "Z002\sql2k8" "TestPSUnit" "bulkadmin"
This command Removes the TestPSUnit login from the bulkadmin server role.
.EXAMPLE
$server = Get-SqlServer "Z002\sql2k8"
Remove-SqlServerRoleMember $server "TestPSUnit" "bulkadmin"
This command Removes the TestPSUnit login from the bulkadmin server role.
.LINK
Remove-SqlServerRoleMember 
#>
function Remove-SqlServerRoleMember
{
    param(
    [Parameter(Position=0, Mandatory=$true)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] [string]$loginame,
    [Parameter(Position=2, Mandatory=$true)] [string]$rolename,
    [Parameter(Position=3, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=4, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($sqlserver.GetType().Name)
    {
        'String' { $server = Get-SqlServer $sqlserver }
        'Server' { $server = $sqlserver }
        default { throw 'Remove-SqlServerRoleMember:Param `$sqlserver must be a String or Server object.' }
    }

    Write-Verbose "Remove-SqlServerRoleMember $($server.Name) $name"

    if($server.Logins["$loginame"])
    {
        try {
            $svrole = $server.Roles["$rolename"]
            if ($svrole) {
                $svrole.DropMember($loginame)
                $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
                write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
            }
            else
            { throw "ServerRole $rolename does not exist on server $($server.Name)." }
        }
    catch {
        $message = $_.Exception.GetBaseException().Message
        $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
        write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
        Write-Error $message
        }
    }
    else
    { throw "Login $loginame does not exist on server $($server.Name)." }

} #Remove-SqlServerRoleMember

#######################
<#
.SYNOPSIS
Adds a user or role to a database role.
.DESCRIPTION
The Add-SqlDatabaseRoleMember function adds a user or role to the specified database role.
.INPUTS
None
    You cannot pipe objects to Add-SqlDatabaseRoleMember
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
Add-SqlDatabaseRoleMember "Z002\sql2k8" "pubs" "TestPSUnit" "TestPSUnitDBRole" 
This command adds the TestUnit user to the TestPSUnitDBRole database role.
.EXAMPLE
$database = Get-SqlDatabase "Z002\sql2k8" "pubs"
Add-SqlDatabaseRoleMember -dbname $database -name "TestPSUnit" -rolename "TestPSUnitDBRole" 
This command adds the TestUnit user to the TestPSUnitDBRole database role.
.LINK
Add-SqlDatabaseRoleMember 
#>
function Add-SqlDatabaseRoleMember
{
    param(
    [Parameter(Position=0, Mandatory=$false)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] $dbname,
    [Parameter(Position=2, Mandatory=$true)] [string]$name,
    [Parameter(Position=3, Mandatory=$true)] [string]$rolename,
    [Parameter(Position=4, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=5, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($dbname.GetType().Name)
    {
        'String' { $database = Get-SqlDatabase $sqlserver $dbname }
        'Database' { $database = $dbname }
        default { throw "Add-SqlDatabaseRoleMember:Param '`$dbname' must be a String or Database object." }
    }

    Write-Verbose "Add-SqlDatabaseRoleMember $($database.Name) $name $rolename"

    if ($database.Users["$name"])
    {

        try {
            $role = $database.Roles["$rolename"]
            if ($role) {
                $role.AddMember($name)
                $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
                write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
            }
            else
            { throw "DatabaseRole $rolename does not exist in database $($database.Name)." }
        }
        catch {
            $message = $_.Exception.GetBaseException().Message
            $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
            write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
            Write-Error $message
        }
    }
    else
    { throw "User $name does not exist in database $($database.Name)." }

} #Add-SqlDatabaseRoleMember

#######################
<#
.SYNOPSIS
Removes a user or role from a database role.
.DESCRIPTION
The Remove-SqlDatabaseRoleMember function removes a user or role from the specified database role.
.INPUTS
None
    You cannot pipe objects to Remove-SqlDatabaseRoleMember
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
Remove-SqlDatabaseRoleMember "Z002\sql2k8" "pubs" "TestPSUnit" "TestPSUnitDBRole" 
This command removes the TestUnit user to the TestPSUnitDBRole database role.
.EXAMPLE
$database = Get-SqlDatabase "Z002\sql2k8" "pubs"
Remove-SqlDatabaseRoleMember -dbname $database -name "TestPSUnit" -rolename "TestPSUnitDBRole" 
This command removes the TestUnit user to the TestPSUnitDBRole database role.
.LINK
Remove-SqlDatabaseRoleMember 
#>
function Remove-SqlDatabaseRoleMember
{
    param(
    [Parameter(Position=0, Mandatory=$false)] $sqlserver,
    [Parameter(Position=1, Mandatory=$true)] $dbname,
    [Parameter(Position=2, Mandatory=$true)] [string]$name,
    [Parameter(Position=3, Mandatory=$true)] [string]$rolename,
    [Parameter(Position=4, Mandatory=$true)] [string]$ChangeOrder,
    [Parameter(Position=5, Mandatory=$false)] [string]$Description
    )

    $ErrorActionPreference = "Stop"

    $PSUserName = $PSSenderInfo.UserInfo.Identity.Name

    switch ($dbname.GetType().Name)
    {
        'String' { $database = Get-SqlDatabase $sqlserver $dbname }
        'Database' { $database = $dbname }
        default { throw "Remove-SqlDatabaseRoleMember:Param '`$dbname' must be a String or Database object." }
    }

    Write-Verbose "Remove-SqlDatabaseRoleMember $($database.Name) $name $rolename"

    if ($database.Users["$name"])
    {
        try {
            $role = $database.Roles["$rolename"]
            if ($role) {
                $role.DropMember($name)
                $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
                write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage
            }
            else
            { throw "DatabaseRole $rolename does not exist in database $($database.Name)." }
        }
        catch {
            $message = $_.Exception.GetBaseException().Message
            $logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"}) + "`n$message"
            write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage -EntryType 'Error'
            Write-Error $message
        }
    }
    else
    { throw "User $name does not exist in database $($database.Name)." }

} #Remove-SqlDatabaseRoleMember

#######################
<#
.SYNOPSIS
Writes an entry to SqlProxy Log File.
.DESCRIPTION
The Write-SqlProxyLog function writes an entry to the SqlProxy Log File.
.INPUTS
None
    You cannot pipe objects to Write-SqlProxyLog
.OUTPUTS
None
    This function does not generate any output.
.EXAMPLE
$message =  "PSUserName=$PSUserName`n" + $($psBoundParameters.GetEnumerator() | %{"$($_.Key)=$($_.Value)"})
write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $message
This command writes a message to the SQLProxy Log
.NOTES
This must be run as administrator to create the new EventLog and EventLog Source!!!
New-EventLog -LogName SqlProxy -Source SqlProxy
.LINK
Write-SqlProxyLog 
#>
function Write-SqlProxyLog
{
    param(
    [Parameter(Position=0, Mandatory=$true)] $EventID,
    [Parameter(Position=1, Mandatory=$true)] $Message,
    [Parameter(Position=2, Mandatory=$false)] $EntryType='SuccessAudit'
    )

    write-eventlog -logname SqlProxy -source SqlProxy -eventID $eventID -message $message -EntryType $EntryType
    
} #Write-SqlProxyLog
