Solved

Moving SSAS Tabular cube to new server, permissions grant question

Posted on 2016-07-19
1
46 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

20 Experts available now in Live!

Get 1:1 Help Now