Administering Microsoft SQL Server 2012/2014 Databases — Question 56

You administer a Microsoft SQL Server database named Contoso that contains a single user-defined database role named BillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permissions for all stored procedures in Contoso to BillingUsers.
Which Transact-SQL statement should you use?

Answer options

• A. GRANT EXECUTE ON Schema::dbo TO BillingUsers
• B. EXEC sp_addrolemember 'db_procexecutor', 'BillingUsers'
• C. GRANT EXECUTE ON INFORMATION_SCHEMA.ROUTINES TO BillingUsers
• D. GRANT EXECUTE ON Schema::Contoso TO BillingUsers

Correct answer: A

Explanation

The correct answer is A, as it directly grants EXECUTE permissions on the dbo schema where all stored procedures reside. Options B and C do not grant the necessary permissions on stored procedures specifically, while option D incorrectly targets the Contoso schema, which is not where the procedures are located.