Don Hite at myITforum.com

Granting SMS Web Reports Users Access To New Or Existing Views Using SQL Scripts

This article will provide you with an example of how you can grant Select permissions to the smsschm_users and webreport_approle accounts to a new or existing SMS view programmatically using a SQL query as opposed to using the SQL Server Management Studio.

If you have created a SQL view and referenced that view in a web report and the select permissions have not been applied for the smsschm_users and webreport_approle accounts you may receive the following error when executing your web report(s):

An error occurred when the report was run. The details are as follows:

SELECT permission denied on object 'View_Name', database 'SMS_XXX', owner 'dbo'.

Error Number: -2147217911

Source: Microsoft OLE DB Provider for SQL Server

Native Error: 229

If you already have an existing view created and you want to grant Select permissions to the smsschm_users and the webreport_approle accounts execute this command from within the query window of your SMS Database after changing View_Name to the name of your existing view:

Grant Select On View_Name To smsschm_users, webreport_approle

If you have not yet created your view use this script below as a template for creating new views to use as SMS web reports. The template script will first check for the presence of the specified view and if it is found the view will be deleted or dropped. Next the template will create the specified view and then grant the appropriate Select permission to the smsschm_users and the webreport_approle accounts:

If Exists (Select * From dbo.SysObjects Where ID = Object_ID(N'[View_Name]')

and ObjectProperty(ID, N'IsView') = 1)

Drop View View_Name

Go

Create View View_Name As

<Your_Sql_Query_Here>

Go

Grant Select On View_Name To smsschm_users, webreport_approle

Go

Note: Be sure to change all four instances of View_Name to the name of your view.

Using this template on a regular basis to create your views for using in your SMS web reports will ensure that you apply the appropriate Select permissions and will allow you to consistently create your views for the SQL database.  For example you have the following simple SQL query that will associate the System_Disc tables User_Name0 column with the User_Disc tables Full_User_Name0 column that you want to use as a web report:

Select

SD.Name0,

SD.User_Name0,

UD.Full_User_Name0

From System_Disc SD

Join User_Disc UD on SD.User_Name0 = UD.User_Name0

Using the SQL template above you would simply replace the line that reads <Your_Sql_Query_Here> with the SQL query example above as shown here:

If Exists (Select * From dbo.SysObjects Where ID = Object_ID(N'[View_Name]')

and ObjectProperty(ID, N'IsView') = 1)

Drop View View_Name

Go

Create View View_Name As

Select

SD.Name0,

SD.User_Name0,

UD.Full_User_Name0

From System_Disc SD

Join User_Disc UD on SD.User_Name0 = UD.User_Name0

Go

Grant Select On View_Name To smsschm_users, webreport_approle

Go

Then you can test the view from within the SQL query window by executing the following:

Select * From View_Name

From this point on it is just a matter of creating the web report as normal.