 |
|
| SQL Server Tips: |
|
|
|
DATA WAREHOUSING AND BUSINESS INTELLIGENCE
Managing permissions in SQL Server Reporting Services
Eric Johnson
09.09.2007
Rating: -4.43- (out of 5)
|
SQL Server Reporting Services (SSRS) is an extremely handy tool in the SQL Server 2005 suite. You can use it to easily create, deploy and manage reports for your entire organization. Its simplicity, however, can lead to a less then optimal configuration. You set it up, toss in some reports and walk away. The problem is that you really don't know who can see your reports. How can you manage permissions in SQL Server Reporting Services to ensure that sensitive reports are only seen by the people authorized to view them?
Because much of a company's data is sensitive and should not be seen by everyone, data protection is a priority. In this tip, we will cover the SSRS security model and talk about how you can leverage it to lock down your SSRS environment.
Overview
SQL Server Reporting Services security is managed on two levels: the SSRS site and on items within the site. At each level, the tasks that users can perform are managed via roles. Roles are just groups with certain tasks assigned to them and members of the role can perform the assigned tasks. It's that simple. Taking some time to create appropriate roles and assigning users accordingly will ensure that your SSRS site, and the reports it houses, are secure.
Site-level security in Reporting Services 2005
The first level of security is site-level security. On the SSRS site as a whole, you can manage the tasks your users are allowed to perform. The tasks are fixed, and you need to create your roles with these fixed tasks in mind. At this level, you will be assigning "administrative" tasks to users. Standard users that just need to view reports will probably not require any permissions at this level of SQL Server Reporting Services, beyond the ability to view properties and schedules and execute report definitions. Here are the tasks you can assign to roles at the site level:
To control which users are allowed to perform each of these tasks, you first create a system
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
-level role. There are two built in system-level roles when you install SQL Server Reporting Services.
System Administrator. This system-level role manages all aspects of the SSRS site. The only task that cannot be performed by default is the Generate Events task. If you want administrators, or any user for that matter, to be able to perform this task, you must explicitly assign it to the appropriate role.
System User. These users are allowed to view report server properties and shared schedules and execute report definitions. These tasks are assigned so that users can run reports.
Take a look at the screenshot below. To create new roles, click Site Settings in the top right corner of the Report Browser and then select Configure system-level role definitions under Security. This will open the System Roles page: To create a new role, click the New Role button. This will open the New System Role page, shown in the screenshot. All you have to do now is name the role, give it a description, and then select all the tasks you want this role to be able to perform. When you're done, click OK.
[IMAGE]
(Click on image for enlarged view.)
To assign users to this newly created role, go back to the site settings screen and select Configure Site-wide Security. This will open the System Role Assignments screen and you can simply click New Role Assignment to add new Windows users or groups to one of your SSRS system-level roles.
Item-level security in Reporting Services 2005
Item-level security is managed in much the same way as site-level security. You still work with roles and tasks, but the role assignment is done on a per-item basis. In other words, a user in the Browser role for one folder may be in the Content Manager role on another folder. Here are the tasks that can be assigned to item-level roles:
As with system-level roles, there are some built-in item-level roles you can use when assigning permissions in this area of SQL Server Reporting Services. If these roles aren't enough, you can build additional roles and assign users any combination of the item-level tasks we just looked at. The built-in item-level roles are as follows:
Browser. This role is configured to allow users to view folders and reports and lets them subscribe to reports.
Content Manager. These users can manage the content of the SSRS site, including managing folders, reports and resources.
My Reports. Users are allowed to publish reports and manage reports, folders and resources in their My Reports folder.
Publisher. This user can publish reports and manage reports, folders and resources on the report server.
Report Builder. Report Builders have permission to view report definitions.
Now, let's get down to the details. To manage these roles, you have several options. Item-level security can be applied to a folder, report, data source or resource. To give SQL Server users permission to an item, you need to open that item and view its security properties. When you add a user, you also have to assign a user to a role for that item. In the case of folders, the role a user is assigned at the top-level folder will, by default, be inherited by other items inside that folder. You do have the ability to override security on a lower-level folder of item-level security.
At this point, the security of your SSRS server is entirely up to you. You can create different folders for each department and assign only employees in that department with access to that folder. Within each department folder, I like to create an additional folder for sensitive reports and further lock that folder down to the appropriate users. Take some time and really plan out how your reports will be placed on the server and how you want the security to look. When using SQL Server Reporting Services, there is no reason that all of your reports, regardless of sensitivity, can't be stored in a single report server.
[TABLE]
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
