DP-300 practice questions 111–120

1. Question 111 · 354 in bank

   HOTSPOT - You have an Azure SQL database named DB1 that contains a table named Orders. The Orders table contains a row for each sales order. Each sales order includes the name of the user who placed the order. You need to implement row-level security (RLS). The solution must ensure that the users can view only their respective sales orders. What should you include in the solution? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Answer is correct. Implement RLS by using the CREATE SECURITY POLICY Transact-SQL statement, and predicates created as inline table-valued functions. https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16

   Source: Adapted from community discussion — Pranava_GCP

   References

   • Microsoft Learn

   

   Next question

2. Question 112 · 354 in bank

   DRAG DROP - You have an Azure subscription that contains an Azure SQL database named SQLDb1. SQLDb1 contains a table named Table1. You plan to deploy an Azure web app named webapp1 that will export the rows in Table1 that have changed. You need to ensure that webapp1 can identify the changes to Table1. The solution must meet the following requirements: • Minimize compute times. • Minimize storage. Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   The answer is correct. To track changes, change tracking must first be enabled for the database and then enabled for the tables that you want to track within that database. 1) ALTER DATABASE Sandbox1 SET CHANGE_TRACKING = ON 2) ALTER TABLE Persons ENABLE CHANGE_TRACKING 3) run the CHANGETABLE() function

   Source: Adapted from community discussion — Pranava_GCP

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

3. Question 113 · 354 in bank

   HOTSPOT - You have an Azure subscription that is linked to an Azure Active Directory (Azure AD) tenant named contoso.com The subscription contains an Azure SQL database named SQL1 and an Azure web app named app1. App1 has the managed identity feature enabled. You need to create a new database user for app1. How should you complete the Transact-SQL statement? To answer select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Since the object is a managed identity, there's no UPN, you just give it the name of the resource ([App1] in this case). See an example with a VM MI here: https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql#create-contained-user

   Source: Adapted from community discussion — [Removed]

   References

   • Microsoft Learn

   

   Next question

4. Question 114 · 354 in bank

   DRAG DROP - You have an Azure subscription that contains an Azure SQL database named DB1. You plan to perform a classification scan of DB1 by using Azure Purview. You need to ensure that you can register DB1. Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Create a Purview account. Modify the Access control (IAM) settings for DB1. From the Purview Studio, create a scan rule set.

   Source: Adapted from community discussion — KIET2131

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

5. Question 115 · 354 in bank

   HOTSPOT - You have an Azure subscription that contains a group named Group1 and an Azure SQL managed instance that hosts a database named DB1. You need to ensure that Group1 has read access to new tables created in DB1. The solution must use the principle of least privilege. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   The question is confusing. It states "You need to ensure that Group1 has read access to new tables created in DB1. The solution must use the principle of least privilege." If you grant to schema, then it will be specific to schema only. How about if there are two schema in the database like for example dbo and user1? So, it should be GRANT SELECT ON DATABASE::TABLE...

   Source: Adapted from community discussion — Daemon69

   References

   • Microsoft Learn

   

   Next question

6. Question 116 · 354 in bank

   DRAG DROP - You have a new Azure SQL database named DB1 on an Azure SQL server named AzSQL1. The only user who was created is the server administrator. You need to create a contained database user in DB1 who will use Azure Active Directory (Azure AD) for authentication. Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Select and Place:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Step 1: Set up the Active Directory Admin for AzSQL1. Step 2: Connect to DB1 by using the server administrator. Sign into your managed instance with an Azure AD login granted with the sysadmin role. Step 3: Create a user by using the FROM EXTERNAL PROVIDER clause. FROM EXTERNAL PROVIDER is available for creating server-level Azure AD logins in SQL Database managed instance. Azure AD logins allow database-level Azure AD principals to be mapped to server-level Azure AD logins. To create an Azure AD user from an Azure AD login use the following syntax: CREATE USER [AAD_principal] FROM LOGIN [Azure AD login] Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql

   Source: Adapted from community discussion — Jas_dandiwal

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

