Exam 70-764 topic 1 question 86 discussion

Actual exam question from Microsoft's 70-764

Question #: 86
Topic #: 1

You administer a Windows Azure SQL Database database named Human_Resources. The database contains 2 tables named Employees and SalaryDetails.
You add two Windows groups as logins for the server:
✑ CORP\Employees - All company employees
✑ CORP\HRAdmins - HR administrators only
✑ HR Administrators are also company employees.
You need to grant users access according to the following requirements:
✑ CORP\Employees should have SELECT access to the Employees table.
✑ Only users in CORP\HRAdmins should have SELECT access to the SalaryDetails table.
Logins are based only on Windows security groups.

What should you do?

A. Create a database role called Employees. Add CORP\Employees to the db_datareader role. Add all company employees except HR administrators to the Employees role. Deny SELECT access to the SalaryDetails table to the Employees role.
B. Create a database role called HRAdmins. Add all company employees except HR administrators to the db_datareader role, Add all HR administrators to the HRAdmins role. Grant SELECT access to the SalaryDetails table to the HRAdmins role. Deny SELECT access to the SalaryDetails table to the db_datareader role.
C. Create two database roles: Employees and HRAdmins. Add all company employees to the Employees role. Add HR administrators to the HRAdmins role. Grant SELECT access to all tables except SalaryDetails to the Employees role. Grant SELECT access to the SalaryDetails table to the HRAdmins role. Deny SELECT access to the SalaryDetails table to the Employees role.
D. Create a database role called Employees. Add all HR administrators to the db_datareader role. Add all company employees to the Employees role. Grant SELECT access to all tables except the SalaryDetails table to the Employees role.

Show Suggested Answer

Suggested Answer: D 🗳️

by TheSwedishGuy at Jan. 24, 2020, 3:32 p.m.

Comments

Submit Cancel

[Removed]

5 years, 3 months ago

Regarding „HR Administrators are also company employees.“ a Deny select on salarydetails will also Block HR Administrators from accessing SalaryDetails.

upvoted 4 times

Iva10

5 years ago

Yes you are right, Deny overrides any Grant. https://dba.stackexchange.com/questions/89570/deny-overrides-grant-in-sql-server

upvoted 1 times

...

Iva10

5 years, 4 months ago

D .Create a database role called Employees. Add all HR administrators to the db_datareader role. Add all company employees to the Employees role. Grant SELECT access to all tables except the SalaryDetails table to the Employees role. Deny SELECT access to the SalaryDetails table to the Employees role. In the answer is missing last Deny line.

upvoted 3 times

Zikato

4 years, 5 months ago

It doesn't since HR users are also Employees users, you would DENY them permissions to the SalaryDetails (Deny overrides grant).

upvoted 1 times

...

TheSwedishGuy

5 years, 5 months ago

D is just an elegant solution.

upvoted 3 times

...