Administering a SQL Database Infrastructure — Question 27

You administer a Microsoft SQL Server 2016 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 permissions for all stored procedures in Contoso to BillingUsers.
Which Transact-SQL statement should you use?

Answer options

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

Correct answer: D

Explanation

The correct answer is D because it directly grants EXECUTE permissions on the stored procedures within the dbo schema of the Contoso database to the BillingUsers role. Option A is incorrect as it attempts to add the role to a different role rather than granting permissions. Option B is incorrect because it involves creating another role and adding BillingUsers to it instead of granting the required permissions directly. Option C grants permissions on the schema but does not specify the database context correctly, making it less accurate than option D.