When working at large organizations with several users, managing security using Windows or SQL authentication could be challenging. However, there are a couple of solutions that can be used to quickly and easily resolve the problem. First, you could follow the steps in this whitepaper, Implementing Row and Cell Level Security in Classified Databases. In addition, you can use the SQL Server Label Security Toolkit to assist in the configuration. On the other hand, if your implementation does not require the level of security or complexity described in the paper you can take a more subtle approach.

Before I proceed any further let’s set the stage a little. Assume you are required to create a Sales Report, which is pretty straightforward. But wait, there is one small requirement. You must ensure that a Territory Manager will only see those sales for his/her territory. Now there are several ways to accomplish this, and in this post I will outline one approach that I have implemented on several occasions.

Add a Few Tables

The first step in my approach is to add a minimum of two tables to your databases. Let’s start with a few tables from the AdventureWorks2008R2 database:

To implement row-level security you must start by creating a table that will store your territory manager’s windows user account. The following script creates a table that will store the windows account and an identity column.

IF(OBJECT_ID(‘Users’))IS NOT NULL
    DROP TABLE Users
GO

CREATE TABLE Users

(

    UserID int identity(1,1)
        CONSTRAINT PK_Users_UserID PRIMARY KEY,
    WindowsUserName varchar(75) NOT NULL        

       CONSTRAINT UQ_Users_WindowsUsername UNIQUE

)

GO

Instead of using the windows account as the primary key, I have opted to use an identity column. In my opinion this makes it easier to maintain and it adds flexibility to the solution, as you will see later in the post. The next step is to insert the windows account for each of your territory managers. You could add additional columns to this table if needed. When inserting the accounts don’t forget to prefix the account with the domain name. The following is a sample insert statement:

INSERT INTO Users(WindowsUserName)

VALUES(‘mydomain\pleblanc’)

GO

Next, you will create a bridge table that relates the user to the territory. The following script creates the table:

IF(OBJECT_ID(‘TerritoryUsers’)) IS NOT NULL

    DROP TABLE TerritoryUsers

GO

CREATE TABLE TerritoryUsers

(

    TerritoryID int NOT NULL,

    UserID int NOT NULL,

        CONSTRAINT PK_TerritoryUsers_TerritoryIDUserID PRIMARY KEY (TerritoryID, UserID),

        CONSTRAINT FK_Users_To_TerritoryUsers_On_UserID FOREIGN KEY (UserID) REFERENCES Users(UserID),

        CONSTRAINT FK_Territory_To_TerritoryUsers_On_TerritoryID FOREIGN KEY (TerritoryID) REFERENCES Sales.SalesTerritory(TerritoryID)

)

GO

After the TerritoryUsers table is created your diagram should resemble the following image:

Initially I did not have the Users table. Instead I included the windows account in the TerritoryUsers table. However, after a little thought I realized that if I need to implement this approach in the same database for a different reason, I would be required to maintain windows account in separate tables. As a result, I decided to make one central table with an identity column that can be used throughout the entire database. After you create the table you will insert a territory manager combination for each territory that a manager should see. The following script is a sample insert statement:

INSERT
INTO TerritoryUsers(TerritoryID, UserID)

VALUES

    (1,1),(2,1),
(3,1)

GO

In the above query I have associated my windows account with three territories from the Sales.Territory table. Now that I have my model in place the next step is the write the query.

The Query

The query for the report started out as follows:

SELECT

    st.[Group],

    st.Name,

    soh.TotalDue,

    tu.TerritoryID

FROM Sales.SalesOrderHeader soh

INNER
JOIN Sales.SalesTerritory st

    ON soh.TerritoryID = st.TerritoryID

A very simple query, but when a manager executes a report that uses this query as the source, all territories are returned. By making a slight modification to the query to include JOINS to both of our new tables we can quickly circumvent that problem. See the following query:

SELECT

    st.[Group],

    st.Name,

    soh.TotalDue,

    tu.TerritoryID

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesTerritory st

    ON soh.TerritoryID = st.TerritoryID

INNER JOIN TerritoryUsers tu

    ON st.TerritoryID = tu.TerritoryID

INNER JOIN Users u   

    ON tu.UserID = u.UserID

WHERE

    WindowsUserName = ‘mydomain\pleblanc’

Note the highlighted sections of the query. I have included the two new tables in the query and also a where clause that filters the query by my account. No you may be thinking, that’s going to work now because you explicitly provided your windows account. Where am I going to get that from dynamically? Keep reading and I promise you will find out. So let’s turn this query into a stored procedure, converting the hard-coded windows username into a parameter:

IF(OBJECT_ID(‘GetTerritorySales’))IS NOT NULL

    DROP PROC GetTerritorySales

GO

CREATE PROC GetTerritorySales

@WindowsUserName varchar(75)

AS

SET
NOCOUNT
ON

SELECT

    st.[Group],

    st.Name,

    soh.TotalDue,

    tu.TerritoryID

FROM Sales.SalesOrderHeader soh

INNER
JOIN Sales.SalesTerritory st

    ON soh.TerritoryID = st.TerritoryID

INNER
JOIN TerritoryUsers tu

    ON st.TerritoryID = tu.TerritoryID

INNER
JOIN Users u   

    ON tu.UserID = u.UserID

WHERE

    WindowsUserName = @WindowsUserName

SET
NOCOUNT
OFF

GO

Finally, all of the plumbing is in place to implement the solution in an SQL Server Reporting Services (SSRS) Report.

The Report

Before we create the report, you will need to create a Login on your SQL Server. This account can be a SQL or Windows account. The only thing that really matters is that the account has appropriate permissions to access all the necessary data to fulfill the requirements of the report. You will use the account when you create the data source for your report. The next step is to create your report and add a data set that uses the stored procedure that was created above. Once this is in place double click the parameter that was automatically created when you created your data set. On the Report Parameter Properties dialogue, ensure that General is selected in the left pane, click the radio button labeled Hidden. Next click the Default Values item in the left pane. Choose the radio button labeled Specify values. Click the button labeled Add. Next click the function button next to the newly created text box. In the box labeled Category on the Expression dialogue window. Double click UserID in the box labeled Item. Click OK. You screen should resemble the following:

Click OK again, and your screen should resemble that following image:

Finally click OK and run your report. The corresponding sales for the Territories that are associated to the account that you logged on as will be displayed. Now when a manager runs the report, he/she will only see the sales that are associated to their territories.

As I stated earlier there are several approaches to solve this problem. If you have any questions, comments or concerns about this post please send me an email at pleblanc@sqllunch.com.

Talk to you soon,

Patrick LeBlanc