Which Transact-SQL statement should you use?

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:
References:
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

Show Hint

← Previous question

Next question →

Testee

Solution f) ist right.

Only the Syntax for SQL SERVER 2012 is TO instead of FROM:

DENY SELECT ON Schema::Customers TO Sales;

You can test ist by using this TSQL

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 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 F from Question 8
DENY SELECT ON Schema::Customers TO Sales;

— 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

Michael Wu

But I change you query ‘DENY SELECT ON Schema::Customers TO Sales;’ to ‘REVOKE SELECT ON Schema::Customers TO Sales;’. The results are same.