James works as an Administrator for Softech Inc. The company has a SQL Server 2005 computer
named SQL1. The company has six departments; Accounting, Sales, Marketing, Manufacturing,
Production, and Research. The user accounts of each department are located in a domain group
named after the department. James wants to create a separate database on SQL1 for each
epartment which will store the department related information. Users in each department must be
able to read and update data in the database belonging to their respective departments. No user
must be able to access the database belonging to another department. James wants to configure
access to the database to meet these requirements. What should James do to accomplish the task?

A.
James should 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_ddladmin database role.

B.
James should 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.

C.
James should 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_ddladmin database role.

D.
James should 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.

Explanation:
In order to accomplish the task James should 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.
As the criterion in the question states that the users of each department belong to a group named
after that department. These groups can
be used to configure access to the database. Each department user should be assigned
db_datareader and db_datawriter database roles for
their respective databases. This will prevent a user in one department from accessing another
department’s database.
Answer D is incorrect. Create a Windows Authentication login for each domain user, and configure
it as a database user for the
appropriate database. Then 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 when
compared to that created for each domain user.
Answer A is incorrect. James could use the domain group to configure access to the database but
the database user should not be
added to the db_ddladmin database role. The db_ddladmin is a fixed database role. A user who is a
member of this database role can add,
modify, or drop objects at the database level but cannot issue the GRANT, REVOKE, or DENY
statements. The db_ddladmin role exists in each
database. As the scenario in the question states that the user can only read and update the data
present in database, thus, assigning user
to the db_ddladmin database role will not serve the purpose.
Answer C is incorrect. James should not create a Windows Authentication login for each user, but
for each group as this task will be
accomplished with least administrative effort. Moreover, the database should be added to the
db_datareader and db_datawriter database
roles as they have to only read and update the data present in the database.