To create a SQL statement using Query Designer
1.
In Microsoft SQL Server Management Studio, navigate to Databases \ SMS_SiteCode \ Views.
2.
Find and right-click the v_SecuredObject view, and then click Open View. The records for the view are displayed in the Results pane.
3.
Click the Query Designer menu item, highlight Pane, and then click Diagram.
4.
Click the Query Designer menu item, highlight Pane, and then click Criteria.
5.
Click the Query Designer menu item, highlight Pane, and then click SQL.
The view objects are displayed in the Criteria pane, the SQL statement is displayed in the SQL pane, and the results are listed in the Results pane.
6.
In the Criteria pane, right-click the row with the asterisk (*), and then click Delete. The asterisk specifies that all columns for the view should display in the query results.
7.
In the Diagram pane, select the ObjectName check box in v_SecuredObjects.
8.
Right-click anywhere in the Diagram pane, and click Add Table to open the Add Table dialog box.
9.
Click the Views tab, scroll down and click v_UserClassPermNames, and then click Add.
10.
In the Add Table dialog box, click Close.
11.
In the Diagram pane, highlight the ObjectKey column in v_SecuredObject and then drag it to the ObjectKey column in v_UserClassPermNames. This sets up an INNER JOIN between the two views by using the ObjectKey column.
12.
In the Diagram pane, click the UserName and PermissionName check boxes in v_UserClassPermNames.
13.
In the Criteria pane, type Object in the Alias column for the ObjectName row.
14.
In the Criteria pane, type User Name in the Alias column for the UserName row.
15.
In the Criteria pane, type Object Permission in the Alias column for the PermissionName row.
16.
In the Criteria pane, click the Sort Order column, and then type 1 or select 1 from the drop-down list, for the ObjectName row. This sorts the results by the object name in ascending order.
17.
In the Criteria pane, click the Sort Order column and then type 2, or select 2 from the drop-down list, for the UserName row. This creates a secondary sort order where the data is displayed in ascending order by object name and then user name.
18.
Run the query by clicking the Execute icon (the red exclamation point). All Configuration Manager objects should display with the associated permission name and user account.
The query results are now how Hailey wants them, but she still needs to filter on the user’s name without the domain name. She can accomplish this by adding the v_R_User view to the SQL statement, which contains both the domain\username and username values. She completes the query by performing the following steps.
To filter the query on user name
1.
Right-click anywhere in the Diagram pane, click Add Table to open the Add Table dialog box, click the Views tab, scroll down and click the v_R_User view, and then click Add. Click Close in the Add Table dialog box.
2.
Highlight the UserName column in v_UserClassPermNames, and then drag it to the Unique_User_Name0 column in v_R_User. This will set up an INNER JOIN between the two views by using the UserName and Unique_User_Name0 columns.
3.
Select the User_Name0 check box in v_R_User. The column will appear in the Criteria pane.
4.
In the Criteria pane, click the Output column in the User_Name0 row. This will clear the check box so that the User_Name0 column will not display in the query results.
5.
In the Criteria pane, click the Filter column in the User_Name0 row and type LIKE Administrator. This filters the query results so that objects only for the Administrator account will be displayed.
The User_Name0 column from v_R_User is what will be used as the report prompt variable, but because variables are not supported in Query Designer, Hailey adds Administrator for the user name to test the query.
The following SQL statement displays in the SQL pane:
SELECT v_SecuredObject.ObjectName AS Object, v_UserClassPermNames.UserName AS [User Name],
v_UserClassPermNames.PermissionName AS [Object Permission]
FROM v_SecuredObject INNER JOIN
v_UserClassPermNames ON v_SecuredObject.ObjectKey = v_UserClassPermNames.ObjectKey INNER JOIN
v_R_User ON v_UserClassPermNames.UserName = v_R_User.Unique_User_Name0
WHERE (v_R_User.User_Name0 LIKE 'Administrator')
ORDER BY Object, [User Name]
6.
Hailey runs the query to make sure it returns the expected results.
The query returns the results that Hailey needs. She is now ready to create a new Configuration Manager report by using the SQL statement that she has created. She performs the following steps in the Configuration Manager console to create the new report.
To create a new Configuration Manager report
1.
Open the Configuration Manager console.
2.
Navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reports.
3.
Right-click the Reports node, point to New, click Report, and in the New Report Wizard, type User Class Security Permissions for SMS Objects in the Name text box.
4.
Because there isn’t a report category that fits this report, create a custom category by typing Security in the Category text box.
5.
Type This report will show the class security permissions for all Configuration Manager objects for a specific user in the Comment text box.
6.
Click Edit SQL Statement to open the Report SQL Statement dialog box. A default SQL query statement populates the SQL statement text box.
7.
Copy the SQL statement from the SQL pane in Query Designer, and paste it into the SQL statement text box, replacing the default SQL statement.
8.
Change the static user name value to a variable by replacing LIKE 'Administrator' with @User. The condition line in the SQL statement is now WHERE (v_R_User.User_Name0 = @User).
9.
Click Prompts, and click the New Prompt icon (the yellow asterisk) to create a new report prompt.
10.
Type User in the Name text box (where User is the name of the variable defined in the SQL statement: @User), type User Name in the Prompt text text box, select the Allow an empty value check box, and then leave the Provide a SQL statement check box clear. Adding a SQL statement for the report prompt allows you to use wildcards when entering the report prompt value.
11.
Click OK to close the Prompt Properties dialog box, click OK to close the Prompts dialog box, and then click Next on the General page.
12.
Click Next on the Display page, click Next on the Links page, click Next on the Security page, and then click Close on the Confirmation page to complete the wizard.
Hailey now has the report that she needs to check Configuration Manager object class permissions for specific users. She can later choose to add a link to another report, but for now, this report achieves her primary objective.
details are seen on this url:
http://technet.microsoft.com/en-us/library/dd334679.aspx