• Article

Grant access to database objects

To perform data integration and run stored procedures, you must grant EXECUTE permissions to each stored procedure and function that is in the staging database. Consider creating a script that iterates through each stored procedure in the staging database, as in the following example.

To grant a user EXECUTE permission on database objects

  1. Run the following statement to give DA_Mary the EXECUTE permission to run the bsp_DI_ValidateHierarchiesForADimension stored procedure:

    GRANT EXECUTE ON bsp_DI_ValidateHierarchiesForADimension TO [domain_name/DA_Mary];
GO

  2. Repeat step 1 for each stored procedure in the staging database.

    To generate a complete list of stored procedures in the staging database on which you might have to grant EXECUTE permissions, consider using the following SELECT statement:

    USE [Alpine_Ski_House_StagingDB]
SELECT name FROM sys.procedures WHERE name LIKE 'bsp[_]%'
GO

See Also