You work as the database administrator at Domain.com. Domain.com has five departments named Accounting, Sales, Marketing, Research and Manufacturing. The Domain.com user accounts of each department are located in a domain group named after the department. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run on Windows Server2003. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01.
You have been instructed to create a separate database on Certkiller -DB01 for each department. Each Domain.com department will use their database to store department-related data. Users in each department must be able to read and update data in the database belonging to their department. No users must be able to access the database belonging to another department.
You need to configure access to the databases to meet these requirements. You want to use the least amount of administrative effort in accomplishing this task.
What should you do?
A.
Create a Windows Authentication login for each domain group and configure the logins as database users for the appropriate database. Add each database user to the db_datareader and db_datawriter database roles.
B.
Create a Windows Authentication login for each domain user and configure the logins as database users for the appropriate database. Add each database user to the db_datareader and db_datawriter database roles.
C.
Create a Windows Authentication login for each domain group and configure the logins as database users for the appropriate database. Add each database user to the db_ddladmins database role.
D.
Create a Windows Authentication login for each domain user and configure the logins as database users for the appropriate database. Add each database user to the db_ddladmins database role.
Explanation:
The users of each department belong to a group named after the department. You can use these groups to configure access to the databases. Each database user should be added to the db_datareader and db_datawriter database roles for their respective databases. This will prevent users in one department from accessing another department’s database.
Incorrect Answers:
B:
You could create a Windows Authentication login for each domain user, configure the logins as database users for the appropriate database, and add each database user to the db_datareader and db_datawriter database roles but it would require less administrative effort to create a Windows Authentication login for each domain group.
C: You can use the domain groups to configure access to the databases but you should not add each database user to the db_ddladmins database role as this violates the principle of least privilege.
D: You could create a Windows Authentication login for each domain user but it would require less administrative effort to create a Windows Authentication login for each domain group. Furthermore, you should not add each database user to the db_ddladmins database role as this violates the principle of least privilege.