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.aspx
http://msdn.microsoft.com/en-us/library/ms187926.aspx
The Answer is: D.
0
0
Yes, D is sufficient
0
0
D
0
0
But when I’ll make the exam, I’ll have to choose B or D?
In all vce that I’ve seen the answer is B (WITH EXECUTE AS USER = ‘dbo’)
0
0
Test Script:
use [AdventureWorks2012]
drop user ContosoUser;
drop proc Purchases.PurgeInactiveSuppliers;
drop table Purchases.Suppliers;
drop schema Purchases;
create schema Purchases;
create table Purchases.Suppliers (id int, IsActive int);
insert into Purchases.Suppliers select 1, 1 union all select 2, 0 union all select 2, 1;
select * from Purchases.Suppliers;
CREATE USER ContosoUser WITHOUT LOGIN
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0;
GRANT EXECUTE ON Purchases. PurgeInactiveSuppliers TO ContosoUser;
execute as user = ‘ContosoUser’;
exec Purchases.PurgeInactiveSuppliers
revert
select * from Purchases.Suppliers
the answer is ‘D’
0
0
Answer is D i tried it on my Computer and it works but for B it gave my error on ‘User=’dbo”
0
0
here is the same question from 70-461 exam:
http://www.aiotestking.com/microsoft/which-transact-sql-statement-should-you-use-59/
correct awnser there:
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser
so
B is INCORRECT
D is the correct awnser!
0
0