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

 

Question 261. Which SQL Server feature allows administrators to group users and assign permissions to the group?

1) Database roles
2) Query Store
3) Statistics
4) Indexes

Answer: 1) Database roles

Explanation:

Database roles provide a convenient way to organize database principals and manage permissions collectively. Instead of granting the same permissions separately to many users, an administrator can create or use an appropriate database role, assign the required permissions to that role, and then add users or other principals as members. This simplifies security administration and supports the principle of least privilege. Roles can be designed around job responsibilities, such as read-only access or data modification. Administrators should periodically review role memberships to ensure that users retain only the access required for their current responsibilities.

Question 262. Which permission allows a principal to read data from a table?

1) SELECT
2) UPDATE
3) EXECUTE
4) ALTER

Answer: 1) SELECT

Explanation:

The SELECT permission allows a database principal to retrieve data from supported database objects such as tables and views. Administrators can grant SELECT at different scopes depending on the security requirements, including individual objects, schemas, or broader database-level permissions. Granting SELECT does not automatically allow the principal to modify the data. Separate permissions such as INSERT, UPDATE, and DELETE control modification operations. Using narrowly scoped SELECT permissions can help implement least privilege, particularly when users need access to only specific tables or views rather than the entire database.

Question 263. Which permission allows a principal to modify existing rows in a table?

1) UPDATE
2) SELECT
3) CONNECT
4) VIEW DEFINITION

Answer: 1) UPDATE

Explanation:

The UPDATE permission allows a principal to modify existing data in rows of an object when the applicable permission has been granted. It is separate from SELECT, INSERT, and DELETE permissions, allowing administrators to control different types of data access independently. For example, an application may be permitted to read and update customer records without necessarily being allowed to delete them. Administrators should grant UPDATE only where required and should consider whether permissions need to be limited to specific columns or objects. Properly scoped permissions help reduce accidental or unauthorized modifications.

Question 264. Which permission allows a principal to add new rows to a table?

1) INSERT
2) UPDATE
3) SELECT
4) EXECUTE

Answer: 1) INSERT

Explanation:

The INSERT permission allows a database principal to add new rows to a table or another supported database object. This permission controls data creation and is distinct from SELECT, UPDATE, and DELETE. Administrators can grant INSERT at an appropriate scope based on application and user requirements. In some scenarios, column-level permissions may also be appropriate when only particular columns should be supplied by a principal. Database constraints, triggers, and other rules can still affect whether an INSERT succeeds. Granting INSERT should therefore be combined with appropriate data validation and security controls.

Question 265. Which permission allows a principal to remove rows from a table?

1) DELETE
2) INSERT
3) SELECT
4) REFERENCES

Answer: 1) DELETE

Explanation:

The DELETE permission allows a principal to remove rows from a table when the applicable authorization has been granted. Because deleting data can have significant consequences, administrators should grant DELETE carefully and only to users or applications that genuinely require it. Referential integrity constraints may also affect whether a deletion is allowed, particularly when related rows exist in other tables. In some applications, deletion may instead be controlled through stored procedures or business logic. Using roles and narrowly scoped permissions can help ensure that only authorized principals can perform destructive database operations.

Question 266. Which SQL Server statement is used to give a database principal a permission?

1) GRANT
2) REVOKE
3) DENY
4) REMOVE

Answer: 1) GRANT

Explanation:

The GRANT statement is used to assign permissions to database principals. Administrators can use GRANT for permissions such as SELECT, INSERT, UPDATE, DELETE, EXECUTE, and other supported database permissions. Permissions can be assigned to users, roles, or other principals depending on the security design. GRANT is an important part of implementing controlled database access because it explicitly defines what actions a principal is allowed to perform. Administrators should grant only the permissions required for the user’s responsibilities and should avoid unnecessarily broad permissions that could increase security risk.

Question 267. Which SQL Server statement removes a previously granted permission while allowing another applicable permission to remain?

1) REVOKE
2) DROP
3) DELETE
4) CLEAR

Answer: 1) REVOKE

Explanation:

The REVOKE statement removes a previously granted or otherwise applicable permission from a principal, subject to SQL Server permission rules. It is different from DENY, which explicitly prevents a principal from receiving a permission through certain permission paths. REVOKE is useful when an administrator wants to remove a permission that is no longer required. Security administrators should understand the source of effective permissions before making changes because a principal may receive access through membership in another role. Reviewing effective permissions helps ensure that revoking one permission actually produces the intended security result.

