SSRS ReportServer Database Internals

SQL Server creates two Reporting databases when reporting services installed on report server/machine. user should connect the server using database engine services.

Under database there will be two databases that are used by reporting services.

1) ReportServer

2)ReportServerTempDB

Report server is the main database and contain all information about reports,subscriptions,schedules,report data source ,report snapshots, report history, roles,data source and data sets.

The tables shown above are created by default. These all tables are useful to identify the all the report service details.

I will illustrate few tables and its usage.

 dbo.Subscriptions : 

When user subscribe a report one row will be added to the table. This table used for general subscriptions as well as data driven subscriptions.

It stores information about last status (when the report ran and its status (fail/passed) and stores error if any. It also stores report parameters (input/output),Event type ,Delivery type(email,windows share).

dbo.Catalog : 

This table stores the information report server folder path (include folder hierarchy).  

dbo.DataSource :

This table store the information related to SSRS Data source. It contains connection strings,username ,password (encrypted) etc.

dbo.ExecutionLogStorage :

When ever a report executed in the report server a row will be inserted into the report server. Its stores reportid,timestart ,timeend,status ,rowcount and username etc.

dbo.Roles : 

Roles table stores the predefined roles as well as custom roles. below are the default role stored in roles tables. if any new custom role created it will add row in a table.

• Browser
• Content Manager
• Model Item Browser
• My Reports
• Publisher
• Report Builder
• System Administrator
• System User

dbo.users :

user table is used to store the system users to connect the report server database.

There are system view also available that is very useful for error information.

The ExecutionLog views is used for getting report execution information. Its also returns the execution status and additional information include system default values.

There are plenty system stored procedures available in SSRS used for creating subscriptions,snapshots,schedules etc. using the system stored procedures SSRS can be customized through pro-grammatically.

There is additional database role available in report server apart from the predefined fixed server roles.

RSEXECRole : 

This Role is used for executing the reports in SSRS Server. User must have associated with this role if reports needs to execute in the SSRS  report manager.