SQL Server Roles

Depending on your corporate security policies, you may want to restrict access to the Reporter database.

As Reporter uses a Microsoft SQL Server database, you must use the security mechanisms provided by SQL Server. Permissions on SQL Server databases are managed through “roles”. To grant someone the rights associated with a particular role, you must add that user account to the role. For more information on managing roles in SQL Server, see the Microsoft SQL Server Enterprise Manager Help.

To take advantage of the predefined SQL Server roles created by Reporter, you must first determine the reporting responsibilities of your administrators and end users. Depending on the rights you want to grant, add these users to one or more of the roles.

Reporter includes the following customized SQL Server roles, each providing a different level of access to the database:

role	description
Reporter Admin	Members have full access to the Reporter database.
Stored Report Generator	Members can run report templates against stored data only. Note: Unless the default Report Wizard collection type is changed using the Configuration Utility, it may appear that members in this role can run a live report template. However, no data is stored to the database and any resulting report is based on the existing contents of the database.
Live Report Generator	Members can run report templates against live or stored data.
Reporter Data Collector	Members can only run scheduled collections. Best Practice: Create a dedicated user account for scheduled collections and add the user account to this role. Note: This role is not intended for regular interactive users of Reporter.
Report Creator	Members can create, modify, and delete report templates. Members can duplicate report templates, add and remove fields, and change groupings. If you select this role, then you must also select either the Live Report Generator role or the Stored Report Generator role. If you add an account to only this role, then the account will not have sufficient database permissions to run Reporter.
Object Set Creator	Privileges to create, modify, and delete object sets. If you select this role, then you must also select either the Live Report Generator role or the Stored Report Generator role. If you add an account to only this role, then the account will not have sufficient database permissions to run Reporter.
Quest Knowledge Portal User	Privileges to generate reports through the Quest Knowledge Portal.

If you are using SQL Server 2005 or later, users and schemas are separated. Therefore, each user must have their own schema in order to be given role permissions. To give a user a schema 1. In SQL Server Management Studio, add a new schema. 2. Create a user account. 3. Assign the new schema and role permissions to the new user account. 4. Make the user account schema owner of the new schema.   This procedure can also be accomplished using the following SQL statement:   CREATE SCHEMA <schema name> AUTHORIZATION <user name> ALTER USER <user name> WITH DEFAULT_SCHEMA = <schema name>   The user account must be created in the database security folder prior to running this statement.