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:
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
Ans. F. Deny select on schema::Customers from Sales
0
0
Correct answer is J:
The Sales role is previously granted SELECT permission on the the Customer schema. In oreder to prevent this permission from being effective going forward, you have to REVOKE it.
Secondly, there’s no syntax like DENY SELECT ON… FROM…
The correct deny syntax is: DENY SELECT ON Schema::Customers TO Sales. Meanwhile, the REVOKE syntax is absolutely correct.
0
0
Slazenger_M you’re correct about the syntax, but with the correction of syntax, “F” is the correct answer.
“J” cannot be correct because you need to ensure that both the rols Sales and UserA are disallowed to select from “any” of the tables in the Customers schema. The UserA has been separately granted the Select permission on the Regions table, hence REVOKE from Sales will still enable UserA to select from Region table.
0
1
@Faisal: You said “The UserA has been separately granted the Select permission on the Regions table…”
In order to remove a previously granted permission, you cannot DENY it!! You have to REVOKE any previously granted permission. Please read the following:
https://msdn.microsoft.com/en-us/library/ms173724%28v=sql.110%29.aspx
https://msdn.microsoft.com/en-us/library/ms187719%28v=sql.110%29.aspx
Plus, Microsoft didn’t just make typographic errors in the answers by inserting the wrong keyword in the DENY syntax… it is a deliberate attempt to suck people in.
Again, I repeat, once a permission is granted, you can only remove it by REVOKING, not by DENYING.
0
0
SalesDb contains a schema named Customers that has a table named Regions.
UserA is granted the Select permission on the Regions table.
J. REVOKE SELECT ON Schema::Customers FROM Sales
J will do nothing to remove access to UserA, just as and Revoke on UserA will not remove permissions from Sales.
You can test it by:
— 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 J from Question 8
REVOKE SELECT ON Schema::Customers FROM Sales
EXECUTE AS LOGIN = ‘LoginUserA’;
GO
— this still works
Select * from Customers.Regions;
GO
0
0
F is the right answer.
Contrary to what Slazenjer states, you can “grant select: and then “deny” it. This will prohibit the user from selecting.
0
0
yes F is right though syntax is wrong
http://websql.org/websql/help/sqlserver2000/tsql/ts_ra-rz_2un9.html
0
0