Question 268. Which SQL Server statement explicitly prevents a principal from performing a specified action?

1) DENY
2) GRANT
3) REVOKE
4) ENABLE

Answer: 1) DENY

Explanation:

The DENY statement explicitly prevents a principal from performing a specified database action, subject to SQL Server permission semantics and exceptions. It is different from REVOKE because REVOKE removes a permission assignment rather than explicitly prohibiting the permission. DENY can be useful when an administrator needs to prevent access even though a principal might otherwise inherit permissions through another path. However, administrators should use DENY carefully because it can make permission troubleshooting more complicated. A well-designed role structure with appropriately scoped GRANT permissions is often easier to maintain and understand.

Question 269. Which database-level role normally provides permission to read all user data in a database?

1) db_datareader
2) db_datawriter
3) db_ddladmin
4) db_denydatareader

Answer: 1) db_datareader

Explanation:

The db_datareader fixed database role provides members with permission to read data from all user tables and views in the database, subject to applicable permission behavior. It is useful when a user or application genuinely requires broad read access. However, membership should not be granted automatically simply because it is convenient. If an application needs access to only a small number of objects, more narrowly scoped permissions or custom roles may better support least privilege. Administrators should regularly review role memberships because broad database roles can provide substantially more access than an individual task may require.

Question 270. Which database-level role normally provides permission to modify data in all user tables?

1) db_datawriter
2) db_datareader
3) db_securityadmin
4) db_denydatawriter

Answer: 1) db_datawriter

Explanation:

The db_datawriter fixed database role provides members with permissions to insert, update, and delete data in all user tables in the database, subject to applicable SQL Server permission behavior. This role can be useful for applications that genuinely need broad data modification capabilities. However, granting membership to this role may provide more access than necessary for many workloads. Administrators should consider custom database roles or object-specific permissions when an application only needs to modify selected tables. Limiting write access can reduce the possibility of accidental changes and helps support a least-privilege security model.

Question 271. Which SQL Server database role is intended to allow members to create and alter database objects?

1) db_ddladmin
2) db_datareader
3) db_datawriter
4) db_denydatareader

Answer: 1) db_ddladmin

Explanation:

The db_ddladmin fixed database role is associated with permissions for database definition language operations. Members can perform certain actions involving database objects, such as creating or modifying objects, according to the permissions associated with the role. Because DDL permissions can significantly affect database structure, membership should be restricted to appropriate administrative or development personnel. A user who only needs to read or modify table data generally does not require DDL privileges. Administrators should carefully separate schema-management responsibilities from routine data access to reduce unnecessary changes to production database structures.

Question 272. Which SQL Server role is associated with managing database security permissions?

1) db_securityadmin
2) db_datareader
3) db_datawriter
4) db_backupoperator

Answer: 1) db_securityadmin

Explanation:

The db_securityadmin fixed database role is associated with managing database permissions and role memberships within the database. Because security administration can affect which users and applications can access data or perform database operations, membership should be carefully controlled. Administrators should understand that granting security-management capabilities can provide significant influence over database access even if the principal does not directly receive broad data permissions. In environments following least privilege, security administration should be separated from routine data access where practical. Permission changes should also be documented and reviewed to maintain an appropriate security posture.

Question 273. Which SQL Server fixed database role is associated with performing database backups?

1) db_backupoperator
2) db_datareader
3) db_ddladmin
4) db_accessadmin

Answer: 1) db_backupoperator

Explanation:

The db_backupoperator fixed database role is designed for principals that need permissions related to backing up the database. Separating backup responsibilities from broader administrative permissions can help organizations delegate operational tasks without granting unnecessary access to database objects. Backup operations are an important part of database administration because they support recovery from accidental deletion, corruption, or other failures. However, a backup strategy should also consider retention, recovery objectives, restore testing, and protection of backup data. Granting backup-related permissions should be based on the responsibilities of the account rather than provided broadly to ordinary database users.

Question 274. Which SQL Server command can be used to display information about database consistency errors?

1) DBCC CHECKDB
2) DBCC SHOWPLAN
3) DBCC BACKUP
4) DBCC INDEX

Answer: 1) DBCC CHECKDB

Explanation:

DBCC CHECKDB checks the logical and physical integrity of database structures and can report consistency errors that may indicate corruption or other problems. Database administrators commonly use it as part of database maintenance and troubleshooting. The command can perform extensive checks, so administrators should understand its resource requirements before running it on large production databases. If corruption is detected, the results should be analyzed carefully and an appropriate recovery strategy should be followed. DBCC CHECKDB does not replace backups; maintaining reliable backups and regularly testing restoration procedures remains essential for database recovery.

