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
The Solution B) is wrong
The Solution D) is right.
The Explanation is found here:
http://msdn.microsoft.com/en-us/library/bb153640.aspx
You can try it by this example:
USE AdventureWorks2012;
GO
CREATE LOGIN LoginContosoUser WITH PASSWORD = ‘J345#$)thb’;
GO
CREATE USER ContosoUser FOR LOGIN LoginContosoUser;
GO
CREATE SCHEMA Purchases;
GO
CREATE TABLE Purchases.Suppliers (SupplierID int, IsActive bit)
GO
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
AS BEGIN
DELETE FROM Purchases.Suppliers
WHERE IsActive = 0;
END;
GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser;
GO
— Switch from Admin-User to Contosouser
PRINT SUSER_NAME()
PRINT USER_NAME()
EXECUTE AS LOGIN = ‘LoginContosoUser’;
GO
PRINT SUSER_NAME()
PRINT USER_NAME()
— Execute the stored procedure ==> OK, because of Ownership Chaining from the Schema to the tables in the procedure
EXECUTE Purchases.PurgeInactiveSuppliers;
GO
— Direct Delete fails because no rights (no ownership chaining)
DELETE FROM Purchases.Suppliers WHERE IsActive = 0;
GO
REVERT;
GO
PRINT SUSER_NAME()
PRINT USER_NAME()
DROP TABLE Purchases.Suppliers;
GO
DROP SCHEMA Purchases;
GO
DROP PROCEDURE Purchases.PurgeInactiveSuppliers;
GO
DROP LOGIN LoginContosoUser;
GO
DROP USER ContosoUser;
GO
You can look also here:
Page 379
Chapter “How it works”:
http://books.google.de/books?id=SbwLJTD-UuYC&pg=PA379&lpg=PA379&dq=ownership+chaining+sql+server+2012+procedure+delete+from&source=bl&ots=3rfleZt4Zx&sig=qjOOP_DbulI6uaTWPSrTuAMNBHs&hl=de&sa=X&ei=sZSoUaCjDIHbtAaXiYHYBg&ved=0CGQQ6AEwBg#v=onepage&q=ownership%20chaining%20sql%20server%202012%20procedure%20delete%20from&f=false
Summed up shortly:
If a stored procedure belongs to the same Schema as the tables which are referenced in the stored procedure only the exucute-right on the stores procedure is checked.
They say nothing about ownerships, so execute as dbo will work in any care .
D is working only if ownership chaining in available
what is the correct answer?
Both option B & D are correct. However, creating the Purchases.PurgeInactiveSuppliers procedure “WITH EXECUTE AS USER = ‘dbo’ clause”, ensures that users not belonging to the Purchases schema, are able to execute the procedure without being granted explicit access on the referenced table/tables. So, option D seems more correct.
Sorry, I meant Option B.
Both option B & D are correct. However, creating the Purchases.PurgeInactiveSuppliers procedure “WITH EXECUTE AS USER = ‘dbo’ clause”, ensures that users not belonging to the Purchases schema, are able to execute the procedure without being granted explicit access on the referenced table/tables. So, option B seems more correct.
If I am understanding the question states that they want the User ContosoUser to execute the stored procedure nothing about any other users not belonging to the Purchases schema, so why give more permissions where its not needed you can always alter it later on?, Also it says “minimal required permissions” hence D is the correct answer for this question even thought B would work but for a different scenario
I agree with you that option D is the correct one because we need “minimal required permissions” while:
“The dbo User Account
The dbo, or database owner, is a user account that has implied permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo.”
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/server-and-database-roles-in-sql-server
The time to read or take a look at the content material or sites we’ve linked to beneath.
Every once in a though we select blogs that we read. Listed below are the most recent web sites that we opt for
b
that could be the end of this article. Here youll uncover some web-sites that we think you will appreciate, just click the links over
The details mentioned in the write-up are several of the ideal obtainable
just beneath, are various completely not associated web-sites to ours, nevertheless, they may be surely worth going over
very few websites that come about to become comprehensive beneath, from our point of view are undoubtedly well worth checking out
very few sites that transpire to become comprehensive below, from our point of view are undoubtedly properly really worth checking out
just beneath, are quite a few totally not related sites to ours, on the other hand, they are surely worth going over
here are some hyperlinks to internet sites that we link to simply because we believe they’re really worth visiting
Here are several of the websites we advocate for our visitors
we prefer to honor a lot of other online websites around the net, even if they arent linked to us, by linking to them. Under are some webpages worth checking out
here are some links to web pages that we link to due to the fact we believe they are really worth visiting
D is the correct answer. There is no need to grant access to any other users. It says: minimal permissions. using WITH EXECUTE AS USER = ‘dbo’ will grant access to users that do not belong to Purchases schema which is not required.