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 following requirements are met:
The Sales role does not have the Select permission on the Customers schema.
UserA has the Select permission on the Regions table.
Which Transact-SQL statement should you use?
A.
DENY SELECT ON Object::Regions FROM Sales
B.
DENY SELECT ON Schema::Customers FROM Sales
C.
REVOKE SELECT ON Object::Regions FROM Sales
D.
REVOKE SELECT ON Schema::Customers FROM Sales
E.
DENY SELECT ON Object::Regions FROM UserA
F.
DENY SELECT ON Schema::Customers FROM UserA
G.
REVOKE SELECT ON Object::Regions FROM UserA
H.
REVOKE SELECT ON Schema::Customers FOR 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
B is right answer.
0
0
agree, B, deny (not revoke)
0
0
“Sales role is granted the Select permission on the Customers schema”. In order for the Sales role to cease to have the Select permission, the permission has to be revoked:
REVOKE SELECT ON Schema::Customers FROM Sales
If ‘DENY SELECT ON Schema::Customers FROM Sales’ is used, it would deny UserA access to all objects in Customers (including access to the Regions table).
0
0
Slazenjer_m is right! Deny, would deny UserA the permission on the regions table aswell, so it should be revoke. revoke = “You take the granted rights back”
0
0
Agree with Slazenger_m should be D
0
0