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) }