Create a New SMS 2003 Report

The following scenario provides an example of how to create a new SMS 2003 report and modify the report's SQL statement in the SQL Server 2000 Query Design Tool.

Creating a New SMS 2003 Report

Preston is an SMS administrator who needs to create a report that will show a specific user’s class permissions for all SMS objects. Specifically, he needs a report that will prompt for a user name and then list the SMS object, the full user name (domain\user name), and the permission name. Because he has some users who have accounts in multiple domains, when prompted for the user name he wants to enter the user name, without the domain, and retrieve class permissions for that user account in any of his domains. Preston looks at the SMS 2003 predefined reports and doesn’t find one that gives him what he needs, so he decides to create a new report.

Preston first creates a query that retrieves the data that he needs. To do this, he opens the DTS Query Designer from within SQL Server 2000 Enterprise Manager. The specific steps to create queries using DTS Query Designer are outlined in the Query Design Tools section of this document. Preston looks at the Security View Reference section of this document to help him determine which views and associated columns to use when creating his query. He finds that the Security View Sample Queries section in Appendix B helps him determine which views he needs to use, and he follows these steps to create his query in the DTS Query Designer:

To create a query in DTS Query Designer

1. Right-click anywhere in the Diagram pane, click Add Table to open the Add Table dialog box, click the Views tab, scroll down, click the v_SecuredObjects and v_UserClassPermNames views while holding down the CTRL key, and then click Add. This will add both views to the pane. Alternatively, you can highlight and add each view individually.

2. In the Add Table dialog box, click Close.

3. Highlight the ObjectKey column in v_SecuredObjects and then drag it to the ObjectKey column in v_UserClassPermNames. This sets up an inner join between the two views using the ObjectKey column.

4. Click the ObjectName check box in v_SecuredObjects, click the UserName and PermissionName check boxes in v_UserClassPermNames, and then run the query by clicking the Run icon (the red exclamation point).

5. The checked columns are displayed in the Grid pane, the SQL statement is displayed in the SQL pane, and the results are listed in the Results pane.

6. To sort the results by ObjectName, click the Sort Order column of the ObjectName row in the Grid pane, and then select from the drop-down list or type 1. To do a secondary sort by UserName, click the Sort Order column of the UserName row, and then type 2 or select 2 from the drop-down list. When the query is run again, it will now be sorted in the desired sort order.

7. To simplify SQL statements by providing an alias name for each view, right-click the v_SecuredObject view, click Properties, type SO in the Alias text box, and then click Close.

8. Repeat step 7 for the v_UserClassPermNames view using UCP for the alias. The new alias names can now be seen in the Display, Grid, and SQL Query panes.

The query results are now how Preston wants them, but he still needs to filter on the user’s name without the domain name. He can accomplish this by adding the v_R_User view to the query, which contains both the "domain\username" and "username" values. He completes the query by doing the following:

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 click Add. Click Close in the Add Table dialog box.

2. Right-click the v_R_User view, click Properties, type USR in the Alias text box, and click Close.

3. 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 using the ObjectKey column.

4. Click the User_Name0 check box in v_R_User. The column will appear in the Grid pane.

5. In the Grid pane, click the Output column of the User_Name0 row. This will clear the check box so that the User_Name0 column will not display in the query results.

6. The User_Name0 column from v_R_User is what will be used as the report prompt variable. Because variables are not supported in the DTS Query Designer, click the Criteria column of the User_Name0 row and type in the user name for one of the users with SMS object class security, preceded by the LIKE operand. In this example, type LIKE administrator.

   The following query is shown in the SQL Statement pane:

   SELECT SO.ObjectName, UCP.UserName, UCP.PermissionName
   

FROM v_UserClassPermNames UCP INNER JOIN v_SecuredObject SO ON UCP.ObjectKey = SO.ObjectKey INNER JOIN v_R_User USR ON UCP.UserName = USR.Unique_User_Name0 WHERE (USR.User_Name0 LIKE 'administrator') ORDER BY SO.ObjectName, UCP.UserName

7. Run the query to make sure it returns the expected results.

The query returns the results that Preston needs. He is now ready to create a new SMS report using the SQL statement that he has created. He follows these steps to create the new report:

To create a new SMS 2003 report

1. In the SMS 2003 console tree, navigate to the Reports node, as shown in the following example:

   Systems Management Server

     + Site Database (site code - site name)

       + Reporting

         + Reports

2. Right-click the Reports node, point to New, click Report, and in the Report Properties dialog box for the new report, in the Name text box, type User Class Security Permissions for SMS Objects.

3. Because there isn’t a report category that fits this report, create a custom category by typing Security in the Category text box, and in the Comment text box, type This report will show the class security permissions for all SMS objects for a specific user.

4. Click Edit SQL Statement to open the Report SQL Statement dialog box. A default SQL query statement populates the SQL statement text box.

5. Copy the SQL statement created in the DTS Query Designer, and then paste it into the SQL statement text box, which replaces the default query statement.

6. Test the report with the static value before adding the prompt. Click OK in the Report SQL Statement dialog box, click OK in the User Class Security Permissions for SMS Objects Properties dialog box, right-click the User Class Security Permissions for SMS Objects report, point to All Tasks, point to Run, and then click the Reporting Point server name.

7. After the report returns the results that are expected for the static user name value, a report prompt needs to be created to prompt for this user name value. Right-click the User Class Security Permissions for SMS Objects report, click Properties, and then click Edit SQL Statement.

8. Change the static user name value to a variable by replacing 'administrator' with @User. The condition line in the query statement is now **WHERE (USR.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, check Allow an empty value, and then leave the Provide a SQL statement cleared. 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, click Yes to update the report prompt and close the Linked Reports Prompts dialog box, click OK to close the Report SQL Statement dialog box, and then click OK to close the User Class Security Permissions for SMS Objects Properties dialog box.

Preston now has the report that he needs to check SMS object class permissions for specific users. He can later choose to add a link to another report, but for now, this report achieves his primary objective.

Related Links

• SMS 2003 Reports