ConfigMgr
What Are the Supported SQL Server Views to Use with SCCM Reporting?
Topics: ConfigMgr
Have you ever wondered what SQL Server views you can use with SCCM current branch reporting? You wouldn’t be alone in asking this question because the views are not clearly documented. I’m here to tell you that the answer is quite simple. The only supported SQL Server views are the ones which have SQL Server smsschm_users security rights. Those are the only supported views to be used by reporting regardless of whether it’s Power BI or SQL Server Reporting Services (SSRS). This is also true for table-value functions that are used with the Role-Based Administration (RBA) feature. The only supported table-value functions are the ones which have SQL Server smsschm_users security rights.
Unsupported SQL Server Views
I’m seeing a lot of unsupported SQL Server views floating around these days. They appear in forum post answers and in blog posts. In the case of some recent blog posts, I’ve read where some folks are telling others to use a whole host of unsupported views, tables, stored procedures, functions, etc.
If that wasn’t bad enough, in several cases where a problem occurs after one of these unsupported SQL Server views is put into production, the advice is to change the SCCM database rights. This is definitely not supported by Microsoft. In these scenarios, changing database rights appears to be the only way to solve problems, such as access denied or unavailable details, when running reports using unsupported SQL Server views or table-value functions.
Supported SQL Server Views
This SQL Server query shows what views and table-value functions are supported. By the way, there are about 1500 supported objects in my lab. These views and table-value functions are the only SQL Server objects you should use within SCCM reporting.
SELECT Distinct
Case So.type
When ‘V’ then ‘View’
When ‘U’ then ‘Table’
When ‘FN’ then ‘SQL scalar function’
When ‘IF’ then ‘Table Function (RBA)’
When ‘TF’ then ‘Table Function (RBA??)’
When ‘P’ Then ‘SQL Stored Procedure’
When ‘SQ’ then ‘Service queue’
When ‘FS’ then ‘Assembly (CLR) scalar-function’
When ‘S’ then ‘System base table’
When ‘FT’ then ‘Assembly (CLR) table-valued function’
Else so.type
end as ‘Object type’,
CASE
WHEN SO.name like ‘v[_]RA[_]%’ THEN ‘Resource Array’
WHEN SO.name like ‘v[_]R[_]%’ THEN ‘Resource’
WHEN SO.name like ‘v[_]HS[_]%’ THEN ‘Inventory History’
WHEN SO.name like ‘v[_]GS[_]%’ THEN ‘Inventory’
WHEN SO.name like ‘v[_]CM[_]%’ THEN ‘Collection’
WHEN SO.name like ‘%Summ%’ THEN ‘Status Summarizer’
WHEN SO.name like ‘%Stat%’ THEN ‘Status’
WHEN SO.name like ‘%Permission%’ THEN ‘Security’
WHEN SO.name like ‘%Secured%’ THEN ‘Security’
WHEN SO.name like ‘%Map%’ THEN ‘Schema’
WHEN SO.name = ‘v_SchemaViews’ THEN ‘Schema’
ELSE ‘Other’
END As ‘Type’,
SO.name As ‘ViewName’
FROM
–Role/member associations
sys.database_role_members members
JOIN sys.database_principals roleprinc ON roleprinc.principal_id = members.role_principal_id
–Roles
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = roleprinc.principal_id
–Permissions
LEFT JOIN sys.columns col on col.object_id = perm.major_id AND col.column_id = perm.minor_id
–Table columns
LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
Left join sysobjects so on perm.major_id = SO.id
WHERE
— SO.name like ‘v_ApplicationAssignment’
— and
So.type in (‘IF’,’V’)
and SO.name not like ‘v_CM_RES_COLL%’
and SO.name not like ‘fn_RBAC_CM_RES_COLL%’
and roleprinc.name = ‘smsschm_users’
order by
1,
SO.name
If you have any questions about what supported SQL Server views or table-value functions to use with SCCM current branch reporting, please feel free to contact me at @GarthMJ.
