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.
Deny SELECT access to the SalaryDetails table to the Employees role.
A
Remember that Deny overrides grant.
B is wrong. You cannot grant, deny or revoke permissions to or from special roles.
See comments on previous version of this dump
http://www.aiotestking.com/microsoft/you-need-to-grant-users-access-according-to-the-following-requirements-corpemployees-should-have-select-access-to-the-employees-table/
0
0
Anyway not A, because the employee role had no members.
0
0
This question still appears flawed, because although (A) is the only working solution (demonstrated by T-SQL in the link provided by Shak above), it also utilises adding individuals – ‘all company employees except HR administrators’.
The question states ‘Logins are based only on Windows security groups’ so when all the individual users are created and assigned to a role at the database level, which logins will they be mapped to?
There seems to be two possibilities:
– The question as we have it here is not an accurate copy
– The question itself is broken
0
0
This is a bad question, but A is clearly the answer. Both employees and HR employees are in the corp/employees login which is in the db_datareader role that lets them read everything. Non HR employees are also in the employee role which is explicitly denied access to salaries. deny is stronger than grant so now they can read everything except salaries.
Also, you only know that HR is in the corp/employees login and the db_datareader role because: HR Administrators are also company employees.
Well of course they are. They aren’t consultants or part timers. The test really meant they are in the corp/employees login in the stupidest manner possible because if the IT people writing this test had real English writing skills they’d be authors or journalists. They certainly aren’t programmers.
0
0
D
0
0
70-461 question i guess
0
1
A
0
0
Question is if HR Admins are members of Employees group as well. If so then A will work. IF not than D.
0
0
D
0
0
Sorry, A works.
0
0