Security Roles (Analysis Services - Multidimensional Data)

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Roles are used in Microsoft SQL Server SQL Server Analysis Services to manage security for SQL Server 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 SQL Server Analysis Services. Two types of roles are provided in SQL Server Analysis Services:

  • The server role, a fixed role that provides administrator access to an instance of SQL Server Analysis Services.

  • Database roles, roles defined by administrators to control access to objects and data for non-administrator users.

Security in Microsoft SQL Server SQL Server Analysis Services security 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, 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 (members). A Role definition establishes the membership of the users in SQL Server 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 parameters Name, Id, and Members. Members is a collection of strings. Each member contains the user name in the 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.

Server Role

The SQL Server Analysis Services server role defines administrative access of Windows users and groups to an instance of SQL Server Analysis Services. Members of this role have access to all SQL Server Analysis Services databases and objects on an instance of SQL Server Analysis Services, and can perform the following tasks:

  • Perform server-level administrative functions using SQL Server Management Studio or SQL Server Data Tools, including creating databases and setting server-level properties.

  • Perform administrative functions programmatically with Analysis Management Objects (AMO).

  • Maintain SQL Server 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 SQL Server 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, 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 SQL Server Analysis Services, depending on whether he or she is included in the server role for that instance of SQL Server Analysis Services.

Database Roles

An SQL Server Analysis Services database role defines user access to objects and data in an SQL Server Analysis Services database. A database role is created as a separate object in an SQL Server 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.

Permission Objects

Permissions are associated with an object (cube, dimension, others) for a particular role. Permissions specify what operations the member of that role can perform on that object.

The Permission class is an abstract class. Therefore, you must use the derived classes to define permissions on the corresponding objects. For each object, a permission derived class is defined.

Object Class
Database DatabasePermission
DataSource DataSourcePermission
Dimension DimensionPermission
Cube CubePermission
MiningStructure MiningStructurePermission
MiningModel MiningModelPermission

Possible actions enabled by permissions are shown in the list:

Action Values Explanation
Process {true, false}

Default=false
If true, members can process the object and any object that is contained in the object.

Process permissions do not apply to mining models. MiningModel permissions are always inherited from MiningStructure.
ReadDefinition {None, Basic, Allowed}

Default=None
Specifies whether members can read the data definition (ASSL) associated with the object.

If Allowed, members can read the ASSL associated with the object.

Basic and Allowed are inherited by objects that are contained in the object. Allowed overrides Basic and None.

Allowed is required for DISCOVER_XML_METADATA on an object. Basic is required to create linked objects and local cubes.
Read {None, Allowed}

Default=None (Except for DimensionPermission, where default=Allowed)
Specifies whether members have read access to schema rowsets and data content.

Allowed gives read access on a database, which lets you discover a database.

Allowed on a cube gives read access in schema rowsets and access to cube content (unless constrained by CellPermission and CubeDimensionPermission).

Allowed on a dimension grants that read permission on all attributes in the dimension (unless constrained by CubeDimensionPermission). Read permission is used for static inheritance to the CubeDimensionPermission only. None on a dimension hides the dimension and gives access to the default member only for aggregatable attributes; an error is raised if the dimension contains a non-aggregatable attribute.

Allowed on a MiningModelPermission grants permissions to see objects in schema rowsets and to perform predict joins.

NoteAllowed is required to read or write to any object in the database.
Write {None, Allowed}

Default=None
Specifies whether members have write access to data of the parent object.

Access applies to Dimension, Cube, and MiningModel subclasses. It does not apply to database MiningStructure subclasses, which generates a validation error.

Allowed on a Dimension grants write permission on all attributes in the dimension.

Allowed on a Cube grants write permission on the cells of the cube for partitions defined as Type=writeback.

Allowed on a MiningModel grants permission to modify model content.

Allowed on a MiningStructure has no specific meaning in SQL Server Analysis Services.

Note: Write cannot be set to Allowed unless read is also set to Allowed
Administer

Note: Only in Database permissions
{true, false}

Default=false
Specifies whether members can administer a database.

true grants members access to all objects in a database.

A member can have Administer permissions for a specific database, but not for others.

See Also

Permissions and Access Rights (Analysis Services - Multidimensional Data)
Authorizing access to objects and operations (Analysis Services)