Querying Microsoft SQL Server 2012/2014 — Question 16

You administer a Microsoft SQL Server database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the
Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that
ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-
SQL statement should you use?

Answer options

• A. GRANT DELETE ON Purchases.Suppliers TO ContosoUser
• B. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers WITH EXECUTE AS USER = 'dbo' AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
• C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
• D. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

Correct answer: D

Explanation

The correct answer is D because it creates a stored procedure that allows ContosoUser to delete inactive suppliers while maintaining the principle of least privilege. Option A would grant delete permissions directly on the Suppliers table, which is more than necessary. Option B incorrectly uses EXECUTE AS USER = 'dbo', which is not required for this scenario, and option C only allows for selecting data without any deletion capabilities.