Grant Execute/Select Permissions to Functions and Procedures

Many older EQuIS reports are defined as stored procedures or table-valued database functions. A user without EXECUTE (stored procedure) or SELECT (table-valued function) permissions for the corresponding database object will not see or be able to run those. By default, where appropriate, EQuIS stored procedures and functions have EXECUTE/SELECT permission granted to the ES_PUBLIC database role created in EQuIS 7.23.2. Granting permission to ES_PUBLIC will grant any user with permission to that role the ability to log in to the database and use those stored procedures and functions.

If some users cannot see active database reports or encounter permission errors when running these reports, consider resetting the permissions. The script below will use the metadata in the database to generate a GRANT statement for certain stored procedures and functions in the database (including custom functions). A user with SQL access to the database can follow these steps:

1.Log into SQL Server Management Studio and connect to the EQuIS Database.

2.Open a new query window and paste in the following query:

-- functions
SELECT 'GRANT ' + CASE WHEN o.type IN ('FN') THEN ' EXECUTE ' ELSE ' SELECT ' END + ' ON ' + OBJECT_SCHEMA_NAME(o.id) + '.' + o.name + ' TO ES_PUBLIC'
  FROM sys.sysobjects o
    WHERE type IN ('IF','FN','TF')

UNION
-- v54 procedures
SELECT 'GRANT EXECUTE ON ' + OBJECT_SCHEMA_NAME(o.id) + '.' + o.name + ' TO ES_PUBLIC'
  FROM sys.sysobjects o
    WHERE type = 'P'
     AND OBJECT_SCHEMA_NAME(o.id) = 'v54'

UNION
-- specific equis.xxx procedures
SELECT 'GRANT EXECUTE ON ' + OBJECT_SCHEMA_NAME(o.id) + '.' + o.name + ' TO ES_PUBLIC'
  FROM sys.sysobjects o
    WHERE type = 'P'
     AND OBJECT_SCHEMA_NAME(o.id) = 'equis'
     AND o.name IN ('get_ids', 'new_ebatch', 'table_order', 'user_context', 'user_report_parameter')

UNION
-- additional permissions needed for some functions
SELECT 'GRANT VIEW DATABASE STATE TO ES_PUBLIC'

UNION
SELECT 'GRANT VIEW DEFINITION TO ES_PUBLIC'

3.Execute the query (result of query will be a series of GRANT statements shown as text).

4.Review and copy all desired GRANT statements into a new query window.

5.Execute the GRANT statements.