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.
I think B is a better answer…
In D, since HRAdmins are also employees, their access to the SalaryDetails Table will also be Denied through the Employees Role since ALL employees were added to that role… right?
Not for me, in B aswer HR administrarors have GRANT SELECT only on SalaryDetails table, and not on all Employees table.
I think that D is correct.
But Hr can access to all other tables through the employee because they are employee also
so b is the best answer
No, B can’t be correct since you are adding all company employees “except HR administrators” to the db_datareader, therefore even though the HR Admins are employees from business point of view, they won’t be included in db_datareader role from SQL point of view. They will only get SELECT access to the SalaryDetails table.
There is no correct answer in this question.
C is correct. Its the only one one that fully addresses all the requirements.
agreed
The Main Problem hear is that the HR Department is a group of the employee department
and we want to deny all the employee department from SELECT the Salary table
So if we deny all the employee from SELECTING the salary table then the HR (Because they are Employee also will be Denied)
So the solution is to make two Groups
1- All Employee Except the HR and Grant then the SELECT to all the tables and Deny them from the Salary Table
2- The HR Department and Grant them the SELECT to all the tables including the SALARY table
then the Answer Number B is the Correct one
But the Problem in this case is that the HR_Role can not access to the other Tables
So i think all the answers are wrong
In a,c,d if we Deny the Employee Role then science the Hr Employee are also Employee then they will also denied
In B the Hr will not be denied from access the Salary table but they will not access all other table science they grant the access only to the salary table
But Hr employee are group of the employee also so B is the best answer
Do not assume that since all HR Admins are employees, they’ll be added to the db_datareader role. B clearly says that add all except the HR administrators to the db_datareader role. So HR Admins won’t be able to read from any other table. B is not correct either. So are A,C and D. I’ll stop spending more time on this question then.
B is a sh!ty, non-practal solution, but it’s the only one that works: as Mohamed says, the other solutions are denying HRAdmins access to SalaryDetails. Since, there’s no “must minimize administrative work”, requirement, then it safe to go for it.
B is wrong. You cannot grant, deny or revoke permissions to or from special roles.
The only answer that meets security requirements is A.
C is the closest. All the answers are wrong because all of them contain the DENY command which should be avoided as much as possible because it will destroy all required permissions on HRAdmins due to the employee nature of HRAdmins. C is the right one if we take out the Deny.
C would also work if it was add all employees except hradmins to employee role.
D is the correct answer. Why?
Create a database role called Employees. –> For employees
Add all HR administrators to the db_datareader role. –> it can read all
Add all company employees to the Employees role. –> add to employee group
Grant SELECT access to all tables except the SalaryDetails table to the Employees role. –> it can read all
Deny SELECT access to the SalaryDetails table to the Employees role. –> Employee Role cannot read Salary Details now.
“HR Administrators are also company employees” . If you “Deny SELECT access to the SalaryDetails table to the Employees role” then you deny HR Administrators to select from SalaryDetails table.
So exactly which is the correct answer? I am confused and have been looking at the screen for the last 2 hours. Pft.
Am sorry to tell you this…but non of you are write…the correct answer is A…if you need prove try this
–A
use master
CREATE LOGIN EMP1 WITH PASSWORD = ‘Pass@2123’;
CREATE LOGIN EMP2 WITH PASSWORD = ‘Pass@2123’;
CREATE LOGIN MGR1 WITH PASSWORD = ‘Pass@2123’;
GO
use SalesDB
CREATE USER EMP1 FOR LOGIN EMP1;–Employees
CREATE USER EMP2 FOR LOGIN EMP2;–Employees
CREATE USER MGR1 FOR LOGIN MGR1;–HR Administrators
–Create a database role called Employees.
CREATE ROLE Employees;
–Add CORP\Employees to the db_datareader role.
–CORP\Employees – All company employees –HR Administrators are also company employees.
EXEC sp_addrolemember ‘db_datareader’, “EMP1”;
EXEC sp_addrolemember ‘db_datareader’, “EMP2”;
EXEC sp_addrolemember ‘db_datareader’, “MGR1”;
–Add all company employees except HR administrators to the Employees role.
EXEC sp_addrolemember ‘Employees’, “EMP1”;
EXEC sp_addrolemember ‘Employees’, “EMP2”;
–Deny SELECT access to the SalaryDetails table to the Employees role.
Deny SELECT ON OBJECT::SalaryDetails TO Employees;
EXECUTE AS LOGIN = ‘MGR1’;
GO
Select * from SalaryDetails;
GO
REVERT;
GO
Dereje, you’re correct. “A” is the correct answer. I have double checked.
CORP\Employees – All company employees
CORP\HRAdmins – HR administrators only
How using the above 2 groups, do you add everyone but HR?
I also believe A is correct
A is correct:
Create a database role called Employees. (everybody is in here)
Add CORP\Employees to the db_datareader role. (everybody has access to all tables)
Add all company employees except HR administrators to the Employees role. (this is the deny_group_Employees)
Deny SELECT access to the SalaryDetails table to the Employees role. (deny the deny_group_employees access to specific table)
not correct
where are the people from HR getting access to SalaryDetails table? they will not have access to SalaryDetails table because not provided.
all answers are wrong
A is correct.
It’s much clearer when you modify the answer and change the misleading name of the role from Employees to EmployeesWithoutHR:
“Create a database role called EmployeesWithoutHR.
Add CORP\Employees to the db_datareader role. –All employees can read everything
Add all company employees except HR administrators to the EmployeesWithoutHR role. –The role does not contain all employees as hr is not contained
Deny SELECT access to the SalaryDetails table to the EmployeesWithoutHR role. –deny access for everybody but hr”
They say it is A:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/135d0dcd-5314-41d8-b37e-a2cc0048b323/do-you-think-this-sql-server-exam-answer-is-correct?forum=sqlsecurity