You administer a SQL Server 2012 server that contains a database named SalesDb.
SalesDb contains a schema named Customers that has a table named Regions.
A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table.
The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the Sales role, including UserA, is disallowed to select from any of the tables in the
Customers schema.
Which Transact-SQL statement should you use?
A.
REVOKE SELECT ON Schema::Customers FROM UserA
B.
DENY SELECT ON Object::Regions FROM UserA
C.
EXEC sp_addrolemember ‘Sales’, ‘UserA’
D.
DENY SELECT ON Object::Regions FROM Sales
E.
REVOKE SELECT ON Object::Regions FROM UserA
F.
DENY SELECT ON Schema::Customers FROM Sales
G.
DENY SELECT ON Schema::Customers FROM UserA
H.
EXEC sp_droprolemember ‘Sales’, ‘UserA’
I.
REVOKE SELECT ON Object::Regions FROM Sales
J.
REVOKE SELECT ON Schema::Customers FROM Sales
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms188369.aspx
http://msdn.microsoft.com/en-us/library/ms187750.aspx
http://msdn.microsoft.com/en-us/library/ff848791.aspx
Solution f) ist right.
Only the Syntax for SQL SERVER 2012 is TO instead of FROM:
DENY SELECT ON Schema::Customers TO Sales;
You can test ist by using this TSQL
USE SalesDB;
GO
— Create Role Sales
CREATE ROLE Sales;
GO
— Create UserA
CREATE LOGIN LoginUserA WITH PASSWORD = ‘J345#$)thb’;
GO
CREATE USER UserA FOR LOGIN LoginUserA;
GO
— Add UserA to Role Sales
EXEC sp_addrolemember @rolename = ‘Sales’, @membername = ‘UserA’;
GO
— Schema Customers
CREATE SCHEMA Customers;
GO
CREATE TABLE Customers.Regions(RegionID int);
GO
— Set Rights for Role and User
GRANT SELECT ON OBJECT::Customers.Regions TO Sales;
GO
GRANT SELECT ON OBJECT::Customers.Regions TO UserA;
GO
— Set the execution context to LoginUserA
EXECUTE AS LOGIN = ‘LoginUserA’;
GO
— this works
Select * from Customers.Regions;
GO
REVERT;
GO
— Solution F from Question 8
DENY SELECT ON Schema::Customers TO Sales;
— that raise an error because of the deny
— Meldung 229, Ebene 14, Status 5, Zeile 1
— The SELECT permission was denied on the object ‘Regions’, database ‘SalesDB’, schema ‘Customers’.
EXECUTE AS LOGIN = ‘LoginUserA’;
GO
Select * from Customers.Regions;
GO
REVERT;
GO
— Remove the temporary principal
DROP TABLE Customers.Regions
GO
DROP SCHEMA Customers;
GO
DROP LOGIN LoginUserA;
GO
DROP USER UserA;
GO
DROP ROLE Sales;
GO
0
0
But I change you query ‘DENY SELECT ON Schema::Customers TO Sales;’ to ‘REVOKE SELECT ON Schema::Customers TO Sales;’. The results are same.
0
0
is this correct?
0
0
The correct answer is option J.
Reason: The question says “both UserA and the ‘Sales’ role have been previously granted SELECT permission on the Regions table and Customers schema respectively.”
DENY (T-SQL): Denies a permission to a principal; prevents that principal from inheriting the permission through its group or role memberships.
REVOKE (T-SQL): Removes a previously granted or denied permission.
Also, the syntax in option F above is wrong. The correct syntax for DENY is:
DENY { ALL [ PRIVILEGES ] }
| permission [ ( column [ ,…n ] ) ] [ ,…n ]
[ ON [ class :: ] securable ] TO principal [ ,…n ]
[ CASCADE] [ AS principal ]
And the syntax for REVOKE is:
REVOKE [ GRANT OPTION FOR ]
{
[ ALL [ PRIVILEGES ] ]
|
permission [ ( column [ ,…n ] ) ] [ ,…n ]
}
[ ON [ class :: ] securable ]
{ TO | FROM } principal [ ,…n ]
[ CASCADE] [ AS principal ]
0
0
Maybe I am mistaken, but, the answer is to Deny the sales role.
if you revoke it it is as if the sales role had no grant but he UserA will still have grant to the regions table.
But if you deny it the UserA will not have permissions to access the Regions table.
http://www.sqlservercentral.com/blogs/steve_jones/2011/06/30/the-difference-between-revoke-and-deny/
0
0
No. I figured it out. Look at 56,7,8
http://www.microsoft4shared.com/new-updated-microsoft-mcsa-70-462-real-exam-questions-and-answers-download-51-60.html
If the question says “disallow” then the answer is DENY
If the question says “remove” permission, then it is REVOKE
This question doesn’t really test your database knowledge, it’s a trick of english semantics that has nothing to do with computers.
0
0
Its probed. The way Henry Figgins says, works!!
If they ask about Disalow, the answer must be DENY. —> D – D
If they ask about Remove permission, the answer must be REVOKE —> R – R
0
0