Cotega Minimum User Credentials Required

The following document outlines the minimum user permissions required by Cotega to perform monitoring of your database. It is important to note that Cotega will only execute SELECT queries against system tables and will never execute queries against your base tables.

Creating a Cotega User and Login

Using your system administator account connect to the Master database and execute the following where [PASSWORD] is replaced with a valid password:

• CREATE LOGIN [COTEGAUSER] WITH password = '[PASSWORD]';
• CREATE USER [COTEGAUSER];
• (SQL Server Only) GRANT VIEW SERVER STATE TO [COTEGAUSER];

Using your system administator connect to the database that will be monitored and execute:

• CREATE USER [COTEGAUSER];
• GRANT VIEW DATABASE STATE TO [COTEGAUSER];
• (SQL Server Only) GRANT VIEW SERVER STATE TO [COTEGAUSER];

Credentials Required for Scheduled Stored Procedures

If you wish to let Cotega execute scheduled stored procedures for you, please ensure that you have "GRANT EXECUTE" priviledges on these stored procedures for the Cotega user specified as outlined below.

• GRANT EXEC ON [ProcedureName] TO [COTEGAUSER];

In addition, Cotega needs to be able to list the set of stored procedures you have. As such it needs select access to the sys.procedures table as outlined below.

• GRANT SELECT ON sys.procedures TO [COTEGAUSER];

In some cases you will need to grant the [COTEGAUSER] access to the schema of your stored procedures. You will know this is true if you do not see your stored procedure in the list when you add it. To do this execute the following:

• GRANT Execute On Schema::[SCHEMA_NAME] To [COTEGA USER]

If you are uncertain which schema your stored procedure is using, the following query which you should run as the admin for your database will help.

• SELECT sys.procedures.name as StoredProc, sys.schemas.name as SchemaName
  from sys.procedures, sys.schemas where type_desc = 'SQL_STORED_PROCEDURE'
  and sys.procedures.schema_id = sys.schemas.schema_id
  order by sys.procedures.name

Credentials Required for to Send Emails from SQL Azure

If you wish to utilize Cotega's "Send Email from SQL Azure" capbaility, please ensure that you have "GRANT INSERT, UPDATE, DELETE, SELECT" priviledges on CotegaMessageQueue table within your database.

• GRANT INSERT, UPDATE, DELETE, SELECT on [CotegaMessageQueue] to [COTEGAUSER];

At this point you can choose to 'Add Agent' from the Dashboard and use this new cotegauser user account to start monitoring your databases.