Question 275. Which backup type contains all data required to represent the complete database at the time the backup was taken?

1) Full backup
2) Differential backup
3) Transaction log backup
4) Partial log backup

Answer: 1) Full backup

Explanation:

A full database backup contains a complete backup of the database data necessary to restore the database to the state represented by that backup, subject to the applicable recovery process. Full backups provide a foundation for many backup and restore strategies. Differential backups contain changes since a relevant full backup, while transaction log backups contain log records needed for point-in-time recovery under applicable recovery models. Administrators often combine these backup types to balance backup size, frequency, recovery objectives, and storage requirements. A backup is useful only when it can be successfully restored, so restore testing is also important.

Question 276. Which backup contains changes made since the most recent full backup?

1) Differential backup
2) Full backup
3) Transaction log backup
4) Configuration backup

Answer: 1) Differential backup

Explanation:

A differential backup contains data changes made since the most recent full database backup that serves as its differential base. As additional changes occur, later differential backups may become larger because they continue to include changes since that same full backup. During a restore sequence, the relevant full backup is generally restored first, followed by the appropriate differential backup. Differential backups can reduce the number of backups required compared with restoring many individual transaction log backups, although the exact strategy depends on recovery requirements. Administrators should choose backup schedules based on RPO, storage, and recovery needs.

Question 277. Which backup type records transaction log changes and supports point-in-time recovery when used appropriately?

1) Transaction log backup
2) Differential backup
3) Full backup
4) File snapshot only

Answer: 1) Transaction log backup

Explanation:

A transaction log backup captures transaction log records that have become eligible for backup under the applicable recovery model. A sequence of log backups can support point-in-time recovery by allowing administrators to restore database changes up to a selected point in time, provided the required backup chain is available. Transaction log backups are therefore important for databases with specific recovery objectives. Administrators must protect the backup chain and ensure that backups are taken according to an appropriate schedule. A full backup alone does not provide the same granularity for point-in-time recovery.

Question 278. Which SQL Server feature can automatically create recommendations for improving certain database performance conditions?

1) Database Advisor and automatic tuning capabilities
2) Database Mail only
3) Transparent Data Encryption
4) Firewall rules

Answer: 1) Database Advisor and automatic tuning capabilities

Explanation:

Azure SQL environments provide automated performance-management capabilities that can analyze workload behavior and provide recommendations or perform supported tuning actions. These capabilities can help administrators identify opportunities involving indexes, query performance, or plan behavior without requiring every issue to be investigated manually. Recommendations should still be reviewed in the context of the application’s workload and business requirements. Automatic tuning does not eliminate the need for monitoring or database expertise. Administrators should evaluate recommendations, understand their potential effects, and verify that changes improve performance without creating undesirable impacts on other workloads.

Question 279. Which feature can automatically force a previously known good execution plan when SQL Server detects a plan regression?

1) Automatic plan correction
2) Database Mail
3) Dynamic Data Masking
4) Firewall rules

Answer: 1) Automatic plan correction

Explanation:

Automatic plan correction can help address certain query performance regressions by identifying a problematic execution plan and forcing a previously known plan when the required conditions are met. This capability is associated with Query Store and automatic tuning functionality in supported Azure SQL environments. It can reduce the time required for administrators to respond to recurring plan regressions. However, administrators should still monitor the affected query after a corrective action because workload behavior can change. Automated correction is a support mechanism, not a replacement for understanding indexing, statistics, query design, and application workload patterns.

Question 280. Which Azure SQL Database feature is designed to provide a scalable environment for multiple databases with unpredictable individual workloads?

1) Elastic pools
2) Database Mail
3) Transparent Data Encryption
4) SQL Server Agent

Answer: 1) Elastic pools

Explanation:

Elastic pools provide a shared collection of compute resources for multiple Azure SQL databases. They are particularly useful when individual databases experience variable usage patterns and do not all require their maximum capacity simultaneously. Instead of provisioning each database for its highest possible workload, a pool can provide shared capacity that participating databases use according to demand. This approach can improve resource utilization for suitable workloads. Administrators should monitor pool-level and database-level resource consumption to ensure that the configured capacity is appropriate. Proper sizing is important because excessive contention can still affect database performance within the pool.