Solved

Moving SSAS Tabular cube to new server, permissions grant question

Posted on 2016-07-19
1
84 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

695 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