Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 22, 2010

Introduction to Security in Analysis Services

By William Pearson

This article introduces security in Analysis Services. Here, we will introduce security and discuss the basic concepts and considerations surrounding the implementation of security in Analysis Services.

Note: For more information about my MSSQL Server Analysis Services column in general, see the section entitled “About the MSSQL Server Analysis Services Series” that follows the conclusion of this article.


Virtually any of us that have worked with Microsoft, as well as other, applications and operating systems have encountered the type of role-based security that we find in Microsoft SQL Server Analysis Services. In this article, we will introduce a subseries that focuses upon Analysis Services security in general, the member articles of which will provide hands-on exposure to the implementation of security within our cubes.

In this introductory article, we will explore general concepts surrounding security, including:

  • An overview of Role-based Security in Analysis Services;
  • The two general types of Roles in Analysis Services;
  • A discussion of Role and Role Member Objects, focusing upon:
    • The Server Role
    • The Database Role
  • Access Rights and Permissions (including Permissions Inheritance and Permissions Objects)

Introducing Role-Based Security in Analysis Services

Roles are used in Analysis Services to manage security for Analysis Services objects and data. In basic terms, a role associates the security identifiers (SIDs) of Microsoft Windows users and groups that have specific access rights and permissions defined for objects managed by an instance of Analysis Services.

The Two General Types of Roles in Analysis Services

Two general types of roles are provided in Analysis Services:

  • The Server Role: a fixed role that provides administrator access to an instance of Analysis Services.
  • Database roles: roles defined by administrators to control access to objects and data for non-administrator users.

Security in Analysis Services is managed by using roles and permissions. Roles are groups of users. Users, also called members, can be added or removed from roles. Permissions for objects are specified by roles, and all members in a role can use the objects for which the role has permissions. All members in a role have equal permissions to the objects. Permissions are particular to objects. Each object has a permissions collection with the permissions granted on that object and different sets of permissions can be granted on an object. Each permission, from the permissions collection of the object, has a single role assigned to it.

Role and Role Member Objects

A Role is a containing object for a collection of users (or members). A Role definition establishes the associated membership of the users in Analysis Services. Because permissions are assigned by Role, a user must be a member of a Role before the user has access to any object.

A Role object is composed of the following parameters:

  • Name
  • ID
  • Members

The members' parameter is composed of a collection of strings. Each member contains the user name in the familiar form of "domain\username". Name is a string that contains the name of the role. ID is a string that contains the unique identifier of the role. The relevant section of a script for a Database Admin role (we’ll discuss these later) I set up in my local environment can be seen in Illustration 1.

Script of an Example Admin Role
Illustration 1: Script of an Example Admin Role

Server Role

The Server Role in Analysis Services security defines administrative access for Windows users and groups to an instance of Analysis Services. Members of this role have access to all Analysis Services databases and objects on an instance of Analysis Services, and can:

  • Perform server-level administrative functions within SQL Server Management Studio or Business Intelligence Development Studio, including creating databases and setting server-level properties.
  • Perform administrative functions programmatically with Analysis Management Objects (AMO).
  • Maintain Analysis Services Database Roles.
  • Start traces (other than for processing events, which can be performed by a Database Role with Process access).

Every instance of Analysis Services has a Server role that defines which users can administer that instance. The name and ID of this role is Administrators, and unlike Database roles (we discuss these in the next section), the Server role cannot be deleted, nor can permissions be added or removed. In other words, a user either is or is not an administrator for an instance of Analysis Services, depending on whether he or she is included in the Server role for that instance of Analysis Services.

Database Roles

A Database Role in Analysis Services defines user access to objects and data in an Analysis Services database. A Database Role is created as a separate object in an Analysis Services database, and applies only to the database in which that Role is created. Windows users and groups are included in the Role by an administrator, who also defines permissions within the Role.

The Permissions of a Role may allow members to access and administer the database, in addition to the objects and data within the database. Each Permission has one or more access rights associated with it, which in turn give the Permission finer control over access to a particular object in the database.

For more information on Permissions, see the corresponding subsections that follow.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM