You administer a SQL 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 remove the Select permission for UserA on the Regions table. You also need to
ensure that UserA can still access all the tables in the Customers schema, including the
Regions table, through the Sales role permissions.
Which Transact-SQL statement should you use?
A.
DENY SELECT ON Object::Regions FROM UserA
B.
DENY SELECT ON Schema::Customers FROM UserA
C.
EXEC sp_addrolemember ‘Sales’, ‘UserA’
D.
REVOKE SELECT ON Object::Regions FROM UserA
E.
REVOKE SELECT ON Object::Regions FROM Sales
F.
EXEC sp_droproiemember ‘Sales’, ‘UserA’
G.
REVOKE SELECT ON Schema::Customers FROM UserA
H.
DENY SELECT ON Object::Regions FROM Sales
I.
DENY SELECT ON Schema::Customers FROM Sales
J.
REVOKE SELECT ON Schema::Customers FROM Sales
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
Alternativa correta é ‘A’, pois o GRANT SCHEMA não é removido com REVOKE OBJECT.
Marcelo, “A” cannot be correct as DENY will take precedence and will stop UserA from selecting from Region table even it has select permission through the role.
Correct answer is “D”
Ans. is D. Revoke select on object::Regions from UserA
@Faisal: Talking about precedence, which one is higher? Role-assigned permission or individual-assigned permission?!
If you’re a member of a sysadmin fixed server role, and you’ve been assigned some permissions through that role, they take precedence over and above every other permissions assigned to you as an individual system user (even if part of those latter permissions accrue as a result of you being a local administrator on your system). Hence, your submission above does not always hold true.
Nonetheless, the correct answer is option D (based on same reasoning explained for the two previous questions). Revoke select on Regions from UserA; but he can still select from the Customers schema (due to implicit permission).
Deny takes priority no matter what level the permission comes from for users.
https://msdn.microsoft.com/en-us/library/ms191291%28v=sql.110%29.aspx
The permission check algorithm includes overlapping group memberships and ownership chaining, both explicit and implicit permission, and can be affected by the permissions on securable classes that contain the securable entity. The general process of the algorithm is to collect all the relevant permissions. If no blocking DENY is found, the algorithm searches for a GRANT that provides sufficient access.