CHAPTER 8 Securing and Administering SSAS

chapter summary

• SSAS supports a comprehensive role-based security model that is layered on top of Windows security.
• Members of the SSAS Administrators role have unrestricted access to SSAS. You can create database roles to protect the cube data and control security all the way down to the individual cube cells.
• Dimension data security protects dimension members and data associated with those members. To enforce dimension data security, you define allowed sets and denied sets.
• Cell security lets you define security policy at the most granular level—that is, at the cube cell level.
• Backing up and restoring SSAS databases are essential disaster-recovery tasks. You can automate them by using the SQL Server Agent service.
• With SQL Server Profiler, you can audit server activity and troubleshoot performance problems. Windows Performance Monitor (PerfMon) lets you identify high-level performance bottlenecks. Query the SSAS schema rowsets to retrieve support and monitoring information from an SSAS instance.

Lesson 1: Setting Up SSAS Server Security

1. Which server property do you need to change to disallow local Windows administrators administrative access to the SSAS server?

To disallow local administrators the rights to become SSAS administrators, change the Security\BuiltinAdminsAreServerAdmins property for the Analysis Services instance to false.

2. Is a new role allowed cube access by default?

By default, a new role is not allowed cube access. you need to grant the role at least read permission to the cube so that its members can browse the cube.

3. What do you need to do to prevent a role from viewing calculated members that use a given measure?

Quick check answers

To prevent a role from seeing calculated members that derive from a measure, you need to set a read-contingent permission that revokes that measure.

Lesson 2: Managing SSAS High Availability, Backups, and Object Scripting

1. What should you do to protect a backup file from being accessed by unauthorized users?

To protect a backup file, select the Encrypt Backup file check box in the Backup Database dialog box, and then enter a password.

2. What SQL Server Agent step type do you need to run a backup script?

To run a backup script in a SQL Server Agent job, you need to select SQL Server Analysis Services Command as a step type.

3. How can you script the CREATE definition of a cube in SSMS?

Quick check answers

To script the CREATE cube definition in SSMS, right-click the cube, and then click the Script Cube As, Create To option.

Lesson 3: Managing SSAS Tuning and Logging

1. Which server property do you need to set to change the location of where SSAS saves data files?

you need to change the DataDir property to change where SSAS saves data files.

2. Can you enhance an existing aggregation design (custom or produced with the Aggregation Design Wizard) with an aggregation design from the usage-Based optimization Wizard?

yes, on the last page of the usage-Based optimization Wizard, select the Merge With An Existing Aggregation Design option.

3. Does the query logging feature in the SSAS server properties let you capture the MDX statements to a file for later review?

No, the QueryLog table does not store the actual MDX statements. It stores the levels for each of the attributes and hierarchies that are used by the query. You can use SQL Server Profiler to capture SSAS activity by tracing MDX.

Lesson 4: Tracing and Monitoring SSAS Instances

1. Which SSAS trace can you use to monitor the server activity for the past hour?

To monitor the server activity for the past hour, you can use the flight recorder trace.

2. Which tool would you use to determine how long a given query takes to run?

you can use SQL Server Profiler to determine how a long a query takes to run. The Duration column will give you the execution time.

3. Which schema rowset can you use to find the currently opened connections to the server?

you can use the DISCovER_ConnECTIonS schema rowset to discover information about current

1. How can you set up dimension data security so that all cubes share the allowed set?

2. How can you find out which users have been successfully authenticated by the server?