Exam 70-765 topic 3 question 45 discussion

Actual exam question from Microsoft's 70-765

Question #: 45
Topic #: 3

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. 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

Show Suggested Answer

Suggested Answer: G 🗳️
Use SQL Data Warehouse or Parallel Data WarehouseGRANT and DENY statements to grant or deny a permission (such as UPDATE) on a securable (such as a database, table, view, etc.) to a security principal (a login, a database user, or a database role).
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-parallel-data-warehouse

by AlexandreVM at May 7, 2019, 10:07 p.m.

Comments

Submit Cancel

DudeHere

4 years, 8 months ago

Assuming the way the question is worded (DISALLOWED) and the syntax in the answers is correct (FROM), the answer would be A. There is no DENY FROM. https://www.mssqltips.com/sqlservertip/2894/understanding-grant-deny-and-revoke-in-sql-server/

upvoted 1 times

...

DudeHere

4 years, 8 months ago

Assuming the way the question is worded (DISALLOWED) and the syntax in the answers is correct (FROM), the answer would be A. There is no DENY FROM

upvoted 1 times

...

crosss

4 years, 8 months ago

Agreed with @AlexandreVM the answer is OK but there's a syntax error. The right answer is G. DENY SELECT ON Schema::Customers FROM UserA (but replacing "FROM" by "TO") like this : DENY SELECT ON Schema::Customers TO UserA

upvoted 2 times

axdev

4 years, 4 months ago

I agree. I believe "FROM" is a typo and the correct answer is G. All other options either incorrect or do not make sense.

upvoted 1 times

...

STH

5 years, 3 months ago

It is not asked to DENY definitively all SELECT autorisation to UserA but only to REVOKE those currently granted

upvoted 2 times

STH

5 years, 3 months ago

So answer is A

upvoted 3 times

...

AlexandreVM

5 years, 7 months ago

TO principal [ ,...n ] One or more principals being granted, denied, or revoked permissions. Principal is the name of a login, database user, or database role. FROM principal [ ,...n ] One or more principals to revoke permissions from. Principal is the name of a login, database user, or database role. FROM can only be used with a REVOKE statement. TO can be used with GRANT, DENY, or REVOKE. https://docs.microsoft.com/pt-br/sql/t-sql/statements/deny-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-parallel-data-warehouse?view=aps-pdw-2016-au7

upvoted 2 times

...

Exam 70-765 All Questions

View all questions & answers for the 70-765 exam

Exam 70-765 topic 3 question 45 discussion

Comments

DudeHere

DudeHere

crosss

axdev

STH

STH

AlexandreVM

SY0-701