Author Posts

June 14, 2018 at 9:10 pm

I store the rights and the names of groups that get granted those rights, and loop through them, granting the rights. Perhaps not the best way, I'm open to suggestions, but what I'm stuck with is making this environment agnostic. That is having it, so the 'Dev" part of the name is a variable that I'd set earlier in the script. I've tried replacing the dev in all those groups with $env but the variable was never set to the value passed.
Any suggestions ? Happy to try a different approach, if there is a better one.

$GlobalUserRoles = [PSCustomObject]@{  
  db_owner =   'Domain\svc_csdev','Domain\G_release_manager','Domain\G_SQL_DomainDev_DBO'
  db_datawriter = 'Domain\G_SQL_DomainDEV_RW','Domain\svc_sqlpayroll'
  db_datareader = 'Domain\G_SQL_DomainDEV_R','Domain\G_SQL_DomainDEV_RW','Domain\svc_sqlpayroll','Domain\svc_sqldelta','Domain\svc_sqlcrm_dev'
}

    $GlobalUserROles |
    ForEach-Object{
        $_.db_owner |
        ForEach-Object{
        Add-DbUsers -InstanceName $instance -Database $DbName -User $_ -Role 'db_owner'
        }
        $_.db_datawriter |
        ForEach-Object{
        Add-DbUsers -InstanceName $instance -Database $DbName -User $_ -Role 'db_datawriter'
        }
        $_.db_datareader |
        ForEach-Object{
        Add-DbUsers -InstanceName $instance -Database $Dbname -user $_ -Role 'db_datareader'
        }
    }

I had tried this, but never the variable was not up dated.

$GlobalUserRoles = [PSCustomObject]@{  
  db_owner =   'Domain\svc_cs$env','Domain\G_release_manager','Domain\G_SQL_Domain$env_DBO'
  db_datawriter = 'Domain\G_SQL_Domain$env_RW','Domain\svc_sqlpayroll'
  db_datareader = 'Domain\G_SQL_Domain$env_R','Domain\G_SQL_Domain$env_RW','Domain\svc_sqlpayroll','Domain\svc_sqldelta','Domain\svc_sqlcrm_$env'
}

June 14, 2018 at 9:12 pm

The magic "replace a variable with its contents" only works in double quotes. You're using single-quoted strings.

Be a little careful with $env; that's a predefined variable that provides a shortcut to the ENV: drive of environment variables. I'd choose something else.

June 14, 2018 at 10:21 pm

You can make your code a little easier one of two ways as well. Process the object like a hash table:

$GlobalUserRoles = [PSCustomObject]@{  
  db_owner =   'Domain\svc_csdev','Domain\G_release_manager','Domain\G_SQL_DomainDev_DBO'
  db_datawriter = 'Domain\G_SQL_DomainDEV_RW','Domain\svc_sqlpayroll'
  db_datareader = 'Domain\G_SQL_DomainDEV_R','Domain\G_SQL_DomainDEV_RW','Domain\svc_sqlpayroll','Domain\svc_sqldelta','Domain\svc_sqlcrm_dev'
}

 foreach ($role in $GlobalUserRoles.PSObject.Properties) {
    foreach ($group in $role.Value) {
        $params = @{
            InstanceName = $instance 
            Database     = $DbName 
            User         = $group 
            Role         = $role.Name
        }
        #Add-DbUsers @params
        Write-Host ("Adding group {0} with role {1}." -f $group, $role.Name)
    }
}

or change the object to be a hashtable and process it with .GetEnumerator():

$GlobalUserRoles = @{  
  db_owner =   'Domain\svc_csdev','Domain\G_release_manager','Domain\G_SQL_DomainDev_DBO'
  db_datawriter = 'Domain\G_SQL_DomainDEV_RW','Domain\svc_sqlpayroll'
  db_datareader = 'Domain\G_SQL_DomainDEV_R','Domain\G_SQL_DomainDEV_RW','Domain\svc_sqlpayroll','Domain\svc_sqldelta','Domain\svc_sqlcrm_dev'
}

 foreach ($role in $GlobalUserRoles.GetEnumerator()) {
    foreach ($group in $role.Value) {
        $params = @{
            InstanceName = $instance 
            Database     = $DbName 
            User         = $group 
            Role         = $role.Name
        }
        #Add-DbUsers @params
        Write-Host ("Adding group {0} with role {1}." -f $group, $role.Name)
    }
}

Output:

Adding group Domain\G_SQL_DomainDEV_R with role db_datareader.
Adding group Domain\G_SQL_DomainDEV_RW with role db_datareader.
Adding group Domain\svc_sqlpayroll with role db_datareader.
Adding group Domain\svc_sqldelta with role db_datareader.
Adding group Domain\svc_sqlcrm_dev with role db_datareader.
Adding group Domain\G_SQL_DomainDEV_RW with role db_datawriter.
Adding group Domain\svc_sqlpayroll with role db_datawriter.
Adding group Domain\svc_csdev with role db_owner.
Adding group Domain\G_release_manager with role db_owner.
Adding group Domain\G_SQL_DomainDev_DBO with role db_owner.

June 14, 2018 at 10:51 pm

I had totally missed that ' versus " , when I modified it to " on the process I'd been trying, it worked.
I've also changed $env for $environment in full
Thanks.

June 14, 2018 at 10:53 pm

Rob, thanks. Despite what I was trying now working after fixing what Don pointed out, I'm going to give the Hashtable solution you suggested a try. I like that better than what I'm doing.