You have a user named John. He has SELECT access to the Sales schema. You need to eliminate John's SELECT access rights from the Sales.SalesOrder table without affecting his other permissions. Which…

Question

Options

• ADROP USER John;
• BDENY SELECT ON Sales.SalesOrder TO John;
• CGRANT DELETE ON Sales.SalesOrder TO John;
• DREVOKE SELECT ON Sales.SalesOrder FROM John;

• A
  9% (4)
• B
  70% (30)
• C
  16% (7)
• D
  5% (2)

Explanation

REVOKE - permanently removes both granted an denied permissions on an object, resulting in no permissions. Main thing you have to remember is, this does not restrict user accessing the object completely. If user is in a role that has permission on the object for the operation, user will be able to perform the operation. DENY - Denies permission to the object for an operation. Once it set, it takes precedence over all other GRANT permissions, user will not be able to perform the operation against the object. To sum up, because John does not have GRANT permissions on the Sales.SalesOrder table (instead it has GRANT permission on Sales schema), then REVOKE SELECT ON Sales.SalesOrder from John will not remove any permissions. Here is a code that shows it clearly. -- create a login and user CREATE LOGIN [John] WITH PASSWORD = '1', CHECK_POLICY = OFF USE [AdventureWorks2008] CREATE USER [John] FOR LOGIN [John] WITH DEFAULT_SCHEMA = [dbo] -- grant permission on Sales schema GRANT SELECT ON SCHEMA :: Sales TO [John] -- Run SELECT with John's credentials and see -- He sees records EXECUTE AS USER = 'John' SELECT * FROM Sales.SalesOrderHeader -- Revoke permisson for the table from him REVOKE SELECT ON Sales.SalesOrderHeader FROM [John] -- He still sees data EXECUTE AS USER = 'John' SELECT * FROM Sales.SalesOrderHeader -- This explicitly denies permission on SalesOrderHeader to John -- Once this is executed, he will not be able to see data -- even we grant him again. DENY SELECT ON Sales.SalesOrderHeader TO [John] -- He sees error message: The SELECT permission was denied on the object 'SalesOrderHeader', database 'AdventureWorks2008', schema 'Sales'. EXECUTE AS USER = 'John' SELECT * FROM Sales.SalesOrderHeader

No community discussion yet for this question.