Viewing active (and missing) permissions in SSRS and Power BI Server

To see who has folder/report level permissions in SSRS and Power BI Server, you have to join a couple of tables together – I started with a query I found at StackOverflow and built onto it – here’s my query (it pulls a bit more detail and shows not just permissions granted, but those not granted):

select  case E.Path
		when '' then '/'
			else E.Path
		end as ItemPathAndName,
		concat(LEFT(E.Path, case
			when len(E.Path)-len(E.Name) = 0 then 0
				else len(E.Path)-len(E.Name)-1 end),'/'
		) as ItemPath,
		E.Name as ItemName,
		case E.Type
			when 1 then 'Folder'
			when 13 then 'PBI Report'
			else concat('Unknown (',Type,')')
		end as ItemType,
		case e.PolicyRoot
			when 0 then 'Inherited'
			when 1 then 'Customized'
			else 'Unknown'
		end as PermissionSource,
		C.UserName,
		D.RoleName,
		--D.Description, -- Uncomment for role description
		convert(bit, case when
				a.id is null then 0
				else 1
		end) as ValidPermission
from dbo.Catalog E
cross join dbo.Users C
cross join dbo.Roles D
   left
   join dbo.PolicyUserRole A
     on E.PolicyID = A.PolicyID 
	and A.RoleID = D.RoleID
	and A.UserID = C.UserID
order by Path, C.UserName

Rather than just selecting the permissions, this query actually shows all possible permissions, and shows where users have or don’t have them. In my case, I wanted to see a list of users on the server that lacked access to specific reports and the “show me the permissions” query wouldn’t do that – additionally, this query can show you all the places that your permissions are manually set, rather than inherited.

To interpret them, I copied the results to Excel, created a pivot table, and then filtered using PermissionSource=Customized (to see all the custom permissions) or ValidPermission=0 (to see the places where people didn’t have permission to view an item).

The crossjoins can make the resultset a bit large, but it worked for me. If you want to filter things down a bit, you can add a WHERE clause near the end to folder those columns for the specific situation you’re looking for.

Migrate database indexes to a new file group

I recently had to mass-migrate all the indexes from a database to a new file group since we’d added some additional storage to our database server. I found¬†this article at SQL Server Central¬†(unfortunately, registration required, so I’ve included a copy of the original script in the download at the end). While it worked okay, there were some things I didn’t like about it:

  • Assumed 90% index fill rate
  • “Moved” indexes were all created as non-unique, regardless of original
  • Fail during index creation left you without an index (drop and then create, with no rollback)
  • Table was un-indexed during the move (index dropped and then created)
  • Script re-created indexes without any “Included” columns, even if original index had them

To address these limitations, I rebuilt the process using that script as a starting point. The new script:

  • Uses 90% fill rate by default, but if the original index had a different rate specified, it will use that
  • Re-creates indexes as unique if the source index was unique
  • Rollback problem resolved – new index is created with different name, old index is dropped, and then new index is renamed, all in a TRY-CATCH block
  • Since the new index is created and then the old one dropped, table indexing remains “online” during the move
  • Migrates “Included” columns in index
  • Updated the script to use SYS views (breaks compatibility with SQL 2000, since SYS is 2005/2008/beyond only)
I welcome any feedback on the script, and would love to know if you see any improvements that should be made.

Download .SQL scripts (contains both Original and Modified scripts)