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 UserA is disallowed to select from any of the tables in the
Customers schema.
Which Transact-SQL statement should you use?
A.
DENY SELECT ON Object::Regions FROM UserA
B.
DENY SELECT ON Object::Regions FROM Sales
C.
REVOKE SELECT ON Schema::Customers FROM Sales
D.
REVOKE SELECT ON Schema::Customers FROM UserA
E.
REVOKE SELECT ON Object::Regions FROM Sales
F.
REVOKE SELECT ON Object::Regions FROM UserA
G.
DENY SELECT ON Schema::Customers FROM Sales
H.
DENY SELECT ON Schema::Customers FROM 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
Ans. is H. Deny Select on schema::Customers from UserA
0
0
Correct option is D:
USE AdventureWorks2012;
DENY EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo
TO Recruiting11;
GO
USE AdventureWorks2012;
REVOKE EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo
FROM Recruiting11;
GO
0
0
D is not correct. With corrected Syntax, H would be the correct answer as below:
DENY SELECT ON Schema::Customers TO UserA
From the question, UserA has not even been granted SELECT on the Customers schema, hence the statement in “D” will not work.
0
1
@Faisal: Have you ever heard of ‘implicit object permissions’?! By being a member of the Sales role, UserA is granted the SELECT permission on the Customers schema…
In order to remove this implicit SELECT permission from UserA, you have to REVOKE it!!!
PS: Please, I have been working with SQL Server since version 6.5, so, I think I know a bit more of what I’m talking about here… READ the references:
https://msdn.microsoft.com/en-us/library/ms187719%28v=sql.110%29.aspx
https://msdn.microsoft.com/en-us/library/ms173724%28v=sql.110%29.aspx
0
0
Revoking will not remove implicit object permissions. READ your own reference:
“Revoking ALL does not revoke all possible permissions. Revoking ALL is equivalent to revoking all ANSI-92 permissions applicable to the specified object. ”
Revoking anything from UserA will not Revoke permissions granted by sales. But, anyone can feel free to test it, no need to have been working on SQL since 6.5 to know what your talking about.
— 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
— Revoking from user as suggested
REVOKE SELECT ON Schema::Customers FROM UserA
EXECUTE AS LOGIN = ‘LoginUserA’;
GO
— this still works
Select * from Customers.Regions;
GO
0
0
Slezenjer, you may have been working with SQL Server since …forever, that doesn’t imply you’re right. And you are wrong on this one.
0
0