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

 

Question 321

Which T-SQL statement removes a previously granted or denied permission from a database principal?

1. DROP
2. REVOKE
3. GRANT
4. ALTER

Correct Answer: 2

Explanation

The REVOKE statement removes a previously granted or denied permission from a database principal. It is commonly used when access should no longer be explicitly assigned to a user or role. GRANT provides permissions, while DENY explicitly prevents a permission. Administrators should understand SQL Server’s permission hierarchy because a user may still receive access through another role even after a direct permission is revoked. Regular permission reviews help maintain least-privilege security.

Question 322

Which T-SQL statement explicitly prevents a user or role from performing a specific operation?

1. GRANT
2. REVOKE
3. DENY
4. EXECUTE

Correct Answer: 3

Explanation

The DENY statement explicitly prevents a database principal from receiving a specified permission through normal permission inheritance. It is different from REVOKE, which removes an explicit permission assignment. Administrators should use DENY carefully because SQL Server permission inheritance and ownership can affect how permissions are evaluated. Before applying a DENY, administrators should understand the user’s membership in database roles and how the requested permission is inherited.

Question 323

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 control over a database. Members can perform most database-level administrative actions and can manage permissions and database objects. Because this role provides broad privileges, administrators should avoid assigning it to users or applications unless it is genuinely required. For normal application workloads, more limited roles such as db_datareader, db_datawriter, or custom roles are generally preferable to follow the principle of least privilege.

Question 324

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

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

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 or application needs broad read access without requiring permissions to modify data. However, assigning this role may provide more access than a specific application actually needs. Administrators should consider creating custom roles with permissions limited to the required tables, views, or procedures when stronger least-privilege controls are needed.

Question 325

Which fixed database role allows members to insert, update, and delete data in all user tables?

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

Correct Answer: 2

Explanation

The db_datawriter fixed database role allows members to insert, update, and delete data in all user tables. It does not provide the same broad administrative privileges as db_owner. However, it can still be excessive for applications that only need access to a small number of tables. Administrators should consider custom database roles when applications require limited write access. Granting only the necessary permissions helps reduce the potential impact of compromised credentials.

Question 326

Which database role is generally used to allow users to execute stored procedures and functions when appropriate permissions are assigned?

1. db_executor
2. db_datareader
3. db_backupoperator
4. db_ddladmin

Correct Answer: 1

Explanation

SQL Server does not provide a built-in fixed database role named db_executor by default, but administrators can create a custom database role with that name and grant it EXECUTE permissions. This pattern is useful when applications need to execute stored procedures without receiving broad direct access to tables. A custom execution role can support least privilege by exposing controlled database operations through procedures rather than granting unnecessary table-level permissions.

Question 327

Which system catalog view contains information about database-level principals such as users and roles?

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

Correct Answer: 2

Explanation

sys.database_principals contains information about database-level security principals, including database users, roles, and other principal types. Administrators can query this catalog view when investigating database security configuration. It can be combined with other catalog views to determine role memberships and permissions. Understanding database principals is important when troubleshooting authorization problems or reviewing whether users and applications have more access than required.

Question 328

Which catalog view can be used to examine explicit database permissions assigned to principals?

1. sys.tables
2. sys.database_permissions
3. sys.columns
4. sys.databases

Correct Answer: 2

Explanation

sys.database_permissions contains information about explicit permissions assigned to database principals. Administrators can query it to investigate GRANT, DENY, and other permission states at the database level. This is useful when troubleshooting authorization problems or performing security reviews. Because permissions can also come through role membership and ownership, administrators should combine this view with information from sys.database_principals and role-membership metadata for a complete understanding of effective access.

Question 329

Which database object provides a namespace that can be used to organize tables, views, procedures, and other objects?

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

Correct Answer: 1

Explanation

A schema is a logical container for database objects such as tables, views, stored procedures, and functions. Schemas help organize objects and can also simplify permission management. Administrators can grant permissions on a schema rather than individually assigning permissions to every object within it. This can make security administration easier in larger databases. Schemas are separate from database users, although users can own or have permissions on schemas.

Question 330

Which permission is required to execute a stored procedure?

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

Correct Answer: 3

Explanation

The EXECUTE permission allows a user or role to execute a stored procedure or other executable database object when applicable. Administrators can grant EXECUTE directly on an individual procedure or at a broader scope such as a schema. Granting EXECUTE on procedures can support least privilege because applications may perform required operations without receiving direct access to underlying tables. Administrators should review procedure permissions regularly and avoid granting unnecessary privileges.

Question 331

Which SQL Server feature can encrypt sensitive column values so that the database engine does not normally see the plaintext data?

1. Dynamic Data Masking
2. Always Encrypted
3. Row-Level Security
4. Data compression

Correct Answer: 2

Explanation

Always Encrypted is designed to protect sensitive data by encrypting selected column values so that the database engine does not normally see the plaintext values. Encryption and decryption are handled by the client-side application or driver. This provides stronger protection for sensitive information than Dynamic Data Masking, which only changes how data is displayed to certain users. Administrators should carefully evaluate application compatibility, key management, supported operations, and performance before enabling Always Encrypted.

