Create-ADSQLPermissions
This code takes a set of business requirements and creates Active Directory (AD) groups and members as well as SQL Server logins, users and permissions. The code is Powershell based and can be run with only a few parameter changes. The business requirements are based on an Excel spreadsheet which can be used as a template.
In order to execute the code, you will need the appropriate permissions for AD and SQL Server as well as a machine with Microsoft Excel, Powershell 3.0 and SQL Server 2008 installed at a minimum.
Permissions Worksheet v3
The Excel worksheet is a template file with space for AD groups and members as well as SQL Server database permissions. For the database permissions, you will need the name of the AD group, the SQL Server instance, the database name, the permission needed and the action at a minimum. Examples are in the template to help guide you. Values from the “Worksheet” tab are automatically filled to the “New Groups and Members” and “Database” tab, where the code grabs the information needed.
You can download the Excel worksheet below.
Create-ADSQLPermissions
The code base is contained in the Create-ADSQLPermissions.ps1 file, the Load-Functions.ps1 file and the Start.bat file. The Start.bat file starts the process and calls Create-ADSQLPermissions.ps1, which in turn calls functions in Load-Functions.ps1.
To run the package, create a directory in the C drive on your machine called ADSQLPermissions and copy the following files to this directory:
- Start.bat
- Start-ADSQLPermissions.ps1
- Load-Functions.ps1
- Permissions Worksheet v3.xlsx
Within the Load-Functions.ps1 file, input the values for your domain controller server for the $DCServer variable. There is space for your Development, QA and Production domain controllers, although they might be the same for two or all three of your environments.
Within the Start-ADPermissions.ps1 file, input the values for your organizational unit in AD, you organizational unit in AD to search, your domain controller server and your environment for the domain controller. It may take some trial and error to get these values right for your environment. You will need values for Development, QA and Production. The variables are $orgUnit, $orgUnitSearch, $server and $env2.
For the changes discussed above, you typically will only need to make them once for your environments. Once you have them, you can save copies of the files as templates to a location and reuse them as needed.
Double click the Start.bat file. This will open the PowerShell ISE with Create-ADSQLPermissions.ps1. In the variables section, specify DEV, QA or PROD for the particular environment you are making changes for. Once this is complete, you can execute the script.
One final note; sometimes, the script will fail to create the group membership on the first run. This is because I haven’t accounted for creating groups and adding membership gracefully. To solve this problem, simply run the script again. Anything already created will be skipped and a message stating such will be presented.
Now let’s look inside the code and walk through it.
Start.bat
This file doesn’t do much except launch the Create-ADSQLPermissions.ps1 file. It includes logic to set the path and launch the file in Powershell ISE with an execution policy of unrestricted. I did not write this script from scratch. It is borrowed and altered for my needs. I include the location for the original script in the comments of the script.
Create-ADSQLPermissions.ps1
This file specifies needed variables as discussed above, loads the required modules and SnapIns, loads the functions needed and executes the functions to create the AD groups, members and SQL accounts. Again, this script and the functions below are borrowed and adapted for my needs. The locations of the original scripts are in the comments.
# Set the environment variable here
$env = "DEV" #Specify "DEV", "QA" or "PROD"
# Define global variables for the Excel file and directory location
$excelFileName = "C:Create-ADSQLPermissionsPermissions Worksheet v3.xlsx"
$csvLoc = "C:Create-ADSQLPermissions"
# Import the PowerShell module containing the AD cmdlets
Import-Module ActiveDirectory
# Import SQL SnapIns required
Add-PSSnapin SQLServerCmdletSnapin100
# Load functions
. "C:BusinessPermissionsLoad-Functions.ps1"
# Export the Excel file to a CSV format in the same directory
ExportWSToCSV -excelFileName $excelFileName -csvLoc $csvLoc
# Set the variables for the AD organization units based on the environmental variable
# $orgUnit - e.g. - "OU=Groups,OU=Accounts,OU=Patriots,DC=Dev,DC=TomBrady,DC=net"
# $orgUnitSearch - e.g. - "OU=Accounts,OU=Patriots,DC=Dev,DC=TomBrady,DC=net"
# $server - e.g. - "Patriots.net"
# $env2 - e.g. - "PROD"
if ($env -eq "DEV") {
$orgUnit = ""
$orgUnitSearch = ""
$server = ""
$env2 = ""
}
if ($env -eq "QA") {
$orgUnit = ""
$orgUnitSearch = ""
$server = ""
$env2 = ""
}
if ($env -eq "PROD") {
$orgUnit = ""
$orgUnitSearch = ""
$server = ""
$env2 = ""
}
# Execute functions
Create-ADGroupsAndMembers -CSVPath "C:Create-ADSQLPermissionsNew Groups and Members.csv" -OrgUnit $orgUnit -Domain $env
Create-SQLAccountsFromCSV -CSVPath "C:Create-ADSQLPermissionsDatabase.csv"
Load-Functions.ps1
This is where all the work happens. There are a total of seven functions defined. Let’s take a look at each of them:
ExportWSToCSV
This function converts all sheets of the Excel file to CSV files. It does this using a simple foreach loop.
function ExportWSToCSV ($excelFileName,$csvLoc)
{
$E = New-Object -ComObject Excel.Application
$E.Visible = $false
$E.DisplayAlerts = $false
$wb = $E.Workbooks.Open($excelFileName)
foreach ($ws in $wb.Worksheets)
{
$n = $ws.Name
$ws.SaveAs($csvLoc + $n + ".csv",6)
}
$E.Quit()
}
Create-ADGroupsAndMembers
This is probably the most complicated function. Each line in the CSV file is fed to a foreach loop, which parses the table and builds a hash table. If the group name is not found in the search, it will be created. Note the group will be created as a Universal group with a category of Security (not a distribution group).
Within this loop, a while loop is create for the members. Each member is checked to see if it’s already a member, and if not, is added.
Both the group creation and member addition code will return messages indicating success, failure, or if the group or member already exists.
function Create-ADGroupsAndMembers{
param(
[Parameter(Mandatory = $true)] [string]$CSVPath,
[Parameter(Mandatory = $true)] [string]$OrgUnit,
[Parameter(Mandatory = $true)] [string]$Domain
)
if (($Domain -ne "DEV") -and ($Domain -ne "QA") -and ($Domain -ne "PROD"))
{
throw New-Object System.ArgumentException ("Domain parameter must be specified as 'DEV', 'QA' or 'PROD'.")
}
#$DCServer - e.g. - "Patriots.net"
if ($Domain -eq "DEV")
{
$DCServer = ""
}
elseif ($Domain -eq "QA")
{
$DCServer = ""
}
elseif ($Domain -eq "PROD")
{
$DCServer = ""
}
$csvData = Import-Csv $CSVPath
foreach ($line in $csvData | Where-Object { $_.sAMAccountName -ne "00" -and $_.sAMAccountName -ne "0" }) {
$accountTable = @{
'givenName' = $line.FirstName
'sn' = $line.LastName
'displayName' = $line.DisplayName
'sAMAccountName' = $line.sAMAccountName
'password' = $line.Password
'description' = $line.Description
'ou' = $OrgUnit
}
try
{
$groupFilterString = "samAccountName -like `"" + $AccountTable['sAMAccountName'] + "`""
$group = Get-ADGroup -Filter $groupFilterString -Server $server
if ($group -eq $null)
{
Write-Host "Creating group account:" $AccountTable['sAMAccountName']
New-ADGroup -SamAccountName $AccountTable['sAMAccountName'] `
-Name $AccountTable['sAMAccountName'] `
-Path $AccountTable['ou'] `
-GroupScope Universal `
-GroupCategory Security `
-Server $DCServer
}
else
{
Write-Host "Group" $AccountTable['sAMAccountName'] "not created as it already exists" -ForegroundColor yellow
}
}
catch
{
Write-Host "There was a problem creating the group" $AccountTable['sAMAccountName'] "-" $_ -ForegroundColor red
}
$groupFilterString = "samAccountName -like `"" + $line.sAMAccountName + "`""
$group = Get-ADGroup -Filter $groupFilterString -Server $server
$memberColumnNumber = 1
$memberColumn = "Member" + $memberColumnNumber
while ($line.$memberColumn)
{
$member = Get-ADGroupMember $group -Server $server | where { $_.sAMAccountName -eq $line.$memberColumn }
$member2 = Get-ADGroupMember $group -Server $server | where { $_.Name -eq $line.$memberColumn }
if ($member -eq $null -and $member2 -eq $null -and $line.$memberColumn -ne "0")
{
Write-Host "Adding" $line.$memberColumn "as a member to group" $group.Name
try
{
$userFilterString = "Name -like `"" + $line.$memberColumn + "`""
$user = Get-ADUser -SearchBase $orgUnitSearch -Filter $userFilterString -Server $server
if ($user -eq $null)
{
$user = $line.$memberColumn
}
Add-ADGroupMember -Identity $group -Members $user -Server $DCServer
}
catch
{
Write-Host "There was a problem adding" $line.$memberColumn "as a member to group" $group.Name "-" $_ -ForegroundColor red
}
}
else
{
Write-Host "User" $line.$memberColumn "not added to group" $group.Name "as it is already a member or member not specified" -ForegroundColor yellow
}
$memberColumnNumber = $memberColumnNumber + 1
$memberColumn = "Member" + $memberColumnNumber
}
}
}
Create-SQLAccountsFromCSV
This is also fairly complicated. Each row in the CSV file is parsed into variables, which are then fed into more variables that create the strings for the SQL statements. Then, depending on the type of command warranted, different functions below are called along with Invoke-Sqlcmd commands that create the permissions based on the SQL statements created.
function Create-SQLAccountsFromCSV {
param(
[Parameter(Mandatory = $true)] [string]$CSVPath
)
$csvData = Import-Csv $CSVPath
foreach ($line in $csvData) {
$AccountName = $line.Name
$SQLServerInstance = $line.SQLServerInstanceName
$Database = $line.DatabaseName
$DatabaseObject = $line.DatabaseObjectName
$DatabaseSchema = $line.DatabaseSchemaName
$Permission = $line.Permission
$Action = $line.Action
$QueryCreateLogin = "CREATE LOGIN [$env2$AccountName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]"
$QueryCreateUser = "CREATE USER [$env2$AccountName] FOR LOGIN [$env2$AccountName]"
$QueryCreateServerRolePermission = "EXEC master..sp_addsrvrolemember @loginame = N'$env2$AccountName', @rolename = N'$Permission'"
$QueryCreateDatabaseRolePermission = "EXEC sp_addrolemember N'$Permission', N'$env2$AccountName'"
$QueryRevokeServerRolePermission = "EXEC master..sp_dropsrvrolemember @loginame = N'$env2$AccountName', @rolename = N'$Permission'"
$QueryRevokeDatabaseRolePermission = "EXEC sp_droprolemember N'$Permission', N'$env2$AccountName'"
if ($AccountName -eq "0") { break }
#If a login and a server role permission is requested
if ($Database -eq "0" -and $Action -eq "Grant")
{
Create-SQLLogin
Write-Host "Creating server permission for previous login:" $Permission
Invoke-Sqlcmd -ServerInstance $SQLServerInstance -Database "master" -Query "$QueryCreateServerRolePermission" -ErrorAction Stop
trap
{
$ExceptionText = $_.Exception.Message -replace "'",""
}
}
#If a login, a user and a database role permission is requested
if ($Database -ne "0" -and $DatabaseObject -eq "0" -and $DatabaseSchema -eq "0" -and $Action -eq "Grant")
{
Create-SQLLogin
Create-SQLUser
Write-Host "Creating database permission for previous user:" $Permission
Invoke-Sqlcmd -ServerInstance $SQLServerInstance -Database $Database -Query "$QueryCreateDatabaseRolePermission" -ErrorAction Stop
trap
{
$ExceptionText = $_.Exception.Message -replace "'",""
}
}
#If a server role permission is requested to be revoked
if ($Database -eq "0" -and $Action -eq "Revoke")
{
Write-Host "Revoking server permission for login:" $AccountName
Invoke-Sqlcmd -ServerInstance $SQLServerInstance -Database "master" -Query "$QueryRevokeServerRolePermission" -ErrorAction Stop
trap
{
$ExceptionText = $_.Exception.Message -replace "'",""
}
}
#If a database role permission is requested to be revoked
if ($Database -ne "0" -and $DatabaseObject -eq "0" -and $DatabaseSchema -eq "0" -and $Action -eq "Revoke")
{
Write-Host "Revoking database permission for user:" $AccountName
Invoke-Sqlcmd -ServerInstance $SQLServerInstance -Database $Database -Query "$QueryRevokeDatabaseRolePermission" -ErrorAction Stop
trap
{
$ExceptionText = $_.Exception.Message -replace "'",""
}
}
}
}
Create-SQLLogin, Test-SQLLogin, Create-SQLUser and Test-SQLUser
These are simple Invoke-SQLCmd commands or SMO commands.
The link for the PowerShell code, start.bat file and Excel worksheet is here.