Microsoft SQL Server 2008 Analysis Services Unleashed Review Chapter 35

Years ago when I started to learn programming I would encounter one of two security models:

• Complete admininstrative permissions (as on the Radio Shack TRS-80 where I first learned BASIC)
• Full administrative permissions for what I saw (as on several mini and mainframe computer systems)

Windows has a peculiar challenge, since even if someone presents users an interface where they have full administrative privileges on what they see (including network resources), people have a variety of experience with Windows since it is also a consumer product.  Specifically, Windows Server 2008 R2 and Windows 7 share a lot of similarities.  I personally see differences between being a full administrator on my own machines, and alternatively having to log on to corporate systems where my account may have some or extensive restrictions.  The task challenges especially developers, since what is possible may not, in a particular case, be possible.

I will list the five Analysis Services security areas listed by the book:

• Connection security – a set of security settings and mechanisms that validates users who establish connections to the Analysis Services host server (page 713)
• Administrative security – permissions grated to the Analysis Services administrator and users (page 713)
• Data security – granular data access permissions (page 713)
• External data access security -permissions to access file systems or other Analysis Services instances (page 714)
• Code access security – barrier to prevent external code from performing certain operations (page 714)

 The MSDN website has complete documentation on this topic:  Security and Protection (Analysis Services – Multidimensional Data)
A second MSDN reference under Logical Architecture is User Access Security Architecture

I notice that the MSDN information, while free, only provides descriptive information about security.  The book provides both descriptive and sometimes prescriptive information.  For example, the authors recommend to use a user domain account for a service logon account (page 718).  The LocalSystem account has privileges which are too high, and a local domain account would not necessarily have authenticatable access to external data sources.  I have used this recommended advice on my personal systems.  Yes, I do apply security to my own systems because I periodically will introduce code or data from someone else and I want to both protect what I bring over as well as protect my current system.

The chapter also talks about establishing security roles in SQL Server Management Studio.   I like the idea of roles, and as the authors recommend, name these roles with some specificity (page 724, since a system may come to have a long list of possible roles).  Most major objects and some minor objects in the Analysis Services database allow for security permissions (page 724). 

Commonly some ask how to restrict authorized users from seeing one part of an Analysis Services database but not another.  As a conservative rule (and without my proof), do not give anyone access to an Analysis Services database unless you want them to have read access to the whole thing.  For security reasons, it may be more logical to make a second or multiple instances of portions of an Analysis Services database, and give some users access to those derivative instances.   This recommendation makes more sense at a higher level maintenance issue, and helps clarify future security issues especially in production systems where an Analysis Services database may have many hundreds or thousands of users.

Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.
ISBN: 0-672-33001-6