sql server reporting services role assignment

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Role assignments

• 8 contributors

In Reporting Services, role assignments determine access to stored items and to the report server itself. A role assignment has the following parts:

A securable item for which you want to control access. Examples of securable items include folders, reports, and resources.

A user or group account that Windows security or another authentication mechanism can authenticate.

Role definitions define a set of permissible tasks and include:

• Content Manager
• Report Builder
• System Administrator
• System User

Role assignments are inherited within the folder hierarchy and are automatically inherited by contained:

• Shared data sources

You can override inherited security by defining role assignments for individual items. At least one role assignment must secure all parts of the folder hierarchy. You can't create an unsecured item or manipulate settings in a way that produces an unsecured item.

The following diagram illustrates a role assignment that maps a group and a specific user to the Publisher role for Folder B.

System-level and item-level role assignments

Role-based security in Reporting Services is organized into the following levels:

Item-level role assignments control access to items in the report server folder hierarchy such as:

• report models
• shared data sources
• other resources

Item-level role assignments are defined when you create a role assignment on a specific item, or on the Home folder.

System role assignments authorize operations that are scoped to the server as a whole. For example, the ability to manage jobs is a system level operation. A system role assignment isn't the equivalent of a system administrator. It doesn't confer advanced permissions that grant full control of a report server.

A system role assignment doesn't authorize access to items in the folder hierarchy. System and item security are mutually exclusive. Sometimes, you might need to create both a system-level and item-level role assignment to provide sufficient access for a user or group.

Users and groups in role assignments

The users or group accounts that you specify in role assignments are domain accounts. The report server references, but doesn't create or manage, users and groups from a Microsoft Windows domain (or another security model if you're using a custom security extension).

Of all the role assignments that apply to any given item, no two can specify the same user or group. If a user account is also a member of a group account, and you have role assignments for both, the combined set of tasks for both role assignments are available to the user.

When you add a user to a group that already has a role assignment, you must reset Internet Information Services (IIS) for the new role assignment to take effect.

Predefined role assignments

By default, predefined role assignments are implemented that allow local administrators to manage the report server. You can add other role assignments to grant access to other users.

For more information about the predefined role assignments that provide default security, see Predefined roles .

Related content

Create, delete, or modify a role (Management Studio) Modify or delete a role assignment ( SSRS web portal ) Set permissions for report server items on a SharePoint site (Reporting Services in SharePoint integrated mode) Grant permissions on a native mode report server

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

SQL Server Reporting Services SSRS 2017 and Management Studio

By: scott murray.

A little-known feature of SQL Server Reporting services is that several options can actually be managed via Management Studio. The three main options center around security role management, shared schedules for subscriptions, and job listings.

Role Management

The security Role Maintenance area serves two similar purposes: the creation, maintenance, and deletion of regular roles and the creation, maintenance and deletion of system roles. When you install SSRS, five regular roles (Browser, Content Manager, My Reports, Publisher, and Report Builder) and two system roles (System Administrator and System User) are created as noted below. Each of these roles are assigned a predefined set of permissions or tasks.

New roles can be added, and existing roles deleted (although I do not recommend deleting any of the predefined roles).

When creating new roles, the role just needs to be named and then the appropriate tasks to be assigned to that role are selected.

To maintain the role, right clicking on the role and selecting properties provides a way to update the tasks assigned to that role.

Shared Schedule Management

Shared schedules are used by subscriptions to execute reports at predefined times. To create a new schedule, right click on Shared Schedules folder and then click New Schedule.

Creating a new schedule is illustrated below. The following items needed to be completed:

• End date (optional)
• Recurrence pattern

Based on the recurrence pattern selected, different scenario and selection boxes will appear in the pattern area.  For instance, if you select Week, the repeat interval, the start time, and the days will show (as noted below). When the setup is complete, click OK.

These schedules can be deleted if necessary, or you can select properties to maintain an existing schedule. Care must be taken about deleting schedules as a schedule can be deleted even if it is being used by a subscription.

However, within the properties window, clicking on the Reports tab provides a list of all report subscription which use the selected schedule.

The Jobs folder allows for the review and canceling of current running jobs.  A job in SSRS is any of the following:

• On-demand report run by a user
• Manual creation of a report snapshot
• Manual creation of a report history snapshot
• Currently running standard subscription
• System jobs started by the report server

As shown below, double clicking on a particular job displays the job's properties including: the status, the type of job, the job action (render meaning the report is being run or rendered on the report server), the report name, the server, the user name, and the start time.

Management Studio provides quick access to several security and subscription features. We will conclude our tutorial series in the next tutorial by discussing backup methods for SSRS.

Additional Information

• SSRS Best Practices

Next >>

Comments for this article.

SQL Server Reporting Services (CRM usage, et al.): Role Assignments and Permissions

