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
J. REVOKE SELECT ON …
The syntax is wrong for DENY SELECT ON … and the permission is already granted so it has to be REVOKED.
0
0
But if you only REVOKE SELECT ON Sales, won’t UserA still have SELECT permission since this permission is also granted to the user?
0
0
The correct answer is REVOKE SELECT ON Schema:Customers FROM Sales.
@Brian: The “REVOKE SELECT ON Schema::Customers FROM Sales”, takes precedence over individual SELECT permission granted to UserA, because it’s a role-based permission. The SELECT permission is removed from all members of the ‘Sales’ role by revoking a previously-granted permission.
0
0
You need some studying Slazenjer…I see answer these questions wrong almost every time.
0
0
Well you have to differentiate between the two permissions.
UserA has a individual select permission on Regions table
and UserA has a permission on the rest of the schema via the Sales role.
If you only revoke the select statement on the Sales role, the individual permission on the regions table is still there. So the userA is still allowed to select it.
You need to deny the select on the sales role, so the userA is not allowed anymore, cause the deny statement is more “powerful” then the select statement.
-> Right answer: F: DENY ON Schema::Customers FROM Sales
0
0
This can be easily tested :
— Create the DB
CREATE DATABASE [SalesDb]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’SalesDb’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SalesDb.mdf’ , SIZE = 8192KB , FILEGROWTH = 8192KB )
LOG ON
( NAME = N’SalesDb_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SalesDb_log.ldf’ , SIZE = 8192KB , FILEGROWTH = 8192KB )
GO
–… And schema
USE [SalesDb]
GO
CREATE SCHEMA [Customers] AUTHORIZATION [dbo]
GO
— … And table
CREATE TABLE [Customers].Regions
(ID INT NOT NULL
,RegionName Varchar(50)
) ON [Primary]
GO
— Create the ROLE
CREATE ROLE [Sales] AUTHORIZATION [dbo]
GO
— … And the login
USE [master]
GO
CREATE LOGIN [UserA] WITH PASSWORD=N’1′, DEFAULT_DATABASE=[SalesDb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [SalesDb]
GO
CREATE USER [UserA] FOR LOGIN [UserA]
GO
ALTER ROLE [Sales] ADD MEMBER [UserA]
GO
— Grant Rights
GRANT SELECT ON Schema::Customers TO [Sales]
GRANT SELECT ON [Customers].[Regions] TO [UserA]
GO
— J: — No effect on UserA
REVOKE SELECT ON Schema::Customers FROM Sales
GO
— F: — UserA will no longer be able to select from the Regions table
DENY SELECT ON [Customers].[Regions] TO [Sales]
GO
–> Right answer is F, as Josef has stated correctly
0
0