MSDN Documentation

Row-Level Security in Reporting Services

Row-level security (RLS) in SQL Server Reporting Services (SSRS) enables you to restrict the data that users can see in a report based on their identity or role. This is crucial for applications where different users should only access their own data or data relevant to their department.

Understanding the Concept

The core idea is to ensure that when a user runs a report, the underlying dataset query only returns rows they are authorized to see. This is achieved by dynamically adding a filter condition to the query based on the logged-in user.

Implementation Strategies

1. Using SQL Views with User Context

This is a common and effective method. You create a SQL view that joins your data tables with a security table or uses built-in functions to determine the current user.

For example, consider a table SalesOrders and a table UserPermissions:

-- UserPermissions Table
            CREATE TABLE UserPermissions (
                UserID INT PRIMARY KEY,
                UserName NVARCHAR(100),
                AllowedRegion NVARCHAR(50)
            );

            -- Sample Data
            INSERT INTO UserPermissions (UserID, UserName, AllowedRegion) VALUES
            (1, 'Alice', 'North'),
            (2, 'Bob', 'South');

            -- SalesOrders Table
            CREATE TABLE SalesOrders (
                OrderID INT PRIMARY KEY,
                OrderDate DATE,
                Region NVARCHAR(50),
                Amount DECIMAL(10, 2)
            );

            -- Sample Data
            INSERT INTO SalesOrders (OrderID, OrderDate, Region, Amount) VALUES
            (101, '2023-10-26', 'North', 150.00),
            (102, '2023-10-26', 'South', 200.00),
            (103, '2023-10-27', 'North', 120.00);

            -- Secure View
            CREATE VIEW vw_SecureSalesOrders AS
            SELECT
                so.OrderID,
                so.OrderDate,
                so.Region,
                so.Amount
            FROM
                SalesOrders so
            INNER JOIN
                UserPermissions up ON so.Region = up.AllowedRegion
            WHERE
                up.UserName = SUSER_SNAME(); -- Or equivalent function for your database/authentication method
            

In your SSRS dataset, you would then use this view:

SELECT OrderID, OrderDate, Region, Amount FROM vw_SecureSalesOrders;

2. Using Stored Procedures

Stored procedures offer more flexibility. They can accept the user's name as a parameter and perform complex logic to filter data.

CREATE PROCEDURE usp_GetSecureSalesOrders (@UserName NVARCHAR(100))
            AS
            BEGIN
                SELECT
                    so.OrderID,
                    so.OrderDate,
                    so.Region,
                    so.Amount
                FROM
                    SalesOrders so
                INNER JOIN
                    UserPermissions up ON so.Region = up.AllowedRegion
                WHERE
                    up.UserName = @UserName;
            END;
            

When creating the dataset in SSRS, you would call this stored procedure and pass the user's name, often using a parameter that resolves to the current user:

EXEC usp_GetSecureSalesOrders @UserName = SUSER_SNAME();

Configuring SSRS for RLS

While the RLS logic resides in the data source, SSRS needs to be aware of the user's identity. Typically, SSRS uses Windows Authentication or SQL Server Authentication. The user's context is passed to the data source when the report is rendered.

Steps:

1. Design your data source to implement RLS using views or stored procedures as described above.
2. Create your SSRS Dataset to query the secure view or stored procedure.
3. Configure Data Source Credentials: Ensure your SSRS data source is configured to use the appropriate credentials that allow it to connect to the database and execute the RLS logic. This often involves using the current user's credentials if Windows Authentication is used.
4. Deploy your Report: Upload the report to the Report Server.
5. Test: Log in as different users and run the report to verify that the correct data is displayed for each user.

Security Considerations

• Ensure your UserPermissions table is highly secured and only accessible by authorized administrators.
• Regularly review and update user permissions.
• Test your RLS implementation thoroughly with various user roles and scenarios.

By implementing row-level security, you enhance the data governance and user experience of your Reporting Services reports, ensuring that sensitive information is only accessible to those who are authorized.