Export (0) Print
Expand All

Admin-approved data access using Windows PowerShell

SharePoint 2013

Published: May 28, 2013

Summary: Grant users database access by using Windows PowerShell, a SharePoint list, workflow, and Task Scheduler.

Applies to:  SharePoint Server 2013 

This scenario describes how to use workflow and a Windows PowerShell script running as a scheduled task to process user requests for access to data sources.

Important Important:

This scenario applies only to SharePoint Server 2013.

In this article:

Scenario overview

This article uses the AdventureWorks sample data set to show an example of automating database access requests using workflow and Windows PowerShell. By using workflow and PowerShell, you can encapsulate your business processes into a pre-defined sequence of events to make sure that they are consistently applied. Using Windows PowerShell also saves on administration time and allows specific Active Directory functions to be performed without the direct involvement of a domain administrator.

This article provides one example of how to use workflow and Windows PowerShell to automate governance processes in an organization. You may want to modify the workflows and the Windows PowerShell script to meet your specific needs.

You can install AdventureWorks and follow the steps in this article to create a working prototype, or you can use your own data sources instead of AdventureWorks.

This article describes how to:

  1. Create a SharePoint list where users can request access to data sources.

  2. Create a SharePoint list where administrators can approve or deny those requests.

  3. Create workflows that copy items between the two lists and emails users and administrators with status and requests.

  4. Create a Windows PowerShell script that, running as a scheduled task, scans the appropriate SharePoint list, and adds approved users to the appropriate Active Directory group to give them the requested data access.

The processes described in this scenario are illustrated in a Business Process Model and Notation (BPMN) diagram that is available for download from the Microsoft Download Center. We recommend that you download this diagram for reference while following the steps in this article. Download the BPMN diagram.

Before you begin

Before starting, note the following:

  • You will need a SharePoint site where you can create SharePoint lists. The procedures in this article assume that you are using a site with the BI Center template, though a Team site will work also. If you want to configure a site with the BI Center template, see Configure a Business Intelligence Center in SharePoint Server 2013.

  • The examples in this article use the AdventureWorks sample data set. You can use AdventureWorks or your own data sources. If you want to install AdventureWorks, see Configure AdventureWorks for Business Intelligence solutions.

  • You will need to create Active Directory accounts and groups as part of the procedures in this article. One of the accounts will need to be added to the Domain Admins group. We recommend using a test environment where you have direct access to a domain controller. Alternatively, you can work with your domain administrator to get the needed accounts and groups configured.

  • You will need a computer running SharePoint Designer 2013 in order to create the workflows.

  • The workflows in this example send emails to the users requesting data access and to the administrators approving or denying those requests. You must have Outgoing E-Mail configured in Central Administration in order for the email functionality to work.

note Note:

If you have followed the steps in related articles in this series, some of the users and groups that you are asked to create may already exist in your environment. If that is the case, you can use the existing users and groups. There is no need to recreate existing users and groups.

Initial setup

In this section, we will:

  1. Configure the needed Active Directory accounts and groups.

  2. Configure data access for the Active Directory groups in SQL Server and Analysis Services.

  3. Create a SharePoint list for collecting data access requests and one for administrators to use in approving or denying those requests.

Completing these steps will provide the infrastructure needed for the workflow, Windows PowerShell automation, and task scheduling described in the following section.

Video demonstration

This video shows the steps involved in setting up accounts and groups and creating the SharePoint lists, as described in this article. (Time: 17:30.)

Video: Initial setup

Video (play button) icon

Configure Active Directory groups and accounts

Create an Active Directory group for each data source to which you want to grant access. For AdventureWorks, we’ll create Active Directory groups for the following data sources:

  • AdventureWorks database – we’ll create a group called AdventureWorksDBUsers

  • AdventureWorks data warehouse – we’ll create a group called AdventureWorksDWUsers

  • AdventureWorks OLAP cube– we’ll create a group called AdventureWorksOLAPUsers

