70-028 Administering Microsoft SQL Server 7.0 Training Course

Demo Question 7.

Anita, Carmen, and Philippe will administer different aspects of the SQL server Finance database. Currently they are all in the db_owner role. Philippe must remain the database owner. Anita must only be able to set up user accounts and grant them permissions. Carmen must only be able to create tables, views, and stored procedures that are needed in the database. Which two changes should you make to meet these requirements? (Choose two)

A. Remove Carmen from the db_owner role, and add her to the db_accessadmin role
B. Remove Carmen from the db_owner role, and add her to the db_ddladmin role
C. Add Anita to the sysadmin role and to the db_accessadmin role
D. Remove Anita from the db_owner role, and add her to only the db_securityadmin role
E. Remove Anita from the db_owner role, and add her to the db_securityadmin role and the db_accessadmin


Display Answer

Answer: B, E.

Explanation: Carmen and Anita should first be removed from the db_owner role which grants all permissions to the database. B. By adding Carmen to the db_ddladmin role she would be able to issue all Data Definition Language (DDL) statements such as creating tables, views, indexes and other objects. She would not be able to issue GRANT, REVOKE, or DENY statements. E. Members of the db_securityadmin role can manage all permissions, object ownerships, roles and role memberships. Members of the db_accessadmin role can add or remove user IDs. By adding Anita to these two roles she would able to create users accounts and grant permissions for users. Incorrect