Grant Execute/Select Permissions to Functions and Procedures

Many standard EQuIS reports are defined as stored procedures or table valued database functions. A user that does not have execute (stored procedure) or select (table valued function) permissions to the corresponding database object, will not see the report (or be able to run it). By default, the stored procedures and functions are created and execute/select permission is granted to the public role. Granting permission to public will grant any user with permission to log in to the database, permission to use the stored procedures and functions.

If some users cannot see some of the database reports or if users are reporting 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 all of the stored procedures and functions in the database (including custom objects, etc.). Follow these steps:

1.Log in to 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 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 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 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 public'

UNION
select 'GRANT VIEW DEFINITION TO public'

3.On the menu, choose Query>>Result To>>Text.

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

5.Copy all of the GRANT statements into a new query window.

6.Execute the GRANT statements.