HuggyBear , 2010-08-12

While supporting Microsoft Dynamics for CRM, I was recently quite surprised to have to associate role membership permissions within Reporting Services for users of what I could only assume are extended properties, because up to this point I had thought permissions for reporting services were handled on the database engine side only. However, under OrganisationName_MSCRM and properties you will see the following Role Assignment options.

The link to your own Reporting Services instance should be the following for the above page: http://REPORTSERVER/Reports/Pages/Folder.aspx?SelectedTabId=PropertiesTab

You might also run into this error, and the above Role configuration should help you fix that:

Reporting Services Error   ________________________________________ • The permissions granted to user 'USERNAME' are insufficient for performing this operation. (rsAccessDenied) Get Online Help   ________________________________________ SQL Server Reporting Services

Log in or register to rate

You rated this post out of 5. Change rating

Related content

Book review: big red - voyage of a trident submarine.

• by Andy Warren
• SQLServerCentral.com

I've grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It's a fairly human look at what's involved in sailing on a Trident missile submarine...

1,439 reads

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

• by Robert Davis

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

1,567 reads

Inserting Markup into a String with SQL

• by Phil Factor

In which Phil illustrates an old trick using STUFF to intert a number of substrings from a table into a string, and explains why the technique might speed up your code...

1,631 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

Introduction To Role-Based Security In SQL Server Reporting Services

There are only two security guarantees after a successful installation of Reporting Services: 1) The Report Manager web application, where you can browse and view reports, will require Windows to authenticate all clients. 2) The only users authorized to use the Report Manager application will be members of the server’s Administrators group (BUILTIN\Administrators). If you want to get your reporting project off the ground and allow other people in your organization to view reports, you’ll need to add additional groups and users into roles. In this article, we will highlight some basic features of Reporting Services role-based security. Reporting Services Roles Reporting Services ships with 6 built-in roles: System Administrator, System User, Browser, Content Manager, My Reports, and Publisher. Placing a Windows user into one of these roles will give the user authorization to perform specific actions with the report manager. For example, a user in the role of Browser can view folders and reports, but not publish new reports. A user in the role of Publisher can create, view, and delete reports, but cannot create new roles. These are the default settings of Reporting Services, which a user in the System Administrator role can modify. System Administrators As mentioned earlier, the Reporting Services setup will place the local machine’s Administrators group into the System Administrators role. Since this role is an all-powerful role, you’ll want take care with any modifications you make. A true system administrator will probably also need admin privileges on the server to take care of tasks such as starting and stopping services, so consider adding the person or group into the local administrators group of the server (but think of the ramifications first). If you want to add additional users or groups to the System Administrators role without adding to the local administrators group, you’ll need to go into the System Role Assignments page. To do this interactively you can navigate to the report manager (http://reportserver/reports, for instance) and click Site Settings in the upper right hand of the screen. Towards the bottom of the Site Settings screen you’ll find three links under a security heading. Click on ‘Configure site-wide security’ to bring yourself to the following screen: Click on “New Role Assignment” to move to the next screen: From here you can enter a username, or a group name into the textbox. For machines in a domain, you can prefix the name with the domain name, (DOMAIN\scott, for example). Reporting Services will verify the entry, so don’t worry about spelling mistakes. Select the checkboxes for the role you want to assign. Notice you can also create a new system role, though we will not cover the topic in this article. Item Level Security A more common task will be adding users and groups into roles to view reports, or create and manage reports. To perform this task you will first want to navigate to the top most level of the area you want to administer. For example, to give access to the SampleReports folder and all reports underneath SampleReports, navigate to the SampleReports folder (http://reporting/Reports/Pages/Folder.aspx?ItemPath=%2fSampleReports, for example). Click on the Properties tab along the top, then click on the Security link along the side. You should now see a screen similar to the following: Click “New role assignment”, and enter a group or user name into the textbox shown below: You can select from the built-in roles shown above, or create a new role. Using the UI you can view the tasks each role has permissions to perform. The security settings you set will flow downwards, that is any folders and reports underneath the SampleReports folder will inherit these settings, so you do not need to repeat this step for each report in the folder. You can break the inheritance by defining a new security policy for a child item. Conclusion I hope that this article will give you a jumpstart on managing roles for Reporting Services. I recommend you go to the official documentation for additional reading, as this article serves as only a brief introduction to role-based security in Reporting Services. By K. Scott Allen Additional Resources More SQL Server Reporting Services articles Using Role-Based Security

All My Pluralsight Courses

30 day money back guarantee

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.

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

One response to “How Permissions can be Granted in SQL Server Report Service”

Leave a reply cancel reply.

Your email address will not be published. Required fields are marked *

• Search Knowledge Base

Grant Necessary Role for SQL Server Reporting Service

• Determining the Reporting Manager URL