QUESTION: 1
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 Transact-SQL statement should you use?

A. DROP USER John;
B. DENY SELECT ON Sales.SalesOrder TO John;
C. GRANT DELETE ON Sales.SalesOrder TO John;
D. REVOKE SELECT ON Sales.SalesOrder FROM John;

Answer(s): B
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
wil 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 wil 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
GO
USE [AdventureWorks2008]
GO
CREATE USER [John] FOR LOGIN [John]
WITH DEFAULT_SCHEMA = [dbo]
GO
-- 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
REVERT
-- Revoke permisson for the table from him
REVOKE SELECT ON Sales.SalesOrderHeader FROM [John]
-- He stil sees data
EXECUTE AS USER = 'John'
SELECT * FROM Sales.SalesOrderHeader
REVERT
-- 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.

2

Free 70-433 Exam Braindumps

Pass your TS: Microsoft SQL Server 2008, Database Development (RETIRED) exam with these free Questions and Answers