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 and 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 the
Regions table.
Which Transact-SQL statement should you use?
A.
REVOKE SELECT ON Schema::Customers FROM UserA
B.
REVOKE SELECT ON Object::Regions FROM UserA
C.
EXEC sp_addrolemember ‘Sales’, ‘UserA’
D.
DENY SELECT ON Schema::Customers FROM Sales
E.
EXEC sp_droprolemember ‘Sales’, ‘UserA’
F.
REVOKE SELECT ON Schema::Customers FROM Sales
G.
DENY SELECT ON Object::Regions FROM UserA
H.
REVOKE SELECT ON Object::Regions FROM Sales
I.
DENY SELECT ON Schema::Customers FROM UserA
J.
DENY SELECT ON Object::Regions 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
I think F is the right answer
sorry F is not
I guess its J.
UserA is granted SELECT permission on the Regions table; for that permission to be removed, it as to be REVOKED.
The Sales role is granted the SELECT permission on the Customers schema; this gives the Sales role inherited SELECT permission on the Regions table. To remove this inherited permission, it has to be REVOKED.
Hence, the correct answer is option H:
REVOKE SELECT ON Object::Regions FROM Sales
Revoke does not remove inherited permissions. It can not be H
Wrong again Slazenjer…right answer is J
nope its not! its J!
Yes, J is correct with syntax corrected as: DENY SELECT ON Object::Regions TO Sales
@Faisal: Pick the correct answer ‘as is’!! Microsoft is NOT giving you the option of correcting the syntax; if the syntax is wrong as it were, then IT IS WRONG for a reason!!!
To assign the ability to select from an object: GRANT
To remove a previously granted permission: REVOKE
To ensure a user does NOT have certain permissions: DENY {specific permissions}