Script: Compare Reporting Services Permissions.

Here’s a script I came up with the other day – perhaps you’ll find it useful.

I received a ticket to assign permissions to a user in SSRS, to match an existing user. Sadly, both our folder and permissions hierarchy are pretty ugly. No AD groups to add the user to, no top level folder, etc.  Everything is kind of hit and miss. (Yes, I menat AND not OR.)

So I needed a quick way to determine what to grant where.  Here’s what I came up with. It started with a script I found on StackOverflow, written by Andrew Bay. I took that and built a basic script that takes two user names, builds a permissions table for each one then FULL JOINs the two, to determine where permissions do not match. You can add the WHERE clause of your choice to check for NULL on either side.  Here it is:

/***************************************************************

  Name: SSRS_User_Permissions_Comparison.sql

  Project/Ticket#: --

  Date: Jan 11 2013

  Requester: --

  DBA: David M Maxwell

  Step: -- of --

  Server: (Reporting Services DB Server)

  Instructions: Set the user names at the top of the script and 

  run to compare the permissions between the two users.
***************************************************************/


USE [ReportServer];
GO

DECLARE @usera VARCHAR(100) = 'SAFEAUTONET\usera';
DECLARE @userb VARCHAR(100) = 'SAFEAUTONET\userb';

IF OBJECT_ID('tempdb.dbo.#usera') IS NOT NULL
BEGIN 
  DROP TABLE #usera
END

IF OBJECT_ID('tempdb.dbo.#userb') IS NOT NULL
BEGIN 
  DROP TABLE #userb
END

SELECT  C.UserName
      , D.RoleName
      , D.Description
      , E.Path
      , E.Name
INTO #usera
FROM    dbo.PolicyUserRole A
INNER JOIN dbo.Policies B
        ON A.PolicyID = B.PolicyID
INNER JOIN dbo.Users C
        ON A.UserID = C.UserID
INNER JOIN dbo.Roles D
        ON A.RoleID = D.RoleID
INNER JOIN dbo.Catalog E
        ON A.PolicyID = E.PolicyID
WHERE c.UserName = @usera

SELECT  C.UserName
      , D.RoleName
      , D.Description
      , E.Path
      , E.Name
INTO #userb
FROM    dbo.PolicyUserRole A
INNER JOIN dbo.Policies B
        ON A.PolicyID = B.PolicyID
INNER JOIN dbo.Users C
        ON A.UserID = C.UserID
INNER JOIN dbo.Roles D
        ON A.RoleID = D.RoleID
INNER JOIN dbo.Catalog E
        ON A.PolicyID = E.PolicyID
WHERE c.UserName = @userb

SELECT * FROM #usera A
FULL JOIN #userb B
  ON A.path = b.Path
 AND a.name = b.Name

/* To see what userA has that userB does not: */
--WHERE b.name IS NULL;

/* To see what userB has that userA does not: */
--WHERE a.name IS NULL;
GO

Hope you find that useful. I certainly do.

Thanks for reading.
-David.