Granting permission to Non-Optiva_User for stored procedures

In a SQL Server database, stored procedures are used for Global Calc and Where Used. You must grant permission to these stored procedures for database users other than Optiva_User. Otherwise, an error about permissions is displayed when these functions are run for that database.

By default, Optiva grants permission for Optiva_User to new databases and those that are upgraded by the dbChanges<version number>.sql.

If you have a database with a different user name, you must run a SQL script with statements to grant permission. In this example, permission is granted to Optiva_User.

/* Create a new role for executing stored procedures */
CREATE ROLE db_executor

/* Grant stored procedure execute rights to the role */
GRANT EXECUTE TO db_executor

/* Add a user to the db_executor role */
EXEC sp_addrolemember 'db_executor', 'Optiva_User'

Additional permissions can be granted by replacing the final line with this syntax.

EXEC sp_addrolemember 'db_executor', '<db_user_name>'