A good script for adding SQL Logins and SQL users to databases

As a part of the Jumpstart installation we grant our Application pools and service accounts rights to a whole bunch of databases. One of the problems we found in the test environment is that the underlying SQL PowerShell provider dont really work well if there is a problem connecting or if the logins/users are already existing. So in order to deal with this I added logic today to deal with existing logins and users, but since I didnt really find a good one on the Internet with the same logic I decided to post it here.

 

$instance = "JS12"
$amUserGroup = "JS10DOM\Domain Admins"
$amDBName = "ConfigMgr_CEN"

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Create an SMO connection to the instance
$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

#Reference the database and display the date when it was created.

$db = $server.Databases[$amDBName]
$db.createDate

Try {
$server.Logins | Out-Null # Throws and exception if we cannot connect to the server
}

catch [Exception] {
Write-Error -Message $_.Exception.Message -Category ObjectNotFound
exit 1
}

# Check that login exists
$login = [Microsoft.SqlServer.Management.Smo.Login] $server.Logins[$amUserGroup]
if ( ! $login ) {
Write "The login $LoginName does not appear to be valid, will create it"
$login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $server, $amUserGroup
$login.LoginType = 'WindowsGroup'
$login.DefaultDatabase = $amDBName
$login.Create('')
}

else
{
Write "We have a login with the same name, will use that!"
}

$user = $db.Users[$login.Name]
if ($user -eq $null) {

# Not present, so add it
$user = New-Object('Microsoft.SqlServer.Management.Smo.User') $db, $amUserGroup
$user.Login = $amUserGroup
$user.create()
}

else {
Write "We have a login with the same name, will use that!"
}

# Check to see if the user is a member of the db_reader role
if ($user.IsMember('db_datareader') -ne $True) {

# Not a member, so add that role
$role = $db.Roles['db_datareader']
$role.AddMember($amUserGroup)
}

if ($user.IsMember('db_datawriter') -ne $True) {
# Not a member, so add that role
$role = $db.Roles['db_datawriter']
$role.AddMember($amUserGroup)
}