When you want to create a custom report in System Center Configuration Manager 2007 you can use existing objects in SQL SCMM database or you can create? new SQL objects using SQL Server Management Studio.

1. Create a new SQL object

• Open SQL Server Management Studio
• Use Object Explorer: [Server name] > Databases > [SCCM Database] > (click the right mouse button) New Query
• Create your new object by using SQL query (for example view, function)

2. Add permission for created object

SQL Function:

• Use Object Explorer: [Server name] > Databases > [SCCM Database] > Programmability > Functions > [Your function] > (click the right mouse button) > Properties
• Select Permissions and click the Add… button
• Add the smsschm_users role (Database role) with permissions: Execute (Grant)
• Add the webreport_approle role (Application role) with permissions: Execute (Grant)

SQL View:

• Use Object Explorer: [Server name] > Databases > [SCCM Database] > Programmability > Functions > [Your function] > (click the right mouse button) > Properties
• Select Permissions and click the Add… button
• Add the smsschm_users role (Database role) with permissions: Select (Grant)
• Add the webreport_approle role (Application role) with permissions: Select (Grant)

Useful links:

• Documentation for Microsoft SQL Server 2005 “SQL Server 2005 Books Online”: http://go.microsoft.com/fwlink/?LinkId=60544