Automate Access to Folders With Service Manager and Orchestrator

Following on from previous posts on 1) Automating Permissions Reporting With Orchestrator and 2) Permissions Logging With Powershell this post discusses how to use the previously created database that contains the network folder to security group mappings to automatically add a user to the correct AD group when they have requested access to a network folder. This requires the requesting user to have no knowledge of the actual group, just the folder path.

Remember this is what the database containing the network folder to group mappings looks like:

The offering on the Service Manager portal is a Custom Form/Class I designed and called NTFSAccessRequest. It is published as a service request on the SCSM Portal.

The runbook that the NTFSAccessRequest invokes as part of its template looks like this:

3 variables start the runbook and these are mapped from the Request Offering in Service Manager (after some tinkering I went with the samAccountName rather than UPN but UPN will work if the users enter it correctly):

The powershell script publishes a variable called group which it retrieves from a connection to the database created in previous post Automating Permissions Reporting With Orchestrator

The powershell is as follows:

$group = powershell {
############################################################
#query database for the group the user should be added to based on the folder path

function getgroup ($folderpath, $level) {

$server = "<em>sqlserver\instance</em>"
$database = "<em>database</em>"
$query = "select * from permtable where Folder like '$folderpath' AND Level like '$level'"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server = $server; Database = $database; Integrated Security = True"
 
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $query
$cmd.Connection = $connection
 
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter
$adapter.SelectCommand = $cmd

$permdataset = New-Object System.Data.Dataset
$adapter.Fill($permdataset)

$permlist = $permdataset.Tables[0].Rows

return $permlist
}

##########################################################script body

##call the function with the variables passed in the Initialize Data step##
$group = getgroup '\\server\share' 'level'
$date = get-date
$date | out-file <em>\\server\permissions\log.txt</em> -append
$group.groupname | out-file <em>\\server\permissions\log.txt</em> -append

return $group.groupname[0]

}

Fetch the user from AD based on the UPN/samAccountName whichever one you decide to use:

Get the group from AD based on the “group” variable returned from the powershell step:

Add the user to the group

This is the framework for the automated process. It will add the user to the correct AD group based on the folder they have requested access too. Later I added some steps to send confirmation emails and an email to the helpdesk if the group variable was empty (if the network path was not found in the database) so they could complete the process manually.

I adjusted the Level of Access field to be linked to a list in SCSM so the only values that could be entered were “Modify” and “ReadAndExecute”.

I also added a review activity into the SCSM template so approval was required see that post here: Add Group Managers as Approvers to Folder Access Requests.

The related previous posts are:

1) Automating Permissions Reporting with Orchestrator which creates an empty database that can be filled with network folders and their associated AD Security Groups. It then sends a report to the group manager listing who has access to the folders.

2) Permissions Logging With Powershell that actually populates the database with information from the network.