BenchMark Quizzes by MDFT Pro

Allow Users to Create Database Objects Without Modifying Existing Ones

You work for MDFT Pro, a well-known training agency that provides hands-on Microsoft certification courses. Mark, the Learning Platform Manager, has set up an Azure SQL database for a practical SQL training course where students learn to design and create database objects. Each student needs the ability to practice creating their own tables, views, and stored procedures as part of the course exercises. However, Mark wants to ensure that students cannot accidentally modify or delete the pre-built course materials and sample objects that have been carefully prepared by the instructors.

You need to recommend a solution that ensures students can create their own database objects but are prevented from modifying any existing database objects created by the instructors.

What should you include in the recommendation?

Choose the correct answer from the options below.

Explanations for each answer:

For each student, grant ddl_admin to the existing schema is incorrect. Granting ddl_admin on the existing schema would allow students to modify existing objects, which violates the requirement.
For each student, create an additional schema and grant ddl_admin to the new schema is correct. Creating a separate schema for each student and granting ddl_admin only to that schema allows them to create objects without being able to modify existing objects in other schemas.
For each student, create an additional schema and grant db_writer to the new schema is incorrect. The db_writer role allows data modification but not object creation. You need ddl_admin for creating database objects.
For each student, grant db_writer to the existing schema is incorrect. The db_writer role allows data modification but not object creation, and doesn't prevent modification of existing objects.

Learn more about database-level roles:

Database-Level Roles and Schemas

Practice Exam

Allow Users to Create Database Objects Without Modifying Existing Ones

Discuss this question on social media: