Solved

Moving SSAS Tabular cube to new server, permissions grant question

Posted on 2016-07-19
1
54 Views
Last Modified: 2016-08-22
Hello,

I have a SSAS Tabular Cube that has been moved from one server to an new one and was wondering about the permissions needed on the new server to connect to the SQL Server that has the Database that the Cube builds its data from.

Kind regards
0
Comment
Question by:deanmachine333
1 Comment
 
LVL 6

Accepted Solution

by:
Manju earned 500 total points
ID: 41722858
By default, no one except a Server Administrator or Database Administrator has permission to query cubes in a database. Cube access by a non-administrator requires membership in a role created for the database containing the cube. Membership is supported for Windows user or group accounts, defined in either Active Directory or on the local computer. Before you start, identify which accounts will be assigned membership in the roles you are about to create.

Having Read access to a cube also conveys permissions on the dimensions, measure groups, and perspectives within it. Most administrators will grant read permissions at the cube level and then restrict permissions on specific objects, on associated data, or by user identity.

To preserve role definitions over successive solution deployments, a best practice is to define roles in SQL Server Data Tools as an integral part of the model, and then have a database administrator assign role memberships in SQL Server Management Studio after the database is published. But you can use either tool for both tasks. To simplify the exercise, we'll use SQL Server Management Studio for both role definition and membership.

Step 1: Create the role
1. In SSMS, connect to Analysis Services.
2. Open the Databases folder in Object Explorer, and select a database.
3. Right-click Roles and choose New Role. Notice that roles are created at the database level and apply to objects within it. You cannot share roles across databases.
4. In the General pane, enter a name, and optionally, a description. This pane also contains several database permissions, such as Full Control, Process Database, and Read Definition. None of these permissions are needed for querying a cube or tabular model. See Grant database permissions (Analysis Services) for more information about these permissions.
5. Continue to the next step after entering a name and optional description.

Step 2: Assign Membership
1. In the Membership pane, click Add to enter the Windows user or group accounts that will be accessing the cube using this role. Analysis Services only supports Windows security identities. Notice that you are not creating database logins in this step. In Analysis Services, users connect through Windows accounts.
2. Continue to the next step, setting cube permissions.

Step 3: Set Cube Permissions
1. In the Cubes pane, select a cube, and then click Read or Read/Write access.
Read access is sufficient for most operations. Read/Write is used only for writeback, not processing. See Set Partition Writeback for more information about this capability.
Notice that you can select multiple cubes, as well as other objects available in the Create Role dialog box. Granting permissions to a cube authorizes access to the dimensions and perspectives associated with the cube. It's not necessary to manually add objects already represented in the cube.

2. At this point, after you click OK, all members of this role have access to the cubes, at the permission levels you specified.
Notice that on the Cubes pane, you can grant users permission to create local cubes from a server cube via Drillthrough and Local Cube, or allow drillthrough only, via the Drillthrough permission.
Finally, this pane lets you grant Process Database rights on the cube to give all members of this role the ability to process data for this cube. Because processing is typically a restricted operation, we recommend that you leave that task to the administrators, or define separate roles specifically for that task.
Step 4: Test
Use Excel to test cube access permissions. You can also use SQL Server Management Studio, following the same technique described next ─ running the application as a non-administrator user.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now