woensdag 7 september 2011

SSAS : Set up simple security in Analysis services

Introduction
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:
    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).

    Setup
    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 :


    No 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:


    It seems that we can query the database without viewing the definition of the database.

    11) let's try whether we can connect to the cube with Excel

    12) 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?


    Conclusion
    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.

    Gr,
    Hennie

    Geen opmerkingen:

    Een reactie plaatsen