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
disagree with answer. i would go with D
0
0
see my comment on 23 – the same applies here. if this were in the dbo schema, it seems all you need is execute permission, but since it is a user schema, you need to execute as a user that has delete permission
0
0
Great answer Shak. That makes sense.
Should it be CREATE PROCEDURE dbo.PurgelnactiveSuppliers, there won’t be any need of “WITH EXECUTE AS USER = ‘dbo’ “.
B is the answer.
0
0
Have any of you tried?
I have created the objects (schema, table, user and stored procedure). Option B is the right answer!
0
0
This is how I understand how ownership chaining works. A reads B. If you own A and B, and you give someone the ability to execute A, they’ll get access to B. If you don’t own B and you have the ability to read B, you can run A, but giving someone else the ability to execute A isn’t enough. The ownership chain is broken. They must be able to read B themselves, or they must execute A as either the owner or the dbo. You made A, the procedure. So as adiminstrator, dbo owns A. Soemone else made B, presumably. That’s why B is the answer, the ownership chain is broken. I dont’ know what this has to do with schemas. Vivek is only right if contosouser had select rights on DBO schema and everything was made in the dbo schema.
0
0
I think D is correct
http://www.aiotestking.com/microsoft/which-transact-sql-statement-should-you-use-80/
0
0
Also check the comments here
http://www.aiotestking.com/microsoft/which-transact-sql-statement-should-you-use-43/
0
0
Correct syntax is ‘WITH EXECUTE AS ‘UserName”. But in B it sounds as ‘WITH EXECUTE AS USER = ‘dbo’ ‘ which is not correct. So D is correct one.
0
0
I go with D
0
0
When a stored procedure is created and used to perform an INSERT, DELETE or SELECT against another database object,if the schema of the stored procedure is the same as the schema of the object referenced within, SQL Server checks only that the stored procedure caller has EXECUTE permission to the stored procedure. In this case both the stored procedure and the table are in the same Purchases schema. So it is not necessary to set the security context as EXECUTE as USER=dbo
0
0