Is there anyway to change the standard SQL SERVER reports? And/OR See the SQL Behind it?

SQL SERVER (through Management Studio) offers quite a few standard reports, but I don’t see how you could change the reports. I don’t even see an option for extracting/obtaining the SQL Code behind it.

Is there any way to achieve this? Specifically, I am more interested in security reports (although it’d be nice to get code for all of them).

Thank you in advance

Yes, we can!

What you will need is a .NET decompiler such as Telerik JustDecompile. (Any other decompiler will do, probably, but this is what I use)

Once that (or another decompiler) is installed, all you need to do is locate the assembly Microsoft.SqlServer.Management.Reports.dll on your client (usually located in C:Program Files (x86)Microsoft SQL Server120ToolsBinnManagementStudioExtensionsApplication).

Once you have found the assembly, you right click on it and choose “Open with JustDecompile”.

Now, within JustDecompile, you can expand the tree view on the left until you see the “Resources” available, most of which are the “Standard Reports”.

Now, you can right click on any report and choose “Save”.

You now have a copy of a standard report to inspect, copy, modify at your leisure.

Note: You can rename the report to .RDL so that Report Builder recognises it if you want, that’s fine.

Yes, run a Profiler trace while you run the report. It’s just plain old T-SQL.

(Source: that’s how I wrote the Performance Dashboard Reports chapter in the book SQL Server 2008 Internals and Troubleshooting.)