View Full Microsoft DP-800 Exam Dumps and Practice Test Dumps.

 

Question 121

Which database permission allows a user to execute a stored procedure?

1. SELECT
2. EXECUTE
3. INSERT
4. REFERENCES

Correct Answer: 2

Explanation

The EXECUTE permission allows a user or principal to execute a stored procedure or other executable database objects where applicable. Granting execute permission can allow users to perform approved database operations without giving them direct permissions on every underlying table. This can improve security when stored procedures are used to control access to data. Administrators should grant only the permissions required for the user’s responsibilities and avoid unnecessarily broad database permissions.

Question 122

Which database role provides broad administrative permissions within a SQL Server database?

1. db_datareader
2. db_datawriter
3. db_owner
4. public

Correct Answer: 3

Explanation

The db_owner fixed database role provides extensive permissions within a database. Members of this role can perform almost all configuration and management tasks for that database. Because the permissions are broad, administrators should avoid adding users to db_owner unless the responsibilities genuinely require it. More limited roles such as db_datareader and db_datawriter should be considered when users only need to read or modify data.

Question 123

Which fixed database role allows members to read data from all user tables and views?

1. db_datareader
2. db_datawriter
3. db_ddladmin
4. db_securityadmin

Correct Answer: 1

Explanation

The db_datareader fixed database role allows members to read data from all user tables and views in the database. It is useful when a user needs broad read-only access without requiring permissions to modify data. However, it may provide more access than necessary for some applications. Administrators should evaluate whether more specific permissions are appropriate. Following the principle of least privilege helps reduce the potential impact of compromised credentials or accidental actions.

Question 124

Which fixed database role allows members to modify data in all user tables and views?

1. db_datareader
2. db_datawriter
3. db_backupoperator
4. db_accessadmin

Correct Answer: 2

Explanation

The db_datawriter fixed database role allows members to add, change, or delete data in all user tables and views where applicable. It does not automatically provide every administrative permission in the database. This role can be useful for applications or users that require broad data modification capabilities. However, it should be assigned carefully because it provides write access across the database. More restrictive object-level permissions may be better when only specific tables need to be modified.

Question 125

Which SQL Server object is used to group database users and assign permissions to them collectively?

1. Database role
2. Database file
3. Index
4. Trigger

Correct Answer: 1

Explanation

A database role groups users or other principals so that permissions can be assigned collectively. Instead of granting the same permissions individually to many users, an administrator can create or use a role and grant the required permissions to that role. Users can then be added to the role. This simplifies permission management and makes access control easier to maintain as employees or applications are added and removed.

Question 126

Which SQL Server statement creates a new database user mapped to an existing login?

1. CREATE USER
2. CREATE TABLE
3. CREATE INDEX
4. CREATE DATABASE

Correct Answer: 1

Explanation

The CREATE USER statement creates a database principal. In a traditional SQL Server environment, a database user can be mapped to an existing server-level login. This separates server authentication from database authorization. A login may exist at the server level while the user represents that identity inside a specific database. Administrators should create users and grant permissions according to the application’s actual requirements rather than automatically assigning broad database roles.

Question 127

Which SQL Server statement creates a server-level login using SQL authentication?

1. CREATE USER
2. CREATE LOGIN
3. CREATE ROLE
4. CREATE PRINCIPAL

Correct Answer: 2

Explanation

The CREATE LOGIN statement creates a server-level security principal that can authenticate to SQL Server. SQL logins typically use a username and password, while Windows or Microsoft Entra-based identities can use other authentication mechanisms. After a login is created, it can be mapped to users in individual databases. Administrators should enforce strong authentication practices and grant only the server and database permissions necessary for the login’s intended workload.

Question 128

Which system catalog view contains information about database users, roles, and other database principals?

1. sys.database_principals
2. sys.tables
3. sys.columns
4. sys.indexes

Correct Answer: 1

Explanation

The sys.database_principals catalog view provides information about database-level principals such as users, roles, and application roles. It can be useful when administrators need to investigate who exists in a database and identify the types of security principals configured there. Other catalog views provide information about tables, columns, indexes, and permissions. System catalog views are valuable for administration because they allow database metadata to be queried directly using T-SQL.

Question 129

Which system catalog view can be used to inspect database-level permissions?

1. sys.database_permissions
2. sys.database_files
3. sys.objects
4. sys.schemas

Correct Answer: 1

Explanation

The sys.database_permissions catalog view contains information about permissions that have been explicitly granted, denied, or otherwise configured at the database level and on securables. Administrators can use it when investigating why a user can or cannot perform a particular operation. Permission analysis may require checking principals, role memberships, object ownership, and inherited permissions as well. Understanding the complete permission hierarchy is important when troubleshooting access issues.

Question 130

Which SQL Server feature helps separate database objects into logical namespaces for organization and security?

1. Schema
2. Index
3. Trigger
4. Transaction

Correct Answer: 1

Explanation

A schema provides a logical container for database objects such as tables, views, and stored procedures. Schemas help organize objects and can also be used as securable objects when assigning permissions. For example, an organization might separate sales and finance objects into different schemas. This makes database administration easier and can simplify permission management. Schemas are different from databases because multiple schemas can exist within the same database.

Question 131

Which SQL Server isolation level provides the highest standard transaction isolation and prevents dirty, non-repeatable, and phantom reads?

1. READ COMMITTED
2. REPEATABLE READ
3. SERIALIZABLE
4. READ UNCOMMITTED

Correct Answer: 3

Explanation