Question 332

Which security feature controls access to individual rows based on a user’s identity or role?

1. Transparent Data Encryption
2. Row-Level Security
3. Always Encrypted
4. Database backup

Correct Answer: 2

Explanation

Row-Level Security (RLS) uses security predicates to control which rows a user can access or modify. It is useful when multiple users or tenants share the same tables but should only see their own data. For example, a multi-tenant application can use RLS to restrict each customer to rows belonging to that customer. Administrators should thoroughly test RLS policies because incorrect predicates can unintentionally expose data or prevent legitimate users from accessing required rows.

Question 333

Which Azure SQL security feature encrypts data at rest without requiring changes to application queries?

1. Always Encrypted
2. Transparent Data Encryption
3. Row-Level Security
4. Dynamic Data Masking

Correct Answer: 2

Explanation

Transparent Data Encryption (TDE) encrypts database files and related data at rest while remaining transparent to applications. Applications continue sending normal queries without implementing encryption and decryption logic for the database files. TDE helps protect data if storage media or backup files are accessed without authorization. It is different from Always Encrypted, which protects selected sensitive column values from access by the database engine and requires appropriate client-side support.

Question 334

Which SQL Server feature can record login activity and database operations for security investigations?

1. SQL Server Audit
2. Data compression
3. Query Store
4. Resource Governor

Correct Answer: 1

Explanation

SQL Server Audit provides a mechanism for recording selected server-level and database-level events. Administrators can configure audit specifications to capture activities such as authentication events, permission changes, and database operations. Audit records can support compliance requirements, security investigations, and monitoring. Administrators should carefully select the events to capture and protect the audit destination because audit data can contain sensitive information. Appropriate retention policies should also be established.

Question 335

Which SQL Server feature can identify login failures and other security-related events for auditing?

1. SQL Server Audit
2. Index rebuild
3. Query Store
4. Database snapshot

Correct Answer: 1

Explanation

SQL Server Audit can capture security-related events, including selected authentication and database activity events, depending on the configured audit specifications. Administrators can use these records to investigate suspicious activity, review access patterns, and support compliance requirements. Audit configuration should be designed according to the organization’s security and regulatory needs. Capturing too many unnecessary events can increase storage and management requirements, so administrators should focus on events that provide meaningful security information.

Question 336

Which SQL Server feature helps prevent a single workload from consuming excessive CPU or memory resources on a shared instance?

1. Resource Governor
2. Query Store
3. Extended Events
4. SQL Server Audit

Correct Answer: 1

Explanation

Resource Governor allows administrators to control resource usage for classified workloads. Administrators can create workload groups and resource pools with limits or priorities for CPU and memory. This can help protect important workloads when multiple applications share the same SQL Server instance. Resource Governor is not primarily a query optimization feature; it is a resource management feature. Administrators should monitor the results after configuration to ensure that limits do not negatively affect required workloads.

Question 337

Which SQL Server feature can help identify queries that consume excessive CPU over time?

1. Query Store
2. Database Mail
3. Model database
4. Database snapshots

Correct Answer: 1

Explanation

Query Store records query execution information over time, including runtime statistics that can help administrators identify CPU-intensive queries. Administrators can sort and analyze query performance to find workloads that consume significant resources. This historical information is useful when performance problems are intermittent or started after a change. Once an expensive query is identified, administrators can inspect its execution plan, indexes, statistics, and query design to determine the appropriate optimization strategy.

Question 338

Which tool is most appropriate for capturing a detailed event when a deadlock occurs?

1. Extended Events
2. Azure Cost Management
3. Azure Advisor
4. BACPAC

Correct Answer: 4

Explanation

Extended Events can capture deadlock events and provide information about the sessions, resources, and statements involved. This information helps administrators determine why transactions are competing for resources and which transaction SQL Server selected as the deadlock victim. Administrators can then investigate indexing, transaction order, isolation levels, and application behavior. Capturing deadlock events is generally preferable to trying to diagnose a deadlock only from a user’s error message.

Question 339

Which SQL Server feature can store multiple historical versions of rows for temporal querying?

1. System-versioned temporal tables
2. Database snapshots
3. Query Store
4. SQL Server Audit

Correct Answer: 1

Explanation

System-versioned temporal tables automatically maintain historical row versions in a history table. This allows administrators and applications to query data as it existed during previous periods. Temporal tables are useful for auditing data changes, investigating historical states, and supporting business requirements that need data history. They are not a substitute for database backups because they do not provide complete database recovery. Administrators should manage the history data appropriately as it can grow over time.

Question 340

Which SQL Server feature allows administrators to compare current data with historical row versions using temporal queries?

1. System-versioned temporal tables
2. Dynamic Data Masking
3. Database Mail
4. Resource Governor

Correct Answer: 3

Explanation

System-versioned temporal tables maintain current and historical versions of rows with associated system-time information. Administrators can use temporal queries to examine how data looked at a previous point in time. This is useful for investigating changes, auditing business records, and understanding historical states. The history table is maintained automatically by SQL Server while system versioning is enabled. Administrators should monitor history-table growth and establish appropriate retention or cleanup strategies where supported.