Use the following procedure to create an Active Directory group. Follow the procedure for each of the AdventureWorks groups listed above or each of your own data sources.

To create an Active Directory group

  1. Log on to a domain controller as a domain administrator.

  2. Click Start, click Administrative Tools, and then click Active Directory Users and Computers.

  3. Expand the domain node.

  4. Right-click Users, click New, and then click Group.

  5. On the New Object – Group dialog box, type a name for the group in the Group name text box, and then click OK.

For this scenario, we also need two domain accounts:

  • A domain administrator account - This account will be used to run a scheduled job that will add users to the Active Directory groups that we created above. You can use an existing domain administrator account, but we recommend that you create a new one specifically for this purpose. We’ll refer to this account as the domain proxy account.

  • A workflow proxy account – This account will be used to create the workflows and to run them when users and administrators make updates to the SharePoint lists. This account does not require any specific domain privileges, but does require Edit permissions to the SharePoint site where the lists are located.

Use the following procedure to create each of the Active Directory accounts listed above.

To create an Active Directory account

  1. In Active Directory Users and Computers, right-click Users, click New, and then click User.

  2. In the Full name text box, type a name for the user account (for example, ADProxyAccount or WFProxyAccount).

  3. In the User logon name text box, type a logon name.

  4. Click Next.

  5. Type and confirm a password for the account.

  6. Choose the password options appropriate for your organization, and then click Next.

  7. Click Finish.

You must add the domain proxy account to the Domain Admins group in Active Directory. Use the following procedure to add the account to the Domain Admins group.

To add an account to the Domain Admins group

  1. In Active Directory Users and Computers, under Users, double-click the Domain Admins group.

  2. On the Members tab, click Add.

  3. Type the name of the account that you just created, and then click OK.

  4. Click OK.

Once you have completed the Active Directory configuration steps, the next step is to grant the appropriate database access to the new Active Directory groups.

Configure SQL Server access

Each data source to which you want to grant user access will need a login in SQL Server or a role in Analysis Services for the corresponding Active Directory group. For AdventureWorks, the databases require the following logins:

  • AdventureWorks database – A login for the AdventureWorksDBUsers Active Directory group

  • AdventureWorks data warehouse – A login for the AdventureWorksDWUsers Active Directory group

Use the following procedure to create a SQL Server login. Follow the procedure for each of the AdventureWorks databases or each of your own databases.

To create a SQL Server login

  1. Log in to the computer running SQL Server as a SQL Server administrator.

  2. Click Start, click All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio.

  3. On the Connect to Server dialog box, choose Database Engine from the Server type dropdown list, and then click Connect.

  4. Expand the Security node.

  5. Right-click Logins, and then click New Login.

  6. Click Search.

  7. On the Select User or Group dialog box, click Object Types.

  8. On the Object Types dialog box, select the Groups check box, and then click OK.

  9. On the Select User of Group dialog box, type the name of the Active Directory group for which you want to create the login (for example Contoso\AdvenureWorksDBUsers), and then click OK.

  10. In the Select a page pane, click User Mapping.

  11. Select the check box for the appropriate database (for example, AdventureWorks2012), and then under Database role membership, select the db_datareader check box.

  12. Click OK.

You must also create an Analysis Services role for access to any Analysis Services cubes. For AdventureWorks, the cube requires a role for the AdventureWorksOLAPUsers Active Directory group.

Use the following procedure to create an Analysis Services role. Create a role for the AdventureWorks OLAP cube or for your own Analysis Services cube.

To create an Analysis Services role

  1. Log in to the computer running SQL Server as a SQL Server administrator.

  2. Click Start, click All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio.

  3. On the Connect to Server dialog box, choose Analysis Services from the Server type dropdown list, and then click Connect.

  4. Expand Databases.

  5. Expand the AdventureWorksDW2012Multidimensional-EE database.

  6. Right-click Roles, and then click New Role.

  7. On the General page, type a name (for example, General Access) in the Role name text box.

  8. Select the Membership page, and then:

    1. Click Add.

    2. On the Select User or Group dialog box, click Object Types.

    3. On the Object Types dialog box, select the Groups check box, and then click OK.

    4. On the Select User of Group dialog box, type the name of the Active Directory group for which you want to create the login (for example Contoso\AdvenureWorksOLAPUsers), and then click OK.

  9. Select the Cube page, and then choose Read from the Access dropdown list for both cubes.

  10. Click OK.

