Quick Script to Audit Access in Report Services

Here is a quick and dirty script that will extract out security information for objects within report services.

USE [ReportServer]
GO
/* Report Services Security Review Script */
SELECT C.ParentID
                , C.ItemID
                , C.Type
                , C.Hidden
                , C.Path
                , C.Name
                , C.PolicyRoot
                , PUR.PolicyID
                , R.RoleName
                , U.UserName
                , U.Sid
                , U.UserType
                , U.AuthType
  FROM Catalog as C
INNER JOIN dbo.PolicyUserRole AS PUR ON PUR.PolicyID = C.PolicyID  
INNER JOIN dbo.Roles AS R ON R.RoleID = PUR.RoleID
INNER JOIN dbo.Users AS U ON U.UserID = PUR.UserID
ORDER BY Path
GO
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