Where is user security and scheduled refresh data stored in the report server database?

[4Eric]

Would like to create a report to show when all the reports on our Report server are scheduled to refresh.  If this is possible where is the information stored.

Also would like to report on what AD group the user belongs to is that stored anywhere on the Report server?

[BhaveshPatel · Accepted solution]

This would help you out.

SELECT
	e.name AS ReportName
	,e.path AS ReportPath
	,d.description as SubscriptionName
	, a.SubscriptionID
	, d.laststatus
	, d.eventtype
	, d.LastRunTime
	,j.date_created AS ScheduleCreatedDate
	,j.date_modified AS ScheduleModifiedDate


	,CASE j.[enabled]
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS Job_Enabled
	,CASE sch.[enabled]
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS Schedule_enabled,

	CASE sch.freq_type
		WHEN 1 THEN 'Once'
		WHEN 4 THEN 'Daily'
		WHEN 8 THEN 'Weekly'
		WHEN 16 THEN 'Monthly'
		WHEN 32 THEN 'Monthly relative'
		WHEN 64 THEN 'When SQLServer Agent starts'
	END AS Frequency, 
	CASE sch.active_start_date
		WHEN 0 THEN null
		ELSE
		substring(convert(varchar(15),sch.active_start_date),1,4) + '-' + 
		substring(convert(varchar(15),sch.active_start_date),5,2) + '-' + 
		substring(convert(varchar(15),sch.active_start_date),7,2)
	END AS ScheduleStartDate,
	CASE len(sch.active_start_time)
		WHEN 3 THEN cast('00:0' 
				+ Left(right(sch.active_start_time,3),1)  
				+':' + right(sch.active_start_time,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(sch.active_start_time,4),2)  
				+':' + right(sch.active_start_time,2) as char (8))
		WHEN 5 THEN cast('0' 
				+ Left(right(sch.active_start_time,5),1) 
				+':' + Left(right(sch.active_start_time,4),2)  
				+':' + right(sch.active_start_time,2) as char (8))
		WHEN 6 THEN cast(Left(right(sch.active_start_time,6),2) 
				+':' + Left(right(sch.active_start_time,4),2)  
				+':' + right(sch.active_start_time,2) as char (8))
	END AS ScheduleStartTime

	
	,CASE jsch.next_run_date
		WHEN 0 THEN null
		ELSE
		substring(convert(varchar(15),jsch.next_run_date),1,4) + '-' + 
		substring(convert(varchar(15),jsch.next_run_date),5,2) + '-' + 
		substring(convert(varchar(15),jsch.next_run_date),7,2)
	END AS nextRunDate,
	CASE len(jsch.next_run_time)
		WHEN 3 THEN cast('00:0' 
				+ Left(right(jsch.next_run_time,3),1)  
				+':' + right(jsch.next_run_time,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(jsch.next_run_time,4),2)  
				+':' + right(jsch.next_run_time,2) as char (8))
		WHEN 5 THEN cast('0' 
				+ Left(right(jsch.next_run_time,5),1) 
				+':' + Left(right(jsch.next_run_time,4),2)  
				+':' + right(jsch.next_run_time,2) as char (8))
		WHEN 6 THEN cast(Left(right(jsch.next_run_time,6),2) 
				+':' + Left(right(jsch.next_run_time,4),2)  
				+':' + right(jsch.next_run_time,2) as char (8))
	END AS NextRunTime
FROM ReportServer.dbo.ReportSchedule a 
        LEFT OUTER JOIN ReportServer.dbo.ReportSchedule c
            ON a.ScheduleID = c.ScheduleID
        LEFT OUTER JOIN ReportServer.dbo.Subscriptions d
            ON c.SubscriptionID = d.SubscriptionID
        LEFT OUTER JOIN ReportServer.dbo.[Catalog] e
            ON d.report_oid = e.itemid
		LEFT OUTER JOIN msdb.dbo.sysjobs j
			ON CAST(a.ScheduleID AS VARCHAR(100)) = j.name
		LEFT OUTER JOIN msdb.dbo.sysjobschedules jsch
			ON j.job_id = jsch.job_id
		LEFT OUTER JOIN msdb.dbo.sysschedules sch
			ON sch.schedule_id = jsch.schedule_id

-- to find specific report last status 
-- WHERE e.name = 'Usage Stats'

-- to find failed status
--WHERE 
--		LastStatus <> 'Completed Data Refresh'

-- or for a specific SQL Agent Job
--WHERE 
--b.name = '9B7BED26-A81C-479C-A23B-016C9E05E760'
-- NOTE you have to look in subscription history for any error messages/details and decode the JSON response (good luck with that!)

Check the below blog as well.

https://workingondata.wordpress.com/2017/11/02/walkthrough-scheduled-data-refresh-in-power-bi-report...

[4Eric]

Thank you so much!  now I just have to figure out the security part.  Really all I want is what AD group they are assigned to or used to login in.