The SERIALIZABLE isolation level provides the strictest standard transaction isolation level. It prevents dirty reads, non-repeatable reads, and phantom reads by using stronger locking behavior. The increased protection can reduce concurrency because transactions may hold locks for longer periods or acquire range locks. Administrators should use SERIALIZABLE only when the application’s consistency requirements justify its potential performance impact. Choosing an isolation level involves balancing correctness, concurrency, and workload requirements.

Question 132

Which isolation level allows dirty reads and generally provides the least restrictive locking behavior?

1. SERIALIZABLE
2. READ UNCOMMITTED
3. REPEATABLE READ
4. SNAPSHOT

Correct Answer: 2

Explanation

READ UNCOMMITTED allows a transaction to read data that another transaction has modified but not yet committed. These are known as dirty reads. This isolation level can provide high concurrency and reduce blocking, but the returned data may not represent committed information. It should therefore be used carefully. It may be appropriate for certain approximate reporting scenarios, but it is generally unsuitable when applications require accurate and consistent transactional data.

Question 133

Which SQL Server feature can use row versions to reduce blocking between readers and writers?

1. Row versioning
2. Database mirroring
3. Full backup
4. Data compression

Correct Answer: 1

Explanation

Row versioning allows SQL Server to maintain previous versions of rows so that certain readers can access a consistent version of data without waiting for writers to release locks. Features such as Read Committed Snapshot Isolation and Snapshot Isolation use row versioning. This can reduce reader-writer blocking in appropriate workloads. However, row versions consume tempdb resources, so administrators should monitor tempdb usage when enabling row-versioning-based isolation.

Question 134

Which SQL Server feature can automatically capture and store information about deadlocks for troubleshooting?

1. Extended Events
2. Database diagrams
3. BACPAC
4. SQL Server Browser

Correct Answer: 1

Explanation

Extended Events can capture detailed information about database events, including deadlocks. Administrators can create an Extended Events session that collects deadlock-related information and then analyze the captured data to identify participating sessions, resources, and statements. This is useful when deadlocks occur intermittently and are difficult to reproduce manually. Proper deadlock analysis can help administrators identify transaction design or indexing issues that contribute to recurring blocking problems.

Question 135

Which SQL Server performance problem occurs when a query waits because another transaction holds a conflicting lock?

1. Blocking
2. Fragmentation
3. Compression
4. Replication

Correct Answer: 1

Explanation

Blocking occurs when one session holds a lock on a resource and another session needs a conflicting lock before it can continue. Short periods of blocking are normal in transactional systems, but long or excessive blocking can cause application performance problems. Administrators can investigate blocking using dynamic management views, Activity Monitor, Query Store, or other monitoring tools. Reducing transaction duration, improving indexing, and optimizing queries can help reduce unnecessary blocking.

Question 136

Which dynamic management view can provide information about currently executing requests in SQL Server?

1. sys.dm_exec_requests
2. sys.dm_db_index_physical_stats
3. sys.tables
4. sys.columns

Correct Answer: 1

Explanation

sys.dm_exec_requests provides information about requests currently executing on a SQL Server instance. It can show details such as session identifiers, request status, wait information, database context, and the statement being executed. Administrators can use it to investigate long-running queries, blocking, and active workload. It is particularly useful during troubleshooting because it provides information about current activity rather than historical query behavior.

Question 137

Which dynamic management function can provide information about index fragmentation?

1. sys.dm_db_index_physical_stats
2. sys.dm_exec_requests
3. sys.database_principals
4. sys.dm_exec_sessions

Correct Answer: 1

Explanation

sys.dm_db_index_physical_stats provides information about the physical characteristics of indexes, including fragmentation levels. Administrators can use it to determine whether indexes may benefit from maintenance operations such as reorganizing or rebuilding. Fragmentation should be evaluated in the context of workload and index usage rather than automatically fixing every index. Large databases may require careful scheduling because index maintenance can consume CPU, memory, storage, and I/O resources.

Question 138

Which Azure service can send alerts when a database metric crosses a configured threshold?

1. Azure Monitor
2. Azure Key Vault
3. Azure DNS
4. Azure Resource Graph only

Correct Answer: 1

Explanation

Azure Monitor can collect metrics from Azure resources and create alert rules based on configured conditions. For example, an administrator can create an alert when CPU utilization, storage consumption, or another supported metric exceeds a defined threshold. Alerts can notify administrators or trigger automated actions depending on the configuration. Monitoring and alerting help teams identify problems before they become major outages. Thresholds should be selected carefully to reduce unnecessary alert noise.

Question 139

Which Azure SQL Database metric is most directly related to the amount of storage space consumed by the database?

1. Storage
2. Login count
3. Number of schemas
4. Number of stored procedures

Correct Answer: 1

Explanation

The storage metric indicates how much database storage is being consumed and can help administrators monitor capacity. Tracking storage usage is important because a database approaching its storage limit may experience operational problems or require scaling. Administrators should monitor growth trends rather than waiting until storage is nearly exhausted. Storage monitoring can also help identify unexpected data growth, large indexes, retained historical data, or other factors affecting database capacity.

Question 140

Which SQL Server tool provides a graphical interface for viewing active sessions, processes, and current database activity?

1. Activity Monitor
2. BACPAC
3. SqlPackage
4. Azure Key Vault

Correct Answer: 1

Explanation

Activity Monitor provides a graphical view of current SQL Server activity. It can help administrators investigate active processes, resource-intensive queries, waits, and other workload information. It is useful for quickly identifying performance problems from a graphical interface. For deeper investigation, administrators can combine Activity Monitor with dynamic management views, Query Store, and Extended Events. Activity Monitor should be used as an initial troubleshooting tool rather than the only source of performance information.