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 UserA is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?
A.
DENY SELECT ON Object::Regions FROM UserA
B.
DENY SELECT ON Object::Regions FROM Sales
C.
REVOKE SELECT ON Schema::Customers FROM Sales
D.
REVOKE SELECT ON Schema::Customers FROM UserA
E.
REVOKE SELECT ON Object::Regions FROM Sales
F.
REVOKE SELECT ON Object::Regions FROM UserA
G.
DENY SELECT ON Schema::Customers FROM Sales
H.
DENY SELECT ON Schema::Customers FROM UserA
I.
EXEC sp_addrolemember ‘Sales’, ‘UserA’
J.
EXEC sp droprolemember ‘Sales’, ‘UserA’
Explanation:
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 H) is rigth
You can test this:
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 SCHEMA::Customers 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 UserA;
— 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
The key here is the word ‘disallow’. Other questions will say ‘remove’ and that is when you use REVOKE.
also if the question says not allow UserX to access TableX but be able to Select via SomeServerRole then that too will be a revoke
I was a question faster than you. I figured this out last question. Again, nothing to do with Computers. All about test taking ninja skills
“UserA is granted the Select permission on the Regions table.
The Sales role is granted the Select permission on the Customers schema.
”
Granting a permission removes DENY or REVOKE of that permission on the specified securable. If the same permission is denied at a higher scope that contains the securable, the DENY takes precedence. However, revoking the granted permission at a higher scope does not take precedence.
http://msdn.microsoft.com/en-us/library/ms187728.aspx
— Only this statement satisfy the requirement
DENY SELECT ON Schema::Customers TO Sales
Clarification
DENY SELECT ON Schema::Customers TO Sales
is the answer to another similar question:
You need to ensure that the Sales role, including UserA, is disallowed to select from any of the tables in the
Customers schema.
————————————————————————————-
DENY SELECT ON Schema::Customers TO UserA
is the answer to the question:
You need to ensure that UserA is disallowed to select from any of the tables in the Customers schema.
so what is the correct answer?
I think testee is correct
Solution H is syntactically wrong. You can’t DENY an already granted permission; you can only revoke it!! Moreso, the syntax for option H above is very wrong.
The correct answer is option D: REVOKE SELECT ON Schema::Customers FROM UserA.
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.
Correct syntax for DENY is:
DENY { ALL [ PRIVILEGES ] }
| permission [ ( column [ ,…n ] ) ] [ ,…n ]
[ ON [ class :: ] securable ] TO principal [ ,…n ]
[ CASCADE] [ AS principal ]
And the correct syntax for REVOKE is:
REVOKE [ GRANT OPTION FOR ]
{
[ ALL [ PRIVILEGES ] ]
|
permission [ ( column [ ,…n ] ) ] [ ,…n ]
}
[ ON [ class :: ] securable ]
{ TO | FROM } principal [ ,…n ]
[ CASCADE] [ AS principal ]
Facts:
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.
Requirement:
Disallow UserA to select from any of the tables in the Customers schema
Answer: H
DENY SELECT ON Schema::Customers FROM UserA