ConfigMgr 2012 – Identify Custom SSRS Reports

Working through an upgrade / migration on a client site and needed to be able to identify custom reports created within the ConfigMgr 2012 reporting structure.

The following query will provide that information, change the DOMAIN below to match your domain name. Run from SQL Server Management Studio.

  FROM [ReportServer].[dbo].[Catalog] c
  INNER JOIN [ReportServer].[dbo].[users] u 
    ON u.UserID = c.ModifiedByID
  where UserName LIKE 'DOMAIN%'

Posted in SQL Server, SSRS, ConfigMgr, SQL | Leave a comment

SQL Server 2012 – Setup account privileges

Encountered a strange issue today, installing SQL Server 2012 on Windows Server 2012 and received the following condition during the Setup Support Roles.

Setup Account Privileges – Failed.


That’s weird, I’m an Administrator on the server (of course :). Checked the status link for more detail, received this dialog.


This gave me the clue that it was likely a local policy issue.

Found and applied the advice in this article:

SQL Server installation fails if the Setup account does not have certain user rights

From <>

Done, installation completed successfully.

Posted in SQL Server 2012, Windows Server 2012 | 1 Comment

Congratulations 2015 Microsoft MVP


Another year, another honor. Thank you Microsoft, and thanks to the community!


Dear Steve Thompson,

Congratulations! We are pleased to present you with the 2015 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in Enterprise Client Management technical communities during the past year.

The Microsoft MVP Award provides us the unique opportunity to celebrate and honor your significant contributions and say "Thank you for your technical leadership."

Mike Hickman
Community Engagement

Posted in ConfigMgr, Microsoft, MVP | Leave a comment

System Center 2012 Configuration Manager: A Practical Handbook for Reporting

Since I’ve had a number of people ask where they can find it, this book is now available on Amazon. Thank you for your support!

SSRS - Cover

Print only at this point, Kindle version should be available in a week or so.

From the back cover:

This book is for IT professionals who need to take their reporting to the next level. The focus of this guide is a practical approach to modify, create, and build reports within SQL Server Reporting Services (SSRS). Although intended for Configuration Manager 2012 Administrators, there are many concepts in this practical handbook that provide value for anyone who requires additional knowledge of SSRS. The reporting principles also apply to other versions of Configuration Manager. In addition to well-proven step-by-step guides, you also get access to sample scripts and source code, allowing you to quickly test the solutions in your own environment.

Discover how to

– Setup ConfigMgr 2012 R2 for SSRS

– How to locate information in the Configuration Manager Database

– Compare and contrast Report Authoring Tools

– Select the right Report Authoring Tool

– Customize existing reports with Report Builder

– Create a Report with SQL Server Data Tools

– Modify a Report with SQL Server Data Tools

– Create Advanced reporting solutions

– Custom reporting on extended hardware inventory

– Techniques to simplify report data display

– Create Linked reports

– Report on databases other than Configuration Manager

– Deploy a solution that tracks and reports on historical client data

– Implement advanced index and statistic optimization

– Create and manage report subscriptions

Posted in SSRS, ConfigMgr, SQL | Leave a comment

Practical Handbook for Configuration Manager 2012 R2 Reporting


This project has been in the works for a few months, very proud of this work. More details to follow.

Posted in ConfigMgr, SQL Server, SQL Server 2012, SQL Server 2014, SSRS | 10 Comments

Change SQL Server default backup compression

The default backup mode for SQL Server databases is no compression.

When creating maintenance plans, you’d need to select enable database backup compression for each plan.

In order to change the server default setting, use the following:

— View current setting. If result is 0, then compression is not enabled.

SELECT value 
FROM sys.configurations 
WHERE name = 'backup compression default' ;

— To change SQL Server default setting, run the following:

EXEC sp_configure 'backup compression default', 1 ;

Posted in SQL, SQL Server, SQL Server 2012, SQL Server 2014 | Leave a comment

ConfigMgr – List all Servers with Maintenance Windows

Developed a report to show all servers that have collection maintenance windows. Very handy for cross checking servers in multiple maintenance windows, or supplying a list to support change control. Here is the query:

 -- List Computers with Maintenance Window
SELECT  sw.Name AS [MW Name], sw.Description, 
    sw.Duration AS 'Duration Minutes',
    sw.IsEnabled AS 'MW Enabled', 
    sys.Name0 AS ServerName, 
    sys.Operating_System_Name_and0 AS OperatingSystem
FROM  dbo.v_ServiceWindow AS sw INNER JOIN 
      dbo.v_FullCollectionMembership AS fcm ON sw.CollectionID = fcm.CollectionID 
INNER JOIN  dbo.v_R_System sys ON fcm.ResourceID = sys.ResourceID 
WHERE  (sys.Operating_System_Name_and0 LIKE '%server%') 
ORDER BY [MW Name], sys.Name0

Posted in Uncategorized | Leave a comment