Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2012 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?

You administer a Microsoft SQL Server 2012 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?

A.
GRANT DELETE ON Purchases.Suppliers TO Contoso User

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

Explanation:
According to these references, this answer looks correct.

Reference:
http://msdn.microsoft.com/en-us/library/ms188354.aspx
http://msdn.microsoft.com/en-us/library/ms187926.aspx



Leave a Reply 3

Your email address will not be published. Required fields are marked *


Ritesh

Ritesh

Correct Answer should be “D”

LL

LL

B is correct answer

khalil

khalil

B is correct answer with following line change

WITH EXECUTE AS USER = ‘dbo’
should be
WITH EXECUTE AS ‘dbo’