View Full Microsoft DP-300 Exam Dumps and Practice Test Dumps

 

Question 381. Which Query Store feature allows an administrator to associate a query with a specific execution plan?

1) Plan forcing
2) Data masking
3) Row-level security
4) Backup compression

Answer: 1) Plan forcing

Explanation:

Query Store plan forcing allows an administrator to instruct the query optimizer to use a selected execution plan for a particular query. This can be useful when a query has experienced a performance regression after a plan change. Before forcing a plan, administrators should investigate the workload and understand why the optimizer selected the newer plan. Plan forcing is a performance-management capability and does not permanently prevent future plan changes in every circumstance. Data masking, row-level security, and backup compression address data protection or storage rather than execution-plan management.

Question 382. Which stored procedure can be used to configure a Query Store hint for a query?

1) sp_query_store_set_hints
2) sp_addrolemember
3) sp_configure_backup
4) sp_database_firewall

Answer: 1) sp_query_store_set_hints

Explanation:

The sp_query_store_set_hints stored procedure is used to apply a Query Store hint to a specific query. Query Store hints allow administrators to influence optimizer behavior without modifying the application code or directly changing the query text. Depending on the supported hint, administrators can address certain performance scenarios by controlling selected query-optimization behaviors. Query Store hints should be applied carefully because inappropriate hints can negatively affect performance. The other procedures listed are not valid mechanisms for configuring Query Store hints. Query Store provides the historical information needed to identify queries that may require investigation.

Question 383. What is a common symptom of parameter sniffing in SQL Server?

1) A query performs differently depending on the parameter values used
2) A database cannot be backed up
3) A firewall rule disappears
4) Encryption keys are automatically deleted

Answer: 1) A query performs differently depending on the parameter values used

Explanation:

Parameter sniffing occurs when SQL Server creates an execution plan based on parameter values encountered during compilation and later reuses that plan for different parameter values. If data distribution is uneven, a plan that works efficiently for one parameter value may perform poorly for another. This can result in inconsistent query performance. Administrators can investigate execution plans, Query Store history, statistics, and workload characteristics when diagnosing the issue. Parameter sniffing is a query-optimization behavior rather than a backup, networking, or encryption problem. Possible remedies depend on the specific workload and should be tested carefully.

Question 384. Which SQL Server option can reduce compilation overhead for workloads that execute many single-use ad hoc queries?

1) Optimize for ad hoc workloads
2) Transparent Data Encryption
3) Database Mail
4) Row-Level Security

Answer: 1) Optimize for ad hoc workloads

Explanation:

The optimize for ad hoc workloads configuration option can help reduce the memory used by cached plans for workloads containing many single-use ad hoc queries. When enabled, SQL Server can initially store a smaller compiled-plan stub instead of the complete plan for certain ad hoc queries. If the query is executed again, SQL Server can then store the full compiled plan. This can be useful in environments where plan cache memory is being consumed by large numbers of single-use statements. The option does not control encryption, email notifications, or row-level access restrictions.

Question 385. What is the primary purpose of a plan guide in SQL Server?

1) To influence query optimization without changing the query text
2) To create database backups
3) To configure Azure firewall rules
4) To encrypt transaction logs

Answer: 1) To influence query optimization without changing the query text

Explanation:

A plan guide allows administrators to influence how SQL Server optimizes a query without modifying the application’s query text. It can associate hints or other optimization behavior with matching queries, which can be useful when changing application code is difficult or impractical. Plan guides should be used carefully because they can affect execution-plan selection and may become unnecessary after application or database changes. Administrators should monitor their effectiveness over time. Plan guides are unrelated to backup creation, Azure firewall configuration, or transaction-log encryption.

Question 386. Which technique can help prevent excessive recompilation of a stored procedure when appropriate?

1) Appropriate plan and procedure design
2) Disabling all indexes
3) Removing all statistics
4) Disabling transaction logging

Answer: 1) Appropriate plan and procedure design

Explanation:

Stored procedure recompilation can occur for several legitimate reasons, but unnecessary recompilation may increase CPU usage because SQL Server must repeatedly compile execution plans. Administrators can investigate recompilation causes through performance monitoring and execution-plan analysis. Appropriate procedure design, stable parameter usage, suitable indexing, and carefully selected recompilation options can help manage this behavior. Recompilation is not inherently harmful because a new plan can sometimes be beneficial when data or workload characteristics change. Disabling indexes, removing statistics, or disabling transaction logging are not appropriate general solutions and can create other performance or recovery problems.

Question 387. What is ownership chaining in SQL Server?

1) A mechanism that can allow access through related objects without separate permissions on every underlying object
2) A backup retention policy
3) A database encryption method
4) A network routing mechanism

Answer: 1) A mechanism that can allow access through related objects without separate permissions on every underlying object

Explanation:

