DRAG DROP
You administer a database named SalesDb that has users named UserA, userB, and UserC. You need
to ensure that the following requirements are met:
• UserA must be able to provide Windows logins access to the database.
• UserB must be able to select, update, delete, and insert data to the database tables.
• UserC must be able to create new tables and stored procedures.
You need to achieve this goal by granting only the minimum permissions required. To which fixed
database role or roles should you add the users? (To answer, drag the appropriate use role or roles.
Answer choices may be used once, more than once, or not at all. Answer targets may be
additionally, you may need to drag the split bar between panes or scroll to view content.)
Answer: See the explanation.
Explanation:
Box 1:
db_ddladmin: UserC
UserC needs to be able to add tables and procedures. He can do these through the db_ddladmin role
(through the create statement).
Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL)
command in a database.
DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of
database objects in database.
Examples: CREATE, ALTER, DROP statements
Box 2:
db_datareader: UserB
UserB needs to be able to select data.
Members of the db_datareader fixed database role can run a SELECT statement against any table or
view in the database.
Box 3:
db_datawriter: UserB
UserB needs to update, delete, and insert data. He can do these through the db_datawriter role.
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
Box 4:
db_owner: <leave blank>
Not required in this scenario.
Members of the db_owner fixed database role can perform all configuration and maintenance
activities on the database.
Box 5:
db_acccessadmin: UserA
This enables UserA to provide Windows logins access.
Members of the db_accessadmin fixed database role can add or remove access for Windows logins,
Windows groups, and SQL Server logins.