In this post i will describe the basics of the security of a cube. For this purpose i'll be using the ResearchAverage cube i've worked before and is described in the following posts:
- SSAS : Selecting a dynamic period in a MDX query
- SSAS/SSRS : Calulating sums and averages with M2M dimensions
Security in SSAS is controlled by roles which allows you to group windows users according to their needs. (windows) Users are members in roles and that determines whether a user can access a cube (and how).
1) First set up a environment with the scripts i've used before (see former posts)
2) The next thing we need to do is creating a user with Computermanagement (Right click computer and manage):
3) Create a group SSASUsers and add a user to the group:
4) Create a role in Analysis Services
5) Add the user to the role
6) Select "Read" in the Access field:
7) Run as SSASuser a SQL Server Management Studio session:
8) Connect with the Analysis server with the SSAS user:
9) let's look at the databases :
10) Let's try a query to check whether we can query the database. The first thing that happens when i press new query is that Database is automatically selected:
And let's try a query:
11) let's try whether we can connect to the cube with Excel
Why can't we see the cube in SSMS? On the General tab there is a Read defintion checkbox
Can we see the cube now in SSMS?
This blogpost is about a simple security model. This way you can setup quite easily a basic security in a early stage of a project or in case of a small project.