Administering Microsoft SQL Server 2012/2014 Databases — Question 34

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

Answer options

• A. CREATE ROLE proc_caller GRANT EXECUTE ON Schema: : dbo TO proc_caller ALTER ROLE proc_caller ADD MEMBER BillingUsers
• B. GRANT EXECUTE ON INFORMATION_SCHEMA.ROUTINES TO BillingUsers
• C. EXEC sp_addrolemember 'executor', 'BillingUsers'
• D. CREATE ROLE proc_caller GRANT EXECUTE ON ALL PROCEDURES TO proc_caller ALTER MEMBER BillingUsers ADD TO ROLE proc_caller

Correct answer: D

Explanation

The correct answer is D because it creates a role specifically for executing procedures and then grants the EXECUTE permission for all procedures to that role, subsequently adding BillingUsers to it. The other options either grant permissions incorrectly, target the wrong schema, or reference non-existent roles, making them invalid for the requirement.