You administer a Microsoft SQL Server 2012 databasenamed ContosoDb.
The database contains a table named Suppliers and acolumn 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 rowsthat are not active from Suppliers.
You also need to grant ContosoUser only the minimumrequired 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:
Need to check these
Reference: http://msdn.microsoft.com/en-us/library/ms188354.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms187926.aspx
D is the correct option
0
0
I agree. no direct privilege is needed and minimum permissions are asked
0
0
I’m with you!
To be sure I also tried by myself and the aswer is definitely D!
0
0
B wrong syntax, correct one is Execute As ‘dbo’, without user
0
0
I would have to still go with B as the correct answer. ContosoUser has no permissions to the Suppliers Table, so how would executing the Procedure automatically grant those rights without execute as dbo, regardless of typo’s and syntax here….
0
0
…and you would be wrong.
D is the right answer
0
0
its D
https://borntolearn.mslearn.net/certification/database/f/516/t/327828
0
0