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 TC ContosoUser
B.
CREATE PROCEDURE Purchases.PurgelnactiveSuppliers
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:
http://msdn.microsoft.com/en-us/library/ms188354.aspxhttp://msdn.microsoft.com/en-us/library/ms187926.aspx
D
Someone on an older version tried it himself:
http://www.aiotestking.com/microsoft/which-transact-sql-statement-should-you-use-43/#comment-13087
Yep i think the right answer is D. B answer gives the DBO permission, so there could be a lot more that what they need.
Yes, it should be D
In any case this is a 461 Exam question. I believe that Ownership Chaining is at work here, which would make D the correct answer. However there is one tiny inconsistency. I am not sure that the creator of the stored procedure has permissions on the purchases Schema. If he hasn’t than the correct answer is B. I would bet on D though.
It should be D
First, because syntax of B is wrong.
Second, It says “You administrate Sql server” which means you are probably member of sysadmin role.
If you create stored procedure, you are its owner. If you give execute permission on that procedure to some user, ownership chaining will be used because sp contain static sql. And ownership chaining works with select, insert, delete, update. (DML).
B is correct
D does not provide the permission to delete from purchases.suppliers due to ownership chaining
recall my earlier comment.
As both objects (procedure and referring table) are in the same schema and no explicit owner is specified for the procedure, ownership chaining works and the given permissions under D are sufficient.
B is incorrect as the objects are in different schemas. Providing execute permissions to the procedure doesn’t necessary allow you to delete from the table when the table has a different owner. In that situation explicit rights should be given to delete from the table.