Once SQL Server and Analysis Services have been configure with the needed logins and roles, the next step is to create the SharePoint lists needed to handle the data access requests.

Create and configure SharePoint lists

In this scenario, we need two SharePoint lists:

  • Request list – This list is used by users requiring data access to enter their requests. This list will contain fields that can be used to define a database access request. This includes the alias of the user requesting access, the data source that they wish to access, and an area for them to specify the business justification for the request.

  • Approval list – This list is used by administrators to approve or deny data access requests. It contains all of the fields present in the Request list, plus an additional field that the Windows PowerShell script can use to track the status of the request, as well as a field where logging information can be stored.

Use the following procedure to create the Request list. You can add additional fields if you need them, but be sure not to omit any fields or the Windows PowerShell script will not work.

To create a SharePoint list for access requests

  1. Go to the site where you want to create the list.

  2. In the left navigation, click Site Contents.

  3. Click add an app.

  4. Click Custom List.

  5. In the Adding Custom List dialog box, type Access Requests in the Name text box, and then click Create.

    note Note:

    You can choose a different name, but you will need to update the list name in the Windows PowerShell script provided later in this article.

  6. On the Site Contents page, click the Access Requests list.

  7. On the list page, on the List tab in the ribbon, click List Settings.

  8. On the list settings page, under Columns, use the Create column link to create columns as described in the following table.

    Field name Description Options

    Status

    The status of the request.

    Choose the Choice (menu to choose from) option and type New, In Progress, Approved, and Rejected for the available choices in the Type each choice on a separate line box. Specify a Default value of New.

    Data Source

    A list of the available data sources.

    Choose the Choice (menu to choose from) option and type AdventureWorks DB, AdventureWorks DW, and AdventureWorks OLAP for the available choices in the Type each choice on a separate line box. (If you are using your own data sources, type those here instead of the AdventureWorks options.)

    User alias

    The alias of the user for whom data access is being requested.

    Use the Single line of text option, and make sure this is a required field by selecting Yes under Require that this column contains information.

    Business justification

    This is an optional field in case you want to keep a record of why a user is requesting data access.

    Use the Multiple lines of text option for this field.

    Comments

    A place for the requestor to type comments for the administrator.

    Use the Multiple lines of text option for this field.

    Request ID

    A workflow-generated value used to maintain a link between the two lists.

    Use the Single line of text option for this field.

In order for our Windows PowerShell script to work, the domain proxy account requires read/write access to this list. This is most easily accomplished by granting the account Edit access to the site where the list is located.

Likewise, the workflow proxy account must have Design permissions to the site in order to publish workflows.

Follow the procedure for each account, choosing Edit access for the domain proxy account and Design access for the workflow proxy account.

To grant access to a site

  1. On the site where the list is located, click Share.

  2. Type the user account for the domain proxy account (for example, contoso\ADProxyAccount).

  3. Click Show Options.

  4. From the Select a group or permission level dropdown list, choose [Edit] or [Design].

  5. Click Share.

Once you have created the SharePoint list and granted Edit permissions to the domain proxy account, the next step is to create the Approval list for administrators to use in approving or denying database access.

Use the following procedure to create the Approval list. You can add additional fields if you need them, but be sure not to omit any fields or the Windows PowerShell script will not work.

To create a SharePoint list for administrators

  1. Go to the site where you want to create the list.

  2. In the left navigation, click Site Contents.

  3. Click add an app.

  4. Click Custom List.

  5. In the Adding Custom List dialog box, type Access Approval in the Name text box, and then click Create.

    note Note:

    You can choose a different name, but you will need to update the list name in the Windows PowerShell script provided later in this article.

  6. On the Site Contents page, click the Access Approval list.

  7. On the list page, on the List tab in the ribbon, click List Settings.

  8. On the list settings page, under Columns, use the Create column link to create columns as described in the following table.

    Field name Description Options

    Status

    The status of the request.

    Choose the Choice (menu to choose from) option and type New, Approved, Rejected, Approved - Pending, and Error for the available choices in the Type each choice on a separate line box. Leave the Default value field blank.

    Data Source

    A list of the available data sources.

    Choose the Choice (menu to choose from) option and type AdventureWorks DB, AdventureWorks DW, and AdventureWorks OLAP for the available choices in the Type each choice on a separate line box. (If you are using your own data sources, type those here instead of the AdventureWorks options.)

    User alias

    The alias of the user for whom data access is being requested.

    Use the Single line of text option, and make sure this is a required field by selecting Yes under Require that this column contains information.

    Business justification

    This is an optional field in case you want to keep a record of why a user is requesting data access.

    Use the Multiple lines of text option for this field.

    Comments

    A place for the administrator to type comments for the requestor.

    Use the Multiple lines of text option for this field.

    Request ID

    A workflow-generated value used to maintain a link between the two lists.

    Use the Single line of text option for this field.

    Log

    A log generated by the Windows PowerShell script describing the actions undertaken by the script.

    Use the Multiple lines of text option for this field.

Since this list is specifically for administrators to use, we must modify the list permissions to prevent the list from inheriting permissions from the parent site, and then grant administrators direct access to the list. We must also grant the workflow proxy account and domain proxy account read/write access to the list so that our workflows and Windows PowerShell script will work properly.

Use the following procedure to modify the list permissions.

To modify list permissions

  1. Go to the Approval list

  2. In the ribbon, on the List tab, click List Settings.

  3. Click Permissions for this list.

  4. In the ribbon, on the Permissions tab, click Stop Inheriting Permissions.

  5. Click OK on the confirmation dialog box.

  6. In the ribbon, on the Permissions tab, click Grant Permissions.

  7. Type the name of the site owner or administrator (for example, the account that you are using to modify the list).

  8. Click Show Options.

  9. From the Select a group or permission level dropdown list, choose Full Control.

  10. Click Share.

  11. Select all of the existing permissions groups (except the user or group that you just added), and then click Remove User Permissions in the ribbon.

    Important Important:

    If you keep any of the default groups, the list may continue to inherit permissions from the parent site through these groups.

  12. In the ribbon, on the Permissions tab, click Grant Permissions.

  13. Type the names of the domain proxy account and any administrators who will be using the list.

  14. Click Show Options.

  15. From the Select a group or permission level dropdown list, choose Contribute.

  16. Click Share.

  17. In the ribbon, on the Permissions tab, click Grant Permissions.

  18. Type the name of the workflow proxy account.

  19. Click Show Options.

  20. From the Select a group or permission level dropdown list, choose Design.

    note Note:

    Because the workflow proxy account will be used to publish workflows from SharePoint Designer, it must have at least Design permissions for the list.

  21. Click Share.

Once you have created the SharePoint lists and granted the necessary permissions to the domain and workflow proxy accounts, the next step is to tie the lists together with workflows and automate the process of granting access to the data sources by using a Windows PowerShell script. The next section describes the process of creating the workflows and script and automating the script as a scheduled task.

Automation and scripting

In this section we will:

  1. Create a Windows PowerShell script that reads the Access Approval list and applies the requested permissions in Active Directory.

  2. Create two workflows to copy list items between the two SharePoint lists.

  3. Create a scheduled task in Windows Server that runs the script at a specified interval.

With the script and workflows in place and the scheduled task running, users can add their database requests to the Access Requests list and they will be automatically copied to the Access Approval list where the administrator can approve or deny them. The script will then apply user permissions for approved users.

note Note:

The script performs functions in Active Directory. In order for the Active Directory Windows PowerShell module to load, the Remote Server Administration Tools must be configured on the server where you are going to run the script. Remote Server Administration Tools is a feature that you can add through Server Manager.

Video demonstration

This video shows the steps involved in configuring the workflows, the Windows PowerShell script, and configuring a scheduled task to run the Windows PowerShell script. (Time: 28:02.)

Video: Automation and scripting

Video (play button) icon

Create site workflows

For this scenario, we need to create two workflows:

  • Copy Request – This workflow copies the original data access request from the Access Requests list to the Access Approval list. It also creates a unique ID to tie the two entries together, and sends an email to the administrator notifying them that there is an outstanding request in the Access Approval list that requires their attention.

  • Update Request – This workflow updates the request status for both lists and sends an email to the user for whom data access was requested informing them if their request was approved or denied.

To create each workflow, you must log into a computer that has SharePoint Designer 2013 using the workflow proxy account. It is important to create the workflows using this account so that you can configure the workflows to run under this account.

When completed, the Copy Request workflow should look like the following screenshot.

Screenshot of workflow in SharePoint Designer 2013

Use the following procedure to create the Copy Request workflow.

To create the Copy Request workflow

  1. Log in to a computer with SharePoint Designer 2013 installed using the workflow proxy account.

  2. Click Start, click All Programs, click Microsoft Office 2013, and then click SharePoint Designer 2013.

  3. Click Open Site, type the URL of the site where your SharePoint lists are located, and then click Open.

  4. In the left pane, select Workflows.

  5. In the ribbon, click List Workflow, and then choose the Access Requests list.

  6. On the Create List Workflow dialog box, type Copy Request for the name, choose SharePoint 2010 Workflow for the Platform Type, and then click OK.

  7. Click in the white space above the Step 1 box.

  8. On the ribbon, click Impersonation Step.

  9. Select the Step 1 box, and then in the ribbon, click Delete.

  10. In the Impersonation Step box, click below the line of text.

  11. Add workflow conditions and actions as follows:

    Condition or Action Values Notes

    Condition = If current item field equals value

    Field = Status

    Value = New

    Causes the workflow to run when the status of the saved list item is New.

    Action = Do Calculation

    First value: Data source = Current Item, Field from source = ID.

    Second value: Data source = Current Item, Field = Created.

    Output to new string variable called ReqID

    This action creates a unique identifier that can be used in both lists to link the two related records together.

    Action = Set Field in Current Item

    Field = Request ID

    Value: Data source = Workflow variables and Parameters, Field from source = Variable: ReqID

    This action sets the Request ID field in the current item to the unique ID that we created in the previous step.

    Action = Copy List Item

    First value: Current Item, second value: Access Approval

    This action copies the list item from the Access Requests list to the Access Approval list.

    Action = Update List Item

    List: Current Item

    Add a Value Assignment: Set Status to In Progress

    This action sets the status for the current list item to In Progress.

    Action = Send an Email

    To an administrator with access to the Access Approval list

    This action sends an email to an administrator with access to the Access Approval list. Include text telling the administrator that an access request awaits their action and include a link to the Access Approval list.

  12. Click Copy Request in the breadcrumb on the Copy Request tab.

  13. On the workflow settings page, under Start Options, select the Start workflow automatically when an item is created and Start workflow automatically when an item is changed check boxes.

  14. In the ribbon, click Save, and then click Publish.

  15. Click OK on the impersonation warning dialog box.

Once the Copy Request workflow has been saved and published, the next step is to create the Update Request workflow. When completed, the Update Request workflow should look like the following screenshot.

Screenshot of workflow in SharePoint Designer 2013

Use the following procedure to create the Update Request workflow.

To create the Update Request workflow

  1. In SharePoint Designer 2013, in the left pane, select Workflows.

  2. In the ribbon, click List Workflow, and then choose the Access Approval list.

  3. On the Create List Workflow dialog box, type Update Request for the name, choose SharePoint 2010 Workflow for the Platform Type, and then click OK.

  4. Click in the white space above the Step 1 box.

  5. On the ribbon, click Impersonation Step.

  6. Select the Step 1 box, and then in the ribbon, click Delete.

  7. In the Impersonation Step box, click below the line of text

  8. Add workflow conditions and actions as follows:

    Condition or Action Values Notes

    Condition = If current item field equals value

    Field = Status

    Value = Approved

    Causes this section of the workflow to run when the status of the saved list item is Approved.

    Action = Update List Item

    Add: List = Access Requests, Field = Status, Value = Approved

    Find the List Item: Field = Request ID, Value = lookup of Data Source = Current Item, Field from source = Request ID

    This action updates the Status field in the Access Requests list to Approved.

    We match the Request ID from the Access Approval list to the same field in the Access Request list in order to find the correct item.

    Note that an alert will appear at the end of this step warning that the lookup may not return a single value. In this case, we are using an unique identifier, so only a single value will be returned.

    Action = Set Field in Current Item

    Set Status = Approved - Pending

    This action sets the list item in the Access Approval item to Approved – Pending, which is the value that the Windows PowerShell script looks for.

    Action = Send an Email

    To: Workflow Lookup for a User

    Data source = Current Item, Field from source = User alias

    This action sends an email to the user for whom data access was requested.

    When specifying a user to send the mail to, choose the Workflow Lookup for a User option and use the User alias field from the SharePoint list. Note in the body of the email that the user’s access has been approved.

    Condition = If current item field equals value

    Field = Status

    Value = Rejected

    Causes this section of the workflow to run when the status of the saved list item is Approved.

    Be sure to put this condition at the same level as the Item = Approved fork so that this fork appear as an Else if statement.

    Action = Update List Item

    Add: List = Access Requests, Field = Status, Value = Rejected

    Find the List Item: Field = Request ID, Value = lookup of Data Source = Current Item, Field from source = Request ID

    This action updates the Status field in the Access Requests list to Rejected.

    We match the Request ID from the Access Approval list to the same field in the Access Request list in order to find the correct item.

    Note that an alert will appear at the end of this step warning that the lookup may not return a single value. In this case, we are using an unique identifier, so only a single value will be returned.

    Action = Send an Email

    To: Workflow Lookup for a User

    Data source = Current Item, Field from source = User alias

    This action sends an email to the user for whom data access was requested.

    When specifying a user to send the mail to, choose the Workflow Lookup for a User option and use the User alias field from the SharePoint list. Note in the body of the email that the user’s access has been approved.

  9. Click Update Request in the breadcrumb on the Update Request tab.

  10. On the workflow settings page, under Start Options, select the Start workflow automatically when an item is created and Start workflow automatically when an item is changed check boxes.

  11. In the ribbon, click Save, and then click Publish.

  12. Click OK on the impersonation warning dialog box.

Create a Windows PowerShell script

To automate the process of granting access to the requested data source, we’ll use a Windows PowerShell script. The sample script below performs the following basic functions:

  1. Loads the Access Approval SharePoint list.

  2. Cycles through each list item looking for a status of Approved – Pending (the Status set by the Update Request workflow when an administrator approves a request).

  3. For each item with a status of Approved – Pending, the script adds the user specified in the User alias field to the Active Directory group associated with the data source specified in the Data Source field.

  4. The status of the list item is changed from Approved – Pending to Approved or Error depending on the outcome.

  5. A brief log of events is written to the Log field in the list item.

Use the following procedure to create the Windows PowerShell script. Create the script on your SharePoint Server 2013 application server.

To create a Windows PowerShell script

  1. On the SharePoint Server application server, click Start, click Run, type PowerShell_ISE.exe, and then click OK.

  2. Copy and paste the following script into the Windows PowerShell editor:

    # Add SharePoint and Active Directory cmdlets
    if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
         Add-PSSnapin "Microsoft.SharePoint.PowerShell"
     } 
     if ((Get-Module ActiveDirectory -ErrorAction SilentlyContinue) -eq $null) {
         Import-module ActiveDirectory
     }
    
    # Get-SPWeb for the site where the list is located
    $ListWeb = Get-SPWeb http://contoso-appsrv1/sites/BICenter
    
    # Get the list by name
    $ListName = $ListWeb.Lists["Access Approval"]
    
    # Get the list items
    $ListItems = $ListName.items
    
    # Loop through each list item
    $ListItems | foreach {
    
    $ListItem = $_
    
    # Look for list items with a status of "Approved - Pending"
    if($ListItem["Status"] -eq "Approved - Pending") {
    
        # Clear out the variables for each loop
        $LogText = ""
        $DataSourceADGroup = ""
        $ActionStatus = ""
        $ActionTime = ""
        
        # Add-ADGroupMember requires alias with no domain, so remove the domain\ if it exists
        $SharePointUser = $ListItem["User alias"]
        $SharePointUser = $SharePointUser.ToLower()
        $SharePointUser = $SharePointUser.Replace("contoso\","")
        
        # Determine data source being requested and assign the associated AD group to the $DataSourceADGroup variable
        if($ListItem["Data Source"] -eq "AdventureWorks DB") {$DataSourceADGroup = "AdventureWorksDBUsers"}
        if($ListItem["Data Source"] -eq "AdventureWorks DW") {$DataSourceADGroup = "AdventureWorksDWUsers"}
        if($ListItem["Data Source"] -eq "AdventureWorks OLAP") {$DataSourceADGroup = "AdventureWorksOLAPUsers"}
    
        # Add user request and data source information to the log variable
        $LogText += "User " + $ListItem["User alias"] + " requesting access to " + $DataSourceADGroup + "`n`r"
    
        # Try to add the requested user to the AD group
        try{
            Add-ADGroupMember -Identity $DataSourceADGroup -Members $SharePointUser
    
            # Note success in the log variable
            $LogText += "Adding " + $ListItem["User alias"] + " to " + $DataSourceADGroup + "`n`r"
            
            # Note success in the $ActionStatus variable for later update to the SharePoint list
            $ActionStatus = "Succeeded"
            }
        catch{
            # If Add-ADGroupMember fails, so note in the log variable
            $LogText += "Error: could not add " + $ListItem["User alias"] + " to " + $DataSourceADGroup + ": " + $_ + "`n`r"
            
            # Note failure in the $ActionStatus variable for later update to the SharePoint list
            $ActionStatus = "Failed"
            }
        
        # Get the current date and time
        $ActionTime = Get-Date
    
        # Append the date, time, and final status to the log variable
        $LogText += $ActionStatus + " " + $ActionTime
        
        # Update the list item with the final status and the log text
        if($ActionStatus -eq "Succeeded") {$ListItem["Status"] = "Approved"}
        if($ActionStatus -eq "Failed") {$ListItem["Status"] = "Error"}
        $ListItem["Log"] = $LogText
        $ListItem.Update()
        }
    }
    
    
  3. Click File, click Save, and save the file to a location on the local disk.

Important Important:

For test purposes, you can save the Windows PowerShell script to a convenient location. In a production environment, have your domain administrator create a folder that requires domain administrator privileges to access and save the script there. This will increase security and help prevent unauthorized changes to the script.

Depending on your environment, you may need to update the following script items:

  • The URL for the site where the SharePoint list is located is set to http://contoso-appsrv1/sites/BICenter. Update this value to the URL for your environment. This is stored in the $ListName variable.

  • On the line $SharePointUser = $SharePointUser.Replace("contoso\","") you must replace contoso with your domain.

  • The Data Source and $DataSourceADGroup values are configured for the AdventureWorks options discussed in this article. If you are using your own data sources, you must update these values to correspond to your data sources.

  • If you named any of the SharePoint list fields differently than those noted in the procedure for creating the list, you must update the script to use the values that you chose. Use the values listed in the procedure for creating the list to find the corresponding values in the script.

Once you have the script saved to your local disk, the next step is to configure the domain proxy account as a script administrator.

Configure a script administrator

In order to successfully run the Windows PowerShell script, the domain proxy account needs permissions to make updates to the SharePoint list using Windows PowerShell. This is accomplished by using the Add-SPShellAdmin Windows PowerShell cmdlet to give the account shell administrator privileges.

Use the following procedure to configure the domain proxy account as a shell administrator.

note Note:

If you previously configured the domain proxy account as a shell administrator in another scenario in this series, there is no need to redo the procedure.

To configure a script administrator

  1. Log in to your SharePoint Server application server as a Farm Administrator.

  2. Click Start, click All Programs, click Microsoft SharePoint 2013 Products, right-click SharePoint 2013 Management Shell, and then click Run as administrator.

  3. In the Windows PowerShell command window, type the following syntax:

    Get-SPContentDatabase | Add-SPShellAdmin –UserName domain\username
    

    For example:

    Get-SPContentDatabase | Add-SPShellAdmin –UserName Contoso\ADProxyAccount
    
  4. Press Enter.

Once the domain proxy account has been added as a shell administrator, it can be used to run the Windows PowerShell script as part of a scheduled task.

Create a scheduled task

By using a scheduled task to run the Windows PowerShell script on a repeating basis, we can automate the addition of the users requesting data access to the appropriate Active Directory group.

Use the following procedure to create a scheduled task.

To create a scheduled task

  1. Log into your SharePoint Server application server as a local administrator.

  2. Click Start, click Administrative Tools, and then click Task Scheduler.

  3. In the Actions pane, click Create Task.

  4. On the General tab:

    1. Type a name for the task in the Name text box.

    2. Choose the Run whether user is logged on or not option.

    3. Select the Run with highest privileges check box.

  5. On the Triggers tab:

    1. Click New.

    2. Select the Repeat task every check box, and choose 10 minutes from the dropdown list.

    3. Choose Indefinitely from the for a duration of dropdown list.

    4. Click OK.

  6. On the Actions tab:

    1. Click New.

    2. In the Program/script text box, type PowerShell.exe.

    3. In the Add arguments (optional) text box, type the path and filename of your Windows PowerShell script (for example, C:\Scripts\AdminApprovedDataAccess.PS1))

    4. Click OK.

  7. Click OK.

  8. Type the username and password of the account that you want to run the script. This is the Active Directory domain administrator account that you configured as a Windows PowerShell script administrator.

Once the scheduled task has been created, the setup steps for this scenario are complete. The next step is to test the functionality by adding items to the SharePoint list.

Test and verify functionality

In this section, we’ll add some items to the SharePoint list to test the functionality of the script and the scheduled task.

Video demonstration

This video shows some examples of testing this scenario. (Time: 9:19.)

Video: Test and verify functionality

Video (play button) icon

Verifying functionality

Try the following actions to verify that the script and scheduled task are working properly:

  • Add an item to the Access Request list with a Status of New. Wait for the Copy Request workflow to complete. Check the Access Approval list to make sure the workflow copied the item correctly. Also check the email of the administrator to whom the workflow sent mail.

  • Have the administrator approve the item in the Access Approval list. Wait for the Update Request workflow to complete. Check the task in Task Scheduler to see when the task is next scheduled to run. Wait until the task has run, then check the SharePoint list to make sure the Status and Log fields have been updated. Check the equivalent item in the Access Request list to ensure that the comments and status have been copied over. Check the appropriate Active Directory group to ensure the specified user has been added.

  • Add an item to the list for each data source that you have (the AdventureWorks database, data warehouse, and OLAP cube in our example). When the Copy Request workflow has completed for each, have the administrator approve or deny each request in the Access Approval list. When the Update Request workflow has completed for each, in Task Scheduler, right-click the task, and then click Run (to avoid having to wait for the next scheduled time). Check both SharePoint lists and the Active Directory groups to ensure you’re getting the expected results.

  • Have the users who have been granted data access try to access the data sources from Excel.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft