SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

How To Determine User permissions on a Report Item

Recently while delivering a SQL Server Reporting Services workshop, a customer presented to me a very interesting question.

In their environment, they have multiple roles defined in SSRS, and when I say multiple, I am talking about more than 300 roles defined in SQL Reporting Services (customer has a work force of over 200K). My initial reaction was one of awe, as this is probably the first time I have seen anyone using this many roles and have that many users defined in SQL Reporting Services.

Their requirements were two fold

  1. Given a Report Item name and the User name, they need to find the permissions this user has on a report item.
  2. If the user is part of multiple roles, they also need to figure the permissions associated with each role.

The first part of the problem is real simple. In the Report Server Database we have 4 tables, writing a query one which provides the role membership of a user on the report items.

  • dbo.Roles
  • dbo.Catalog
  • dbo.Policies
  • dbo.PolicyUserRole
   1: Select C.Name,R.RoleName, U.UserName from 

   2: dbo.Catalog C 

   3: inner join dbo.PolicyUserRole PR on C.PolicyID = PR.PolicyID

   4: inner join dbo.Roles R on R.RoleID = PR.RoleID

   5: inner join dbo.Users U on PR.UserID = U.UserID 

   6: where U.UserName = '<reportusername>' and C.Name = ‘<report Item Name>’ 

The above query returns the effective permissions on the report Item for a given user.

The second part of the problem is a bit complex. In the Report Server Database, we do not store the explicit permissions for the Roles. What we instead do, is store it as a Bit Mask in the dbo.Roles table.

The TaskMask column in the dbo.Roles tables maps to the effective permissions for each role. There is no simple way to find what these bit mask values represent, so I created two static tables in the Report Server database or any other database (preferable) and then use these two tables in the function I created.

The Two Tables I created were

   1: create table SystemRoleTemp (ID int IDENTITY(1,1), permission varchar(200))

   2:         insert into SystemRoleTemp(permission) values 

   3:          ('Manage Roles')

   4:         ,('Manage Report Server Security')

   5:         ,('View Report Server Properties')

   6:         ,('Manage Report Server Properties')

   7:         ,('View Shared Schedules')

   8:         ,('Manage Shared Schedules')

   9:         ,('Generate Events')

  10:         ,('Manage Jobs')

  11:         ,('Execute Report Definitions')

   1: create table UsersRolesTemp (ID int IDENTITY(1,1), permission varchar(200))

   2:         insert into UsersRolesTemp(permission) values 

   3:         ('Set Security for Individual Items')

   4:         ,('Create Linked Reports')

   5:         ,('View Reports')

   6:         ,('Manage Reports')

   7:         ,('View Resources')

   8:         ,('Manage Resources')

   9:         ,('View Folders')

  10:         ,('Manage folders')

  11:         ,('Manage Report history')

  12:         ,('Manage Individual Subscriptions')

  13:         ,('Manage All Subscription')

  14:         ,('View Data Sources')

  15:         ,('Manage Data Sources')

  16:         ,('View Models')

  17:         ,('Manage Models')

  18:         ,('Consume Reports')

These two tables map the permissions for the System Roles and Item Specific roles.

Next I created a UDF to return the permissions associated with each role.

The UDF check for the corresponding bits of the BitMask and appends the permissions in a string, the function then returns the string to the caller.

   1: Alter function dbo.EffectivePermissionsRoles (@TaskMask nvarchar(20), @RoleFlag tinyint)

   2: Returns varchar(500)

   3: As

   4: Begin

   5:     Declare @effectivepermissions varchar(600) ='Role Permissions ==> '

   6:     declare @taskmaskindex int

   7:     set @taskmaskindex = LEN(@TaskMask)

   8:     declare @count int = 1

   9:     declare @temp varchar(50)

  10:     

  11:     ---- For System Roles

  12:         

  13:         If (@RoleFlag = 1)

  14:         begin

  15:             While @count < @taskmaskindex

  16:                 begin

  17:                     if(SUBSTRING(@TaskMask, @count, 1) ='1')

  18:                     begin

  19:                         Select @temp = permission + ','  from dbo.SystemRoleTemp where ID = @count

  20:                     end

  21:                     Set @effectivepermissions = @effectivepermissions + ISNULL(@temp,'')

  22:                     Set @temp = ''

  23:                     Set @count = @count+1

  24:                 end

  25:         end

  26:         

  27:         --- For Item Specific Roles

  28:         If (@RoleFlag = 0)

  29:         begin

  30:                 While @count < @taskmaskindex

  31:                 begin

  32:                     if(SUBSTRING(@TaskMask, @count, 1) = '1')

  33:                     begin

  34:                         Select @temp = permission  + ',' from UsersRolesTemp where ID = @count

  35:                     end

  36:                     Set @effectivepermissions = @effectivepermissions + ISNULL(@temp,'')

  37:                     Set @temp = ''

  38:                     Set @count = @count+1

  39:                 end

  40:         end

  41:     return @effectivepermissions      

  42: end

  43: GO

Finally, we can put all of it together in a Stored Procedure or in a SQL Query as follows, to return the effective permissions for the users on a particular report Item.

   1: Select C.Name,R.RoleName, U.UserName, dbo.EffectivePermissionsRoles(R.TaskMask, R.RoleFlags)  As RolePermissions 

   2: from 

   3: dbo.Catalog C 

   4: inner join dbo.PolicyUserRole PR on C.PolicyID = PR.PolicyID

   5: inner join dbo.Roles R on R.RoleID = PR.RoleID

   6: inner join dbo.Users U on PR.UserID = U.UserID 

   7: where U.UserName = '<User Name>'  and C.Name = '<ReportItemName>'

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: