Free SQL Help for Devs, DBAs, and the Curious

Streamlining permission management is a critical aspect of any robust security architecture. When SQL Server Reporting Services (SSRS) is included in your data storage and management structure, it is crucial to implement custom role-based access control (RBAC) for folder permissions. This blog post aims to guide you through a process of creating and managing these custom folder permissions, utilizing technologies from SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5, and Azure SQL.

Managing permissions at the folder level in SSRS is vital for maintaining a secure and organized database structure. By implementing custom RBAC, you can ensure that users only have access to the data they need, reducing the risk of accidental or malicious data alteration or disclosure. This is especially important in environments where sensitive or confidential data is being handled.

To begin, it's important to understand that SSRS utilizes a hierarchical structure for its folders. Folders can contain other folders, reports, and resources. Permissions in SSRS are managed through roles, and these roles can be assigned to users or groups at any level in the folder hierarchy.

In SQL Server 2012 and 2014, you can create custom roles by navigating to the Site Settings page, then clicking on the Security tab. From here, you can choose to create a New Role. In the New Role page, you can specify a name and description for your role, and choose which tasks this role should be allowed to perform.

-- SQL Server 2012/2014: Creating a new role
USE [ReportServer]
GO
EXEC sp_addrole 'NewRoleName', 'RoleDescription'
GO

In MySQL 5.6, you can create roles using the CREATE ROLE statement, and grant permissions to these roles using the GRANT statement. You can then assign these roles to users with the SET ROLE statement.

-- MySQL 5.6: Creating a new role and assigning permissions
CREATE ROLE 'new_role';
GRANT SELECT ON database.* TO 'new_role';
SET ROLE 'new_role' FOR 'user'@'localhost';

DB2 10.5 also allows for the creation of roles and the assignment of permissions using a similar syntax to MySQL. To create a role in DB2, you would use the CREATE ROLE statement, and to grant permissions to a role, you would use the GRANT statement.

-- DB2 10.5: Creating a new role and assigning permissions
CREATE ROLE new_role;
GRANT SELECT ON TABLE schema.table TO ROLE new_role;

Azure SQL, being a cloud-based database service, offers a slightly different approach to managing roles and permissions. In Azure SQL, you can create roles using the CREATE ROLE statement, and assign permissions to these roles using the GRANT statement, similar to SQL Server and MySQL. However, Azure SQL also allows you to assign roles to users at the database level, using the sp_addrolemember stored procedure.

-- Azure SQL: Creating a new role, assigning permissions, and assigning the role to a user
CREATE ROLE new_role;
GRANT SELECT ON SCHEMA::schema TO new_role;
EXEC sp_addrolemember 'new_role', 'username';

With these custom roles and permissions in place, managing access to SSRS folders becomes a significantly more streamlined process. By effectively using RBAC in your SSRS environment, you can ensure that data access is controlled and secure, while still providing users with the access they need to perform their jobs effectively.