How Permissions can be Granted in SQL Server Report Service

In this article, we look at roles being used to grant permissions on the report server. We also look at who sets these permissions how these permissions are stored.

Reporting Services in SQL Server use a role-based authorization and authentication subsystem in order to determine the users who can perform an operation or access items on a report server. Role-based system is used for categorizing authorization of different roles and action which can be performed by a user or a group. This authentication is based on custom authentication module or built-in Windows Authentication that is provided to the users. Users can custom or predefine roles using either of these authentication types.

How Permissions can be Granted in Report Service

Using Role to Grant Permissions on the Report Server

All users interact or access a report server based on the role that is defined to a specific level for a specific person or a group. Reporting Services include predefined roles which can be assigned to users or groups in order to provide them with immediate access to interact with a report server. Content Manager, Browser, and Publisher are some common examples of these predefined roles. Each of these roles, define a collection of different related tasks. For instance, a Publisher has the permission of adding reports and creating folders for storing these reports.

Role assignments are inherited from the parent node, but users can break this permission of inheritance by simply creating a new role of assignment for each particular item. Note that a user can be a member of Content Manager Role in one report and might also be a member of another report for of Browser role.

Guidelines for granting access to different report server operations and items

1.    Review all predefined roles and determine whether they can be used as it is. If the user needs to adjust any tasks or define any additional roles, he/she should do it before assigning users to specific roles.

2.    Identify users or groups that require access to that particular report server, and on what levels. Most users are assigned to Browser role or Report Builder role. And only selective users need to be assigned for Publisher role. And Content Manager Role should only be assigned to the trusted officials.

3.    Use Report Manager for assigning roles in the Home folder for each group or user who requires access.

4.    Then go to Report Manager’s Site Settings page and create an assignment for system-level roles for each group or user, using predefined roles System Administrator and System User.

5.    Create additional assignments for assigning access to specific folders, reports, and other items. Avoid creating too many role assignments.

Who Sets These Permissions?

Initially, Report server can be accessed by the local administrator’s group or its members. Reporting Services are installed with only two default roles assignments which are used for system-level and granting item-level access to local administrators group and its member. These groups and members are responsible for assigning permission to other users.

How Are These Permissions Stored?

Report Server stores its role definitions and assignments in its database. If a user uses multiple programmatic interfaces or client tools, the access is subjected to the permissions which are defined as a whole for the report server. Role assignment is stored with all the items that they secure, that allow the user to move a database to a different report server without losing the defined permissions.

While MS SQL Server is a highly advanced platform, it still ends up getting plagued by data errors. Always keep a powerful SQL Server repair tool around to deal with unexpected data errors.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com