7. Question 117 · 354 in bank

   You have an instance of SQL Server on Azure Virtual Machines named VM1. You plan to schedule a SQL Server Agent job that will rebuild indexes of the databases hosted on VM1. You need to configure the account that will be used by the agent. The solution must use the principle of least privilege. Which operating system user right should you assign to the account?

   • AIncrease scheduling priority
   • BLog on as a service
   • CProfile system performance
   • DLog on as a batch job

   Show answer

   Answer: B

   Explanation

   The "Log on as a batch job" user right is required for a user account to be able to run batch files or scripts, which is what SQL Server Agent uses to perform index rebuilds. By granting this right, the account will have sufficient privileges to perform the necessary actions, but no more than what is required for the specific task. The other user rights listed - "Increase scheduling priority", "Log on as a service", and "Profile system performance" - are not required for this particular task and would provide more privileges than necessary, violating the principle of least privilege.

   Source: Adapted from community discussion — kkkiet

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

8. Question 118 · 354 in bank

   You have an Azure subscription that contains an instance of SQL Server on an Azure virtual machine named SQLVM1 and a user named User1. SQLVM1 hosts a database named DB1. You need to ensure that User1 can create a scheduled task to perform a full backup of DB1. The solution must use the principle of least privilege. Which built-in database role should you assign to User1?

   • Adb_owner
   • BSQLAgentReaderRole
   • CSQLAgentUserRole
   • DSQLAgentOperatorRole

   Show answer

   Answer: C

   Explanation

   I just did real tests. All of these 3 roles, SQLAgentReaderRole, SQLAgentUserRole, and SQLAgentOperatorRole can NOT create a database backup if the user is NOT in the "db_backupoperator" role on the target DB. Those 3 roles only give the user to create a scheduled job but the user doesn't have the permission to create a DB backup. I got the following error: Executed as user: TESTER. The server principal "TESTER" is not able to access the database "sysdb" under the current security context. [SQLSTATE 08004] (Error 916) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. You have to assign the user the db-Backupoperate role on the target database. If the user is in the db_owner role, the user can do the database backup but the user can not set up a scheduled job if it is not in one of those 3 SQLAgent Roles. It is supprise to me. If I am wrong, please let me know. Thanks

   Source: Adapted from community discussion — U_C

   References

   • Microsoft Learn

   Next question

9. Question 119 · 354 in bank

   HOTSPOT - You have an Azure subscription that contains a logical SQL server. The server hosts two databases named db1 and db2 and an Azure AD service principal named app1. You need to ensure that app1 can access db1. The solution must use the principle of least privilege. How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Actually, rereading the question it says that the logical SQL server hosts the user app1, this means tha app1 has a login on the server. This in turn means that the best way to give app1 permissions to db1 is to create user app1 from login app1

   Source: Adapted from community discussion — BrenFa101

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

10. Question 120 · 354 in bank

    Your on-premises network contains a server that hosts a 60-TB database named DB1. The network has a 10-Mbps internet connection. You need to migrate DB1 to Azure. The solution must minimize how long it takes to migrate the database. What should you use?

    • AAzure Migrate
    • BAzure Data Box
    • CAzure Database Migration Service
    • DData Migration Assistant (DMA)

    Show answer

    Answer: B

    Explanation

    The Microsoft Azure Data Box cloud solution lets you send terabytes of data into and out of Azure in a quick, inexpensive, and reliable way. The secure data transfer is accelerated by shipping you a proprietary Data Box storage device. Each storage device has a maximum usable storage capacity of 80 TB and is transported to your datacenter through a regional carrier. The device has a rugged casing to protect and secure data during the transit.

    Source: Adapted from community discussion — Pranava_GCP

    References

    • Microsoft Learn

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-300 practice, question 111. 354 questions available.