SQL Server Reporting Services is a convenient application for generating reports quickly and efficiently. Its back-end components are a bit more confusing to an unsuspecting administrator.
This article delves into the ReportServer database, revealing the tables and data that are used to power SSRS. In addition, the ability to alter data in these tables is presented as a way to avoid time-consuming migration or data modification processes.
Overview of SSRS Metadata
By default, the SSRS database is given the name ReportServer. This can be adjusted when the SSRS instance is installed or at a later time via the Report Server Configuration Manager. Within this database are a set of tables that describe every object in Reporting Services. The following is a brief overview of these tables and the data that resides in each.
A Warning About the ReportServer Database
Microsoft does not formally document the ReportServer database. It is an internal database to Reporting Services but is maintained in plain sight for administrators to use if needed. It can be freely read or written, and no internal process will stop us from doing so.
Because it is undocumented, take extra caution when making any changes to it! Always include this database in routine database backup processes and ensure it is backed up frequently enough to allow for meaningful recovery if it is ever needed. In addition, always perform a backup of this database prior to modifying any data stored within it. Forgetting a WHERE clause or accidentally deleting the wrong rows could result in reports becoming unavailable to end users, so always exercise caution and due diligence before making any changes to ReportServer data!
Catalog
This table contains all of the report objects that a user can interact with via the Web Portal UI, such as reports, data sources, and images. This data is stored as a hierarchy with a root/parent path and all other objects below it in a tree. The following query returns some basic data from this table:
SELECT
CHILD_ITEM.Path AS Item_Path,
CHILD_ITEM.Name AS Item_Name,
CASE
WHEN CHILD_ITEM.Type = 1 THEN 'Folder (1)'
WHEN CHILD_ITEM.Type = 2 THEN 'Report (2)'
WHEN CHILD_ITEM.Type = 3 THEN 'File (3)'
WHEN CHILD_ITEM.Type = 4 THEN 'Linked Report (4)'
WHEN CHILD_ITEM.Type = 5 THEN 'Data Source (5)'
WHEN CHILD_ITEM.Type = 6 THEN 'Report Model (6)'
WHEN CHILD_ITEM.Type = 7 THEN 'Report Part (7)'
WHEN CHILD_ITEM.Type = 8 THEN 'Shared Data Set (8)'
WHEN CHILD_ITEM.Type = 9 THEN 'Report Part (9)'
WHEN CHILD_ITEM.Type = 11 THEN 'KPI (11)'
WHEN CHILD_ITEM.Type = 12 THEN
'Mobile Report Folder (12)'
WHEN CHILD_ITEM.Type = 13 THEN
'PowerBI Desktop Document (13)'
END AS Item_Type,
PARENT_ITEM.name AS Parent_Item_Name,
CHILD_ITEM.Description AS Item_Description,
CHILD_ITEM.Hidden AS Is_Hidden,
CHILD_ITEM.CreationDate,
CHILD_ITEM.ModifiedDate,
CHILD_ITEM.ContentSize
FROM dbo.Catalog CHILD_ITEM
LEFT JOIN dbo.Catalog PARENT_ITEM
ON PARENT_ITEM.ItemID = CHILD_ITEM.ParentID;
Only a small subset of available columns are returned, but they provide a solid overview of what is contained in the table and how it is formatted:
The results show part of the contents of my local SSRS test server, which includes some folders, reports, and data sources. In general, if detail on reports is needed, this is a good place to start. It can be difficult to get a complete view of the SSRS landscape from the hierarchical web interface, but here it is easy to get a list of objects and then filter accordingly.
Users
Within the site settings in SSRS, users/groups can be added, removed, or have their permissions adjusted. This security is separate from the logins and users that are maintained separately by SQL Server. Some high-level details about all users/groups defined in SSRS can be found in the Users table:
SELECT
Users.UserID,
Users.UserName,
Users.UserType,
Users.AuthType,
Users.ModifiedDate
FROM dbo.Users;
The results are as follows:
UserType indicates the source of the user, which typically will be 0 (a SQL Server user/login) or 1 (a domain user or group). AuthType indicates the type of authentication used for the user, which will often be 0/1 (Windows user/group) or 2 (SQL auth). See the references at the end of this article to get full lookups for these user properties.
Subscriptions
This table contains all subscriptions defined in SSRS. Some joins are needed to get info on the report that the subscription is attached to, who owns it, or who modified it last:
SELECT
Subscriptions.Description,
Subscriptions.LastStatus,
Subscriptions.EventType,
Subscriptions.LastRunTime,
Subscriptions.Parameters,
SUBSCRIPTION_OWNER.UserName AS SubscriptionOwner,
Catalog.Name AS ReportName,
MODIFIED_BY.UserName AS LastModifiedBy,
Subscriptions.ModifiedDate
FROM dbo.Subscriptions
INNER JOIN dbo.Users SUBSCRIPTION_OWNER
ON SUBSCRIPTION_OWNER.UserID = Subscriptions.OwnerID
INNER JOIN dbo.Catalog
ON Catalog.ItemID = Subscriptions.Report_OID
INNER JOIN dbo.Users MODIFIED_BY
ON MODIFIED_BY.UserID = Subscriptions.ModifiedByID;
This is some exceptionally useful information! There is no central way to manage subscriptions in SSRS, and therefore, getting a complete view in one place is quite helpful. The results for a few test subscriptions I have created are as follows:
A busy SSRS server could have dozens or even hundreds of subscriptions. A common use of this data is for security audits. Knowing who has access to a report can be exceptionally valuable as the login that accesses data may not be the same as the person that reviews the report. Therefore, a simple audit of SQL Server access may not provide a complete enough picture of who can access a given data set. Subscriptions can be automatically sent to email addresses or files, thereby allowing data to be made available under other security contexts that the SQL auth or domain login a user typically uses.
A SQL Server Agent is created with every subscription to that is used to trigger the report to run at the specified time. The job names, though, are at first glance meaningless:
Typically, when I name a job, I provide something a bit more descriptive than that 😊 These guids that are represented internally in SSRS as the ID of the report schedule. This data can be viewed by adding an additional join onto the previous query, like this:
SELECT
ReportSchedule.ScheduleID AS AgentJobName,
Subscriptions.Description,
Subscriptions.LastStatus,
Subscriptions.EventType,
Subscriptions.LastRunTime,
Subscriptions.Parameters,
REPLACE(SUBSCRIPTION_OWNER.UserName, 'Datto', 'PIKACHU')
AS SubscriptionOwner,
Catalog.Name AS ReportName,
REPLACE(MODIFIED_BY.UserName, 'Datto', 'PIKACHU') AS LastModifiedBy,
Subscriptions.ModifiedDate
FROM dbo.Subscriptions
INNER JOIN dbo.Users SUBSCRIPTION_OWNER
ON SUBSCRIPTION_OWNER.UserID = Subscriptions.OwnerID
INNER JOIN dbo.Catalog
ON Catalog.ItemID = Subscriptions.Report_OID
INNER JOIN dbo.Users MODIFIED_BY
ON MODIFIED_BY.UserID = Subscriptions.ModifiedByID
INNER JOIN dbo.ReportSchedule
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
AND ReportSchedule.ReportID = Catalog.ItemID;
The results show the added column of AgentJobName (aka: ScheduleID):
This allows us to see which SQL Server Agent job corresponds to each report subscription. In a pinch, a job could be disabled, executed manually, or adjusted as needed. Note that when a subscription is modified, the job is recreated. Therefore, making extensive changes to these SQL Server Agent jobs is not a good permanent solution. This helps to demystify what the poorly named jobs correspond to, and which subscriptions and reports correspond to which jobs.
Execution logs
There is a central execution log table called ExecutionLogStorage, as well as three preconfigured views that provide some additional details as to what various encoded column values mean. This data is quite valuable as it can be used to audit report executions, successes, and failures. The latter is especially useful as it allows for a customized response to scenarios when reports fail to execute. Responses could vary from automatically rerunning a subscription or emailing an operator with the details of the failure.
The view ExecutionLog cleans up a bit of the execution log data but mostly keeps it in a somewhat cryptic and challenging to read format. The following results are for a straight SELECT * from the view:
The most important columns require no modifications: Start and end times, byte count, row count, and the time spent processing and rendering can assist in troubleshooting reports that are failing or taking a long time to complete. Like any reporting or analytics application, if SSRS gets stuck trying to render a billion rows or a terabyte of data directly to a web browser or file, it’s unlikely to end well. The parameter details are also valuable when trying to reproduce a problem, as well as determine the best solution.
Microsoft later added two more creatively named execution logs: ExecutionLog2 and ExecutionLog3. These provide more detail as well as lookups for some of the available dimensions. The following is a sample of the output from selecting all columns from dbo.ExecutionLog2:
There are more columns that didn’t fit on the screen, including the Source (was the report run live or via a subscription), the status (was it successful?), and a variable XML field with additional info that does not fit into any of the preconstructed columns. This view is a worthwhile expansion on the original execution log. ExecutionLog3 is similar to ExecutionLog2, with only a few tweaks to column names and contents.
A common use of this data is to automatically report on report failures. By default, Reporting Services has no mechanism to let you know when a report fails. Report failures can be indicative of anything from a query error to a timeout to a network outage. Therefore, having reliable reporting on them can improve troubleshooting production issues (on top of allowing us to fix broken reports faster). The following query will return a data set containing any reports that failed in the past hour:
SELECT *
FROM ReportServer.dbo.ExecutionLog2
WHERE DATEDIFF(MINUTE, TimeStart, GETDATE()) <= 60
AND Status <> 'rsSuccess'
AND ReportPath <> ''
AND status <> 'rsHttpRuntimeClientDisconnectionError'
ORDER BY TimeStart ASC;
The filters here are quite useful, as they:
- Return reports from the past 60 minutes. Data in ReportServer is stored in the local server time zone, therefore
GETDATE() or an equivalent should be used to interrogate this data.
- Only return reports that are unsuccessful.
- Exclude reports that are run directly from Report Builder (these will have a blank path).
- The additional status filter removes reports that failed because the user’s web browser closed.
Here is a sample of some of the results:
A simple SQL Server Agent job or some other scheduled task could run this query hourly and send an alert/report out when any rows are returned. For those looking to minimize work needed on alerting, an SSRS (or some other type of) report could be created that conditionally emails the details based on the contents of this result set.
An important key here is that SQL Server does NOT automatically report on SSRS failures, whether ad-hoc or scheduled via subscriptions. The details of failures are logged, but it is up to the report server owner or another administrator to manage this data and alert appropriately on failures. This provides a head-start on troubleshooting and is far more comfortable than getting the uncomfortable question from somebody important: “Where is my report?”.
Permissions
Reporting Services manages granular permissions for each object in the catalog, such as reports, folders, or data sources. For a large report server, this can be an exceptionally long list of who has access to what – and which specific permissions are granted. The predefined roles have specific actions that are granted by each. The details of these roles are beyond the scope of this article, but a link to Microsoft’s documentation on them is provided after the conclusion as a reference.
The following query provides a complete list of all permissions assigned to any user for any object in SSRS:
SELECT
CASE
WHEN catalog.Type = 1 THEN 'Folder (1)'
WHEN catalog.Type = 2 THEN 'Report (2)'
WHEN catalog.Type = 3 THEN 'File (3)'
WHEN catalog.Type = 4 THEN 'Linked Report (4)'
WHEN catalog.Type = 5 THEN 'Data Source (5)'
WHEN catalog.Type = 6 THEN 'Report Model (6)'
WHEN catalog.Type = 7 THEN 'Report Part (7)'
WHEN catalog.Type = 8 THEN 'Shared Data Set (8)'
WHEN catalog.Type = 9 THEN 'Report Part (9)'
WHEN catalog.Type = 11 THEN 'KPI (11)'
WHEN catalog.Type = 12 THEN 'Mobile Report Folder (12)'
WHEN catalog.Type = 13 THEN 'PowerBI Desktop Document (13)'
END AS Item_Type,
catalog.Path,
catalog.Name,
users.UserName,
roles.RoleName,
roles.Description
FROM ReportServer.dbo.users
INNER JOIN ReportServer.dbo.policyuserrole
ON users.userid = policyuserrole.userid
INNER JOIN ReportServer.dbo.roles
ON roles.roleid = policyuserrole.roleid
INNER JOIN ReportServer.dbo.catalog
ON catalog.policyid = policyuserrole.policyid
ORDER BY catalog.type, catalog.name, users.username;
The results show an extensive list of who has access to what:
If this list is too long, then it can be pared down to specific objects, users, or types of objects. The table dbo.Roles contains all possible roles that may be assigned to a user in SSRS. The table dbo.Users contains a row per user, which will include some internal users, as well as any added by an administrator. dbo.policyuserrole links users to permissions and catalog items
And more!
There are more tables available in the ReportServer database that can be interrogated to learn about how SSRS is configured and used. For example, favorites are stored in dbo.Favorites and provide a simple linking table between catalog items and users. The following query returns a basic list with this information:
SELECT
Catalog.Path,
Catalog.Name,
Users.UserName
FROM dbo.Favorites
INNER JOIN dbo.Users
ON Users.UserID = Favorites.UserID
INNER JOIN dbo.Catalog
ON Catalog.ItemID = Favorites.ItemID;
The results (all of one row) are as follows:
While it could be interesting to explore every table in the ReportServer database, there is more value here in discussing how and why SSRS data could be changed.
Modifying ReportServer Data
Before discussing how to make changes to ReportServer data, I will iterate again the critical warning from earlier: This is undocumented and not supported by Microsoft. Please back up the ReportServer database before making any changes and be sure to thoroughly QA any changes to ensure they had the intended effect.
With that stark warning out of the way, the first question to answer is: “Why would we even want to do this?” The simplest answer is that SSRS provides no UI for mass-editing entities. If an employee leaves the organization or if there are any significant changes to reporting structures, having to manually update tens, hundreds, or even thousands of entries via the SSRS UI is a recipe for insanity. While a report or subscription that belongs to a disabled user does not afford that user any special access (as they are disabled), most organizations do not want to leave terminated employee accounts associated with anything, as a matter of course.
The primary reason why anyone would issue an UPDATE, INSERT, or DELETE operation against any table in the ReportServer database is to avoid the need to spend hours clicking within the confines of the SSRS web portal. Consider the simple scenario of an administrator leaving an organization. They were configured as the owner on some reports. Without knowing up-front which reports they owned, there is both a challenge of identifying them and then updating them. The following steps can be taken to identify these subscriptions and then update them:
Identify the user in question:
SELECT
*
FROM dbo.Users
WHERE UserName = 'PIKACHU\epollack';
The results return a single row that identifies a user:
With the user in question identified, subscriptions can be searched specifically for that user as the owner:
SELECT
*
FROM dbo.Subscriptions
INNER JOIN dbo.Catalog
ON Catalog.ItemID = Subscriptions.Report_OID
AND Catalog.Type = 2
INNER JOIN dbo.Users
ON Subscriptions.OwnerID = Users.UserID
WHERE Subscriptions.OwnerID = '70B6C1EE-89EF-46B1-ABD5-7AA345CAB4BC';
This returns two rows. From here, if they appear valid, they can then be updated like this:
UPDATE Subscriptions
SET OwnerID = 'F20FC133-9E68-4A6E-938A-B891E8C5020D'
FROM dbo.Subscriptions
INNER JOIN dbo.Catalog
ON Catalog.ItemID = Subscriptions.Report_OID
AND Catalog.Type = 2
INNER JOIN dbo.Users
ON Subscriptions.OwnerID = Users.UserID
WHERE Subscriptions.OwnerID = '70B6C1EE-89EF-46B1-ABD5-7AA345CAB4BC';
Subqueries could be used to reduce the number of queries needed to locate the correct OwnerID values, but the basic principle remains the same. Unlike the system tables in most places in SQL Server, these can be freely modified with few restrictions. While it is possible to enter the wrong value for a column, foreign keys do provide quite a bit of relational integrity within this database. Most tables are keyed to parent tables and would prevent an administrator from entering nonsense values for columns that contain IDs, such as UserID, ItemID, or RoleID.
Similarly, if there were a set of old reports that are no longer needed, it is possible to delete them via the UI, but this would become cumbersome if there were a large number of reports. An easier solution is to delete them via a query:
DELETE Catalog
FROM dbo.Catalog
WHERE Catalog.Path LIKE '/Dev and QA%';
This would delete any catalog items with a specific root path. Executing this DELETE statement results in a foreign key violation as a favorite exists for one of the reports. Therefore, it is necessary to delete the favorites first:
DELETE Favorites
FROM dbo.Catalog
INNER JOIN dbo.Favorites
ON Favorites.ItemID = Catalog.ItemID
WHERE Catalog.Path LIKE '/Dev and QA%';
Once complete, the remaining catalog items can be deleted. Note that in a different database with different report data, there may be more dependencies to deal with before a report, folder, or data source can be deleted. Generally speaking, deleting data directly from the ReportServer database is most efficient when done en masse. It is unlikely to save time if there is only one report to remove. For that scenario, deleting it via the UI is simpler and would likely be faster.
Some other common examples of scenarios when modifying data in the ReportServer database can be a big time-saver:
- A manager has left the organization, and their subscriptions should be redirected to a new user.
- Report builder permissions need to be revoked for a large set of reports for a set of users.
- Report descriptions on all reports need to be appended with some security-related statements.
- The execution log is quite large and requires regular archival to prevent it from becoming too bloated.
Many other use-cases exist, but these highlight a handful of reasons that an administrator may wish to directly modify data within the ReportServer database.
Insights from the SSRS Database
The entirety of a SQL Server Reporting Services server is encapsulated within the ReportServer database. It can be freely queried, alerted on, and metrics crunched on how SSRS is being used. Tables within this database can also be modified when organizational needs arise that would otherwise be time-consuming or error-prone to do manually.
While the contents of this database are mostly not documented by Microsoft, we can (with a healthy dose of caution) use it to improve SSRS processes by reporting on failed reports, mass-applying security policies, making changes to commonly modified fields, or otherwise preventing the need to perform time-consuming tasks regularly by hand.
This article should serve as a diving board for learning more about how SQL Server Reporting Services operates, and then, using that expanded knowledge, to improve the maintainability of data sources, reports, users, and other commonly referenced entities within SSRS.
References
The following are some references that may assist in researching and using data in the ReportServer database:
Enumerations for the UserType and LoginType within the Users table:
UserType Enum (Microsoft.SqlServer.Management.Smo) | Microsoft Docs
LoginType Enum (Microsoft.SqlServer.Management.Smo) | Microsoft Docs
Details about predefined roles in SSRS:
Role definitions – predefined roles – SQL Server Reporting Services (SSRS) | Microsoft Docs
The post Insights from the SSRS database appeared first on Simple Talk.
from Simple Talk https://ift.tt/75sZOht
via
