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 of course…
D
The correct answer is A
DENY takes precedence over GRANT.
The only possible answer is A.
D
Cannot be D: DENY for Employees role will deny HR users too (Add all company employees to the Employees role, HR Administrators are also company employees)
Cannot be A either, as HR admins are members of CORP\Employees too (HR Administrators are also company employees).
Cannot be C: Deny SELECT access to the SalaryDetails table to the Employees role will also deny HR users (Add all company employees to the Employees role)
So only answer left is : B.
It’s A . Employess role doesn’t contain HR arministrators (Add all emploee EXCEPT !!)
After second reading, the right answer is A : CORP\Employees is not member of the Employees role wich is denied.
And it cannot be B: in this case HR admins can SELECT on SalaryDetails table, but not on Employees.
I don’t see where HR Admins need access to Employees
I think B is correct, but most people pick A
A
>> Logins are based only on Windows security groups.
So it can’t be A as
>> Add all company employees except HR administrators to the Employees role.
Before SQL Server 2014 GRANT takes precedent over DENY.
After Sql Server 2014 DENY takes precedent over GRANT.
A
A – Does not include GRANT SELECT rights for CORP\HRAdmins it just adds them to a role. Wrong
B – Gives exactly the permissions outlined in the question. My choice
C – DENY Employees contains everybody and therefore blocks everybody. Wrong
D – Gives correct permission but CORP\HRAdmins has additional access not mentioned in the question. Possible Answer
The permissions assigned to the fixed-database roles cannot be changed.
D is correct. A was wrong, because HR administrators didn’t assigning to any Permission. so cannot access to SalaryDetails table.
Answare: A
HR gets permission by: “Add CORP\Employees to the db_datareader role”!
and “HR Administrators are also company employees.” !!!
D wrong, because:
“Deny SELECT access to the SalaryDetails table to the Employees role. ”
and “Add all company employees to the Employees role. ” so HR gets deny too
Add all company employees except HR administrators to the Employees role…..
If it is A, how the HR people will access data :). Come on, this is a SQL admin test, not for the intelligence.
The most accurate is the D.
Deny for Employees role !
Grant for db_datareader role, which contains all users!
So answare is “A”.