PowerShell Learning – SSRS Data Source Mystery – Part 3 of 42 min read

Here is a continuation blog on things I learned to do with PowerShell to fix an issue in my day-to-day task list.  This one may not be daily, but I think it might help someone out there.

Previous Posts:

Part 1 of 4

Part 2 of 4


A couple weeks ago we had a group of users ask for help with reports that they could no longer run.

What?! These reports have been running for months without any issues and now there is a problem?! Well, I logged in to SSRS and proceeded to the report(s).  And…sure enough the report had an error loading the data source.

Let’s check the data source!!

Well, wouldn’t you know it; there is a username of an individual who is no longer with the company and that account has now been disabled as per security policy.

This is not a standard practice for our data sources so it was an anomaly that has occurred.

After fixing it, I wondered if we have any other “anomalies” that might be sneaking around our SSRS environment?

Off to Google I go to see how easy or not it is to find all the usernames being used by SSRS data sources.

Ha!!! Not so easy as I would think it would have been.  I found a possibility that uses the built in RS.exe and some C# code.


Reference to RS.exe example: SQL Server Central Forum

But then I thought PowerShell has been a life saver for other DBA related tasks, so I turned to that tool and my thinking was again this should be easy.

After asking on Slack and getting a couple people that had advise, Friedrich Weinmann (bt) threw out some code that I still am in awww of and not 100% sure what it does.

That code was much cleaner than the 30 lines I had started hacking together.

So now I have the ability to do an audit on all Data Sources on any SSRS server in my environment.


Off to the next one…


You may have a different URI depending on your SSRS version.  See link for more detail.


One Reply to “PowerShell Learning – SSRS Data Source Mystery – Part 3 of 42 min read

Leave a Reply

Your email address will not be published. Required fields are marked *