SQL Synonym and CM12 Bug
In September I posted a blog post about SCCM 2012 SQL Views. There are a lot of cool things that I like about CM12 and using synonyms within SQL is one of them. What is particularly cool about using SQL synonym is how it allows for backwards compatibility with CM07.
Here’s Some Background Information
With the release of CM12, the team at Enhansoft was working day and night to ensure that everything was perfect with both CM12 and CM07. While testing CM12 to ensure full compatibility, once the SDK was finally released, we encountered some fun stuff with “sms_def.mof” edits. (Yes, I know there isn’t sms_def.mof in CM12 but what else do you call it???)
Within the CM12 SDK there is no guidance about how to add new classes to hardware inventory. (To help out the community we will post our best practices in a future blog post.)
Anyway, after defining editing standards for hardware inventory classes (sms_def.mof and configuration.mof edits) we found that CM12 was using SQL synonyms.
Discovering this helped save us a TON of work as it meant that we didn’t have to re-write all of our reports. This also allowed us to discover a few cool tricks too with compatibility between CM07 and CM12 and writing reports with SSRS.
So why am I telling you all this? Without going into too much detail, the simple answer is we found a bug within CM12! It drove me nuts for a while until finally we worked it through and found the issue.
CM12 does NOT set the correct permissions on synonyms. You can see this by viewing the permissions on any synonym. Take this example from our SQL reporting collection. Notice that there are no permissions set on the synonym.
Luckily there is a very simple fix for this problem. Add Grant Select permission to smsschm_users on the synonym and this will fix all the issues we found while using SQL synonyms and it will ensure backwards compatibility with CM07! It doesn’t get any simpler than that.
You can do it manually or via SQL. Within SQL, use the following command:
GRANT SELECT ON [dbo].[v_GS_ES_SQL080] TO [smsschm_users]
I have filed a bug complaint with Microsoft on this issue, but I wouldn’t hold my breath that Microsoft will fix it.