The case for adding SLUserID to every SSRS report you build

If you build SSRS reports for your company that are available in Web Apps (or even just on your Report Server) I recommend taking a page from Microsoft and adding an SLUserID parameter. Other than usage for potential security, this will give you a way to see which of your users are running reports in Web Apps. That can help drive development to certain reports over others and / or act as an audit trail.

To add the SLUserID to one of your reports, create a shared dataset in your Visual Studio solution named SLUserID.  The Data source should be your SYS database.  Have it execute the stored procedure UserRec_User_fromWindowsUser_Default

This is a standard procedure from Microsoft, so you should have it.  

Click on the Parameters tab and assign [&UserID] as the Default Value for @parm1 and select Internal (read-only)

Add a dataset to your SSRS report, again named SLUserID and point it toward this Shared Dataset.  

Now create a Report Parameter named SLUserID.  Toggle the visibility to hidden and then set the default values (below) to use your report dataset. 

When deploying the report to your report server, you may have to click ‘Manage’ and point the report toward your shared dataset on the report server.  This should only have to be done once per report. 

Now, whenever a report is run, the UserID will be included in the parameters of the report.  Why is this important?  Web Apps typically runs the reports under a Trusted Web Service Account.  So if you query your ReportServer database, you can’t tell which user ran the report; they are all listed as the Trusted Account.    

By placing the User into the parameter, we can pluck that value out, join to the UserRec table and then the PJEmploy table. 

You can find your Trusted Web Service Account in screen 95.261.00

The script below will show you the report name, execution time, parameters, the SLUserID (that we extract from the parameters) and then the employee name.  

You have some ‘To-do’s here…

  1. Get the value of your Trusted Web Service. Place it into the query were you see YOUR_TRUSTED_ACCOUNT_FROM_SL
  2. Enter your domain where you see YOUR_DOMAIN
  3. Enter your System database where you see SYS_DATABASE
  4. Enter your Application database where you see APP_DATABASE
use ReportServer;
go

;with cte as (

select	c.name, 
		l.timeend,  
		l.Parameters, 
		case	when l.username = 'YOUR_TRUSTED_ACCOUNT_FROM_SL' 
				then case	when charindex('SLUserID',l.parameters) = 0 
							then '' 
							else substring(l.parameters,charindex('SLUserID',l.parameters, 0)+9, charindex('%',l.parameters,charindex('SLUserID',l.parameters,0))-charindex('SLUserID',l.parameters, 0)-9)
					  end 
				else Replace(UPPER(l.username),'YOUR_DOMAIN\','')  
		end as userid
from ExecutionLogStorage l
join catalog c on c.ItemID = l.reportid 
	where l.timeend > getdate()-2  --how many days do you want to go back? 
	  and c.name not in ('SLUserID','AvailableSLUserIDs','LastWEDate')
)

select c.name, c.timeend, c.parameters, c.userid, replace(e.emp_name,'~',', ') as emp_name
 from cte c
  left join SYS_DATABASE..userrec r on r.userid COLLATE DATABASE_DEFAULT = c.userid COLLATE DATABASE_DEFAULT
  left join APP_DATABASE..pjemploy e on e.user_id COLLATE DATABASE_DEFAULT = r.userid COLLATE DATABASE_DEFAULT
order by timeend desc 

One quick note…the ExecutionLogStorage has a 60 day ‘expiration’ which means it deletes rows older than 60 days.  You can extend this if you want by updating the ConfigurationInfo table in your ReportServer database.  

select * from ConfigurationInfo where name = 'ExecutionLogDaysKept'
update ConfigurationInfo set Value = 'days_to_keep' where name = 'ExecutionLogDaysKept'

Feel free to share...

Leave a Reply

Close Menu