Ownership chaining occurs when SQL Server objects such as stored procedures and tables have the same owner and a permission check can be avoided on the referenced object as part of the chain. This can simplify application security because users may receive permission to execute a stored procedure without receiving direct permissions on every table accessed by that procedure. Ownership chaining must be designed carefully because it affects how permissions flow between objects. It is not a backup, encryption, or network-routing mechanism. Understanding ownership relationships is important when implementing least-privilege database security.

Question 388. What is the purpose of an application role in SQL Server?

1) To provide permissions based on an application-defined security context
2) To automatically rebuild indexes
3) To create database backups
4) To configure Azure regions

Answer: 1) To provide permissions based on an application-defined security context

Explanation:

An application role allows a database application to activate a specific database security context and use the permissions assigned to that role. This can help applications enforce a defined set of database permissions independently of the permissions associated with the individual user’s normal database access. Application roles should be designed carefully because activating one changes the security context used by subsequent database operations within the connection. They are useful when applications need controlled access to database resources. Application roles do not perform backup, index maintenance, or Azure-region configuration tasks.

Question 389. Which statement correctly describes a database role?

1) It groups database principals so permissions can be managed collectively
2) It automatically creates database backups
3) It represents a physical database server
4) It stores transaction log files

Answer: 1) It groups database principals so permissions can be managed collectively

Explanation:

A database role is a security principal that can contain users or other database principals and receive permissions as a group. Instead of granting the same permissions individually to many users, administrators can assign permissions to a role and then add appropriate users to that role. This simplifies permission management and supports the principle of least privilege when roles are carefully designed. Database roles are logical security objects and do not represent physical servers or storage structures. They also do not automatically create backups or store transaction logs.

Question 390. What is a securable in SQL Server?

1) A resource to which permissions can be granted or denied
2) A backup compression algorithm
3) A database monitoring alert
4) A physical network cable

Answer: 1) A resource to which permissions can be granted or denied

Explanation:

A securable is a SQL Server resource that can be protected through the permission system. Examples include databases, schemas, tables, views, stored procedures, and other objects. Administrators grant, deny, or revoke permissions on securables to control which principals can perform specific actions. Understanding securables is essential when designing database security because permissions can be assigned at different levels of the object hierarchy. Securables are unrelated to backup compression or physical networking. Effective security administration requires identifying the appropriate securable and granting only the permissions necessary for the user’s or application’s responsibilities.

Question 391. Which permission statement grants users SELECT access to all current and future tables within a schema?

1) GRANT SELECT ON SCHEMA::Sales TO User1
2) GRANT BACKUP ON DATABASE::Sales TO User1
3) GRANT SELECT ON SERVER::Sales TO User1
4) GRANT TABLE ON DATABASE::Sales TO User1

Answer: 1) GRANT SELECT ON SCHEMA::Sales TO User1

Explanation:

Granting SELECT permission on a schema provides access to the appropriate securables within that schema, including tables and views to which the permission applies. Schema-level permissions can simplify administration when many objects require consistent access controls. They can also apply to objects added to the schema later, reducing the need to grant permissions individually each time a new object is created. Administrators should still ensure that the schema contains only objects appropriate for the intended users. The other statements use invalid or inappropriate permission syntax and do not provide the required schema-level access.

Question 392. What is cross-database ownership chaining used for?

1) It can allow permissions to flow between objects in different databases under supported ownership conditions
2) It automatically copies databases to another region
3) It encrypts every database backup
4) It controls Azure DNS records

Answer: 1) It can allow permissions to flow between objects in different databases under supported ownership conditions

Explanation:

Cross-database ownership chaining extends ownership-chaining behavior across database boundaries under supported conditions. When ownership and security requirements are satisfied, a user executing an object in one database may access referenced objects in another database without receiving direct permissions on those underlying objects. Because this behavior can affect security boundaries, administrators should evaluate whether it is appropriate for the environment. It should not be enabled or relied upon without understanding the security implications. Cross-database ownership chaining does not replicate databases, encrypt backups, or manage Azure DNS.

Question 393. Which authentication method uses a SQL Server-defined username and password?

1) SQL authentication
2) Microsoft Entra authentication
3) Certificate-only authentication
4) Azure Storage authentication

Answer: 1) SQL authentication

Explanation:

SQL authentication uses a SQL Server login configured with a username and password managed by SQL Server. It differs from Microsoft Entra authentication, where identity management is handled through Microsoft Entra ID. SQL authentication can be useful for applications or environments where SQL logins are required, but administrators should protect credentials carefully and follow organizational security policies. Strong passwords, appropriate permissions, secure connection settings, and monitoring remain important. SQL authentication is an identity mechanism and does not determine database backup behavior, encryption settings, or network routing.

Question 394. What is the role of the Microsoft Entra administrator for an Azure SQL logical server?

1) It establishes an initial Microsoft Entra administrative identity for the server
2) It automatically creates all database indexes
3) It controls backup compression
4) It configures storage redundancy for every database

Answer: 1) It establishes an initial Microsoft Entra administrative identity for the server

Explanation:

The Microsoft Entra administrator provides an identity that can administer Azure SQL resources using Microsoft Entra authentication. Configuring this administrator is an important step when organizations want to use Microsoft Entra-based access rather than relying only on SQL authentication. After authentication is established, database administrators can create appropriate Microsoft Entra users or principals and assign permissions according to organizational requirements. The Microsoft Entra administrator setting does not automatically manage indexes, backup compression, or storage redundancy. Those functions are handled through separate database-management and Azure configuration capabilities.

Question 395. Which identity is commonly used by an Azure-hosted application when passwordless access to Azure SQL is required?

1) Managed identity
2) FTP account
3) Local printer account
4) Anonymous user

Answer: 1) Managed identity

Explanation:

A managed identity provides an Azure-managed identity that applications can use to authenticate to supported Azure services without storing credentials such as passwords or client secrets in application configuration. For Azure SQL scenarios, a managed identity can be granted appropriate database permissions and used by an application to obtain an authentication token. This approach can reduce credential-management overhead and improve security when configured correctly. Administrators still need to create the appropriate database principal and permissions. Managed identities are different from FTP accounts, local system accounts, and anonymous access.

Question 396. Which Azure SQL networking feature provides a private IP address for accessing a database through an Azure virtual network?

1) Private endpoint
2) Public DNS record only
3) Database role
4) Query Store

Answer: 1) Private endpoint

Explanation:

An Azure Private Endpoint provides a private network interface with a private IP address for accessing supported Azure services through an Azure virtual network. For Azure SQL Database, this can allow clients in connected networks to communicate with the database service without relying on a public endpoint for the database connection path. Private endpoints are commonly used with private DNS configuration so that the service hostname resolves appropriately within the private network. A database role controls permissions, Query Store manages query-performance history, and public DNS alone does not create a private network connection.

Question 397. What is the purpose of a private DNS zone when using an Azure SQL private endpoint?

1) To resolve the database service name to the private endpoint address
2) To create SQL Server logins
3) To encrypt transaction logs
4) To rebuild database indexes

Answer: 1) To resolve the database service name to the private endpoint address

Explanation:

A private DNS zone can provide name resolution for resources accessed through private endpoints. When configured correctly, the database service hostname can resolve to the private IP address associated with the private endpoint rather than directing clients toward a public endpoint. This helps applications continue using the expected service hostname while network traffic follows the private connectivity path. DNS configuration is therefore an important part of private-endpoint deployments. Private DNS does not create database users, encrypt transaction logs, or perform index maintenance; those functions belong to separate database services and security mechanisms.

Question 398. What is a key difference between an Azure SQL private endpoint and a service endpoint?

1) A private endpoint provides a private IP address for the Azure service connection
2) A service endpoint creates a database user automatically
3) A private endpoint disables authentication
4) A service endpoint encrypts all database columns

Answer: 1) A private endpoint provides a private IP address for the Azure service connection

Explanation:

A private endpoint uses a private IP address from an Azure virtual network to provide private connectivity to a supported Azure service. A service endpoint, by contrast, extends the identity of a virtual network to the Azure service through Azure networking while the service continues to use its service endpoint addressing model. The two approaches have different networking characteristics and security designs. Administrators should choose based on connectivity, network architecture, and security requirements. Neither networking feature automatically creates database users or replaces database-level authentication and authorization controls.

Question 399. Which feature can help restrict Azure SQL Database access based on client network addresses?

1) Firewall rules
2) Query Store
3) Column statistics
4) Database triggers

Answer: 1) Firewall rules

Explanation:

Azure SQL Database firewall rules control which client network addresses are permitted to establish connections to the logical server or database, depending on the supported firewall configuration. Administrators can define allowed IP address ranges according to the organization’s connectivity requirements. Firewall rules are a network-level security control and should be combined with authentication and authorization rather than treated as a replacement for them. Query Store focuses on query performance, statistics support optimization, and triggers automate database responses to data events. These features do not provide the same network access-control function.

Question 400. Which security principle recommends granting users only the permissions required to perform their assigned tasks?

1) Principle of least privilege
2) Maximum privilege principle
3) Open access principle
4) Unlimited delegation principle

Answer: 1) Principle of least privilege

Explanation:

The principle of least privilege recommends giving users, applications, and services only the permissions necessary to perform their required tasks. In SQL Server and Azure SQL environments, administrators can implement this principle through database roles, object-level permissions, schema-level permissions, Microsoft Entra identities, and carefully controlled administrative roles. Limiting unnecessary privileges reduces the potential impact of compromised credentials or accidental operations. Least privilege should be reviewed periodically as responsibilities change. It works alongside other controls such as authentication, encryption, auditing, network restrictions, and monitoring to create a layered database security approach.