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 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
Explanation:
http://msdn.microsoft.com/en-us/library/ms188354.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx
This question is from the 462. Will not appear on the 461.
0
0
D
0
0
B.
0
0
If ContosoUser has no DELETE privilege on Purchases.Suppliers, although he/she has EXECUTE privileges on the Stored Procedure, without the “WITH EXECUTE AS USER = ‘dbo’ “, the SP will fail. I agree with B as the correct answer
0
0
‘b’ is for sure wrong because of syntaxt error. You cannot use something like:
“with execute as user = ‘dbo'”;
you could use instead of it:
“with execute as ‘dbo'”, but there is not such answer.
‘a’ gives direct permission to delete rows, so wrong;
‘c’ is wrong,
and ‘d’ for me is ok.
0
0
What’s more, part of that new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0