Export (0) Print
Expand All

Department-specific data access using Windows PowerShell

SharePoint 2013

Published: May 28, 2013

Summary: Automatically grant users database access based on their Active Directory department by using Windows PowerShell, a SharePoint list, and Task Scheduler.

Applies to:  SharePoint Server 2013 

This scenario describes how to use a Windows PowerShell script running as a scheduled task to automatically process user requests for access to data sources, and assign those users the appropriate level of access based on their Active Directory department.

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 Windows PowerShell. By using Windows PowerShell, you can encapsulate your business processes into a script 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.

In this scenario, we will use Windows PowerShell to retrieve the requesting user’s Department from Active Directory. Using this information, the Windows PowerShell script will assign the user appropriate access. In the AdventureWorks example described in this article, users in the Sales department will be given full read access to any of the AdventureWorks data sources, whereas users who are not in the Sales department will be given limited OLAP cube access that excludes certain financial information.

This article provides one example of how to use Windows PowerShell to automate governance processes in an organization. You may want to modify the script and processes 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 roles in Analysis Services to provide different levels of data access to different groups of users.

  3. Create a Windows PowerShell script that runs as a scheduled task, scans the SharePoint list, and adds users to the appropriate Active Directory group, based on their Active Directory department, to give them the appropriate data access.

Before you begin

Before starting, note the following:

  • You will need a SharePoint site where you can create a SharePoint list. 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.

  • The examples in this article use an OLAP cube to provide varying levels of access to different users. We use AdventureWorks for the example, but you can use your own cube if you prefer.

  • 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 configured.

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.

Completing these steps will provide the infrastructure needed for the 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 a SharePoint list, as described in this article. (Time: 13:01.)

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 for restricted cube access, and a group called AdventureWorksOLAPSales for full cube access.

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 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.

Use the following procedure to create an Active Directory account.

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

  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.

Once the account has been created, you must add it 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.

As part of this scenario, we will be checking users Active Directory account settings to determine what department they belong to. You will need one or more users that have a department of Sales, and one or more users that have a different department (for example, Engineering).

Use the following procedure to set the department for the Active Directory user accounts that you will be using as part of this scenario.

To configure an Active Directory user’s department

  1. In Active Directory Users and Computers, under Users, double-click the user that you want to update.

  2. Click the Organization tab.

  3. In the Department text box, type the department name that you want to use for this user.

  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 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 the following roles for the AdventureWorksOLAPUsers and AdventureWorksOLAPSales Active Directory groups:

  • General Access– this role will be used to give access to the AdventureWorksOLAPUsers group. This will be for users who are not part of the Sales department.

  • Sales Access – this role will be used to give access to the AdventureWorksOLAPSales group. This will be for users who are members of the Sales department.

Use the following procedure to create an Analysis Services role for each Active Directory group. Create roles 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 or Sales 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 the roles have been created, we need to modify the General Access role to limit the data available to members of this role. In this example, we will use an MDX query to remove some fields containing financial information, preventing users who are not from the Sales department from seeing this data.

Use the following procedure to modify the General Access role.

To limit cube access using MDX

  1. In Analysis Services, under the AdventureWorksDW2012Multidimensional-EE database database, double click the General Access role.

  2. Select the Cell Data page in the left pane.

  3. Select the Enable read permissions check box.

  4. In the Allow reading of cube content box, type the following MDX statement:

    NOT Measures.CurrentMember IS Measures.[Total Product Cost] and NOT Measures.CurrentMember IS Measures.[Standard Product Cost] and NOT Measures.CurrentMember IS Measures.[Gross Profit] and NOT Measures.CurrentMember IS Measures.[Gross Profit Margin] and NOT Measures.CurrentMember IS Measures.[Expense to Revenue Ratio]
    
    noteNote:

    There is additional financial information in the AdventureWorks OLAP cube, but this MDX statement will remove enough information to be effective for our example.

  5. Click OK.

Once SQL Server and Analysis Services have been configured with the needed logins and roles, the next step is to create a SharePoint list to use for data access requests.

Create a SharePoint list

In the SharePoint list, we need 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. We also need a 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 SharePoint 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

  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 Department Data Access 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 Department Data Access 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, Succeeded, and Failed 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.

    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.

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.

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].

  5. Click Share.

Once you have created the SharePoint list and granted Edit permissions to the domain proxy account, the next step is to automate the process of granting access to the data sources by tying the list together with a Windows PowerShell script. The next section describes the process of creating the script and automating it as a scheduled task.

Automation and scripting

In this section we will:

  1. Create a Windows PowerShell script that reads the SharePoint list and applies the appropriate permissions in Active Directory based on the user’s department.

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

With the script in place and the scheduled task running, users can add their database requests to the SharePoint list and they will be automatically handled within a short time of being added to the list.

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 a Windows PowerShell script that reads the SharePoint list and applies the requested permissions based on the user’s department, and configuring a scheduled task to run the Windows PowerShell script. (Time: 11:42.)

Video: Automation and scripting

Video (play button) icon

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 SharePoint list where users request data access.

  2. Cycles through each list item looking for a status of New.

  3. For each item with a status of New, the script:

    1. Checks the Active Directory department of the user.

    2. If the user specified in the User alias field is in the Sales department, then the user is added to the Active Directory group associated with the data source specified in the Data Source filed.

    3. If the user specified in the User alias field is not in the Sales department and they have requested cube access, then the user is added to the AdventureWorksOLAPUsers Active Directory group, giving them limited cube access.

    4. If the user specified in the User alias field is not in the Sales department and they have requested access to the AdventureWorks database or data warehouse, then the request is denied and a string is added to the log noting that access was denied because they are not in the Sales department.

  4. The status of the list item is changed from New to Succeeded or Failed depending on the outcome.

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

  6. An email is sent to the user specified in the User alias field giving the final status of the request.

In this scenario, we deny users access to either of the relational databases if they are not in the Sales department because sensitive financial information exists in these databases and there is no easy way for us to filter that out. In the OLAP cube, however, we have a role set up that excludes the sensitive information, so we can allow non-Sales users access to the cube in a limited fashion.

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
     }
    
    # Create a SMTP client object for sending final status email
    $MailServer = new-object System.Net.Mail.SMTPClient("Contoso-Exch")
    
    # 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["Department Data Access"]
    
    # Get the list items
    $ListItems = $ListName.items
    
    # Loop through each list item
    $ListItems | foreach {
    
    $ListItem = $_
    
    # Look for list items with a status of "New"
    if($ListItem["Status"] -eq "New") {
    
        # Clear out the variables for each loop
        $LogText = ""
        $ResultsText = ""
        $ActionStatus = ""
        $ActionTime = ""
        $DataSourceADGroup = ""
    
        # 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\","")
        
        # Set user's email for status email
        $SharePointUserEmail = $SharePointUser + "@contoso.local"
        
        # Get the user's department from Active Directory
        $department = Get-ADUser -Identity $SharePointUser -Properties Department
        $department = $department.department
        
        # Add user request and data source information to the log variable
        $LogText += "User " + $ListItem["User alias"] + " (" + $department + ") requesting access to " + $ListItem["Data Source"] + "`n`r"
    
        
        # If the user's department is Sales, then they're allowed full read access to the data source they chose
        if($department -eq "Sales")
        {
            # 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 = "AdventureWorksOLAPSales"}
        
            # 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"
    
                # Note success for the Request results field
                $ResultsText = "Access to " + $ListItem["Data Source"] + " has been granted."
                }
            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"
                
                # Note in the Results text that there was an error
                $ResultsText = "There was an error in processing your request for access to " + $ListItem["Data Source"] + ". Please contact your administrator"
                }
        }
        else
        # If the user's department is not Sales, then they're only allowed limited read access to the cube
        {
    
            # If the user is requesting cube access, then attempt to grant that access
            if($ListItem["Data Source"] -eq "AdventureWorks OLAP")
                {
                $DataSourceADGroup = "AdventureWorksOLAPUsers"
                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"
    
                    # Note success in the Results text
                    $ResultsText = "Your request for access to " + $ListItem["Data Source"] + " was completed successfully."
                    }
                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"
    
                    # Note in the Results text that there was an error
                    $ResultsText = "There was an error in processing your request for access to " + $ListItem["Data Source"] + ". Please contact your administrator"
                    }
                }
    
            # If a non-sales user is requesting database access, then access is denied
            else
                {
    
                # Note in the log that user was denied access to the database because they're not in Sales
                $LogText += "Error: could not add " + $ListItem["User alias"] + " to AdventureWorksDBUsers: User is not in the Sales department `n`r"
                
                # Uptdate the Results text for the list item and email
                $ResultsText = "Access denied. Access to " + $ListItem["Data Source"] + " is not available to users in " + $department + "."
                
                # 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
        $ListItem["Request results"] = $ResultsText
        $ListItem["Status"] = $ActionStatus
        $ListItem["Log"] = $LogText
        $ListItem.Update()
    
        # Send mail to the user for whom access was requested
        $EmailMessage = new-object System.Net.Mail.MailMessage
     
        $EmailMessage.From = "DatabaseRequests@contoso.local"
        $EmailMessage.ReplyTo = "DatabaseRequests@contoso.local"
        $EmailMessage.To.Add($SharePointUserEmail)
        $EmailMessage.subject = "Access request for " + $ListItem["Data Source"]
        $EmailMessage.body = "Your reqest for data access had been processed: `n`r"
        $EmailMessage.body += $ResultsText
     
        $MailServer.Send($EmailMessage)
        
        }
    }
    
    # Clean up the mail server object
    $MailServer.Dispose()
    
    
  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.

  • The $MailServer value must be updated with the name of your mail server.

  • 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\DepartmentDataAccess.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: 10:20.)

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 list. 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 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). In Task Scheduler, right-click the task, and then click Run (to avoid having to wait for the next scheduled time). Check the SharePoint list and Active Directory groups to ensure you’re getting the expected results:

    • Users that are members of the Sales department should have their requests completed successfully.

    • Users that are not members of the Sales department should be denied access to the databases, but granted access to the cube via the AdventureWorksOLAPUsers group.

  • Create two identical list items (two requests for the same user and data source). Run the scheduled task. This should produce an error in the second request since the user was already added to the appropriate Active Directory group in the first request. Note that the log entry for the second item specifies this error, which was returned by the Add-ADGroupMember cmdlet.

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

You can confirm that users have varying levels of access to the OLAP cube by having a member of the Sales department create a report in Excel using financial data such as profit margin and then having a user from outside the Sales department attempt to create the same report.

Use the following procedure to create an Excel report using the AdventureWorks OLAP cube. Use a user account that is a member of the Sales department.

To create an Excel report

  1. In Excel, on the Data tab, click From Other Sources, and then click From Analysis Services.

  2. In the Server name box, type the name of the server running Analysis Services, and then click Next.

  3. On the Select Database and Table page, select AdventureWorks, and then click Next.

  4. Choose a name for the connection file, and then click Finish.

  5. On the Import Data dialog box, choose the PivotTable Report option, and then click OK.

  6. In the PivotTable Fields list, select the following check boxes:

    • Sales Territory

    • Sales Amount

    • Gross Profit

A user who is a member of the Sales department should be able to see all of the fields. A user who is not a member of the Sales department should not be able to see the Gross Profit field.

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