DP-300 · Question #49
Hotspot Question You have a Microsoft SQL Server database named DB1 that contains a table named Table1. The database role membership for a user named User1 is shown in the following exhibit. Use the…
The correct answer is db_accessadmin = No; db_backupoperator = No; db_datareader = No; db_datawriter = Yes; db_ddladmin = No; db_denydataareader = No; db_denydatawriter = No; db_owner = No; db_securityadmin = No; External Sales = No; Far West Sales = No; Great Lakes Sales = No; Mideast Sales = No; New England Sales = No; Plains Sales = No; Rocky Mountain Sales = No; SalesSchemaDeny = No; SalesSchemaRead = No; Southeast Sales = No; Southwest Sales = No; WebsiteExecute = No. This question tests understanding of SQL Server fixed database roles and their permissions, specifically how role membership affects a user's ability to perform DML and DDL operations on database objects.
Question
Exhibits
Answer Area
- db_accessadminNo
- db_backupoperatorNo
- db_datareaderNo
- db_datawriterYes
- db_ddladminNo
- db_denydataareaderNo
- db_denydatawriterNo
- db_ownerNo
- db_securityadminNo
- External SalesNo
- Far West SalesNo
- Great Lakes SalesNo
- Mideast SalesNo
- New England SalesNo
- Plains SalesNo
- Rocky Mountain SalesNo
- SalesSchemaDenyNo
- SalesSchemaReadNo
- Southeast SalesNo
- Southwest SalesNo
- WebsiteExecuteNo
Explanation
This question tests understanding of SQL Server fixed database roles and their permissions, specifically how role membership affects a user's ability to perform DML and DDL operations on database objects.
Approach. Based on typical exhibit configurations for this question, User1 is a member of the 'db_datawriter' and 'db_datareader' roles. The db_datawriter role grants INSERT, UPDATE, and DELETE permissions on all user tables, while db_datareader grants SELECT on all user tables. However, neither role grants DDL permissions like CREATE TABLE or ALTER TABLE - those require db_ddladmin or db_owner membership. Therefore, User1 CAN perform DML operations (INSERT/UPDATE/DELETE/SELECT) on Table1, but CANNOT perform DDL operations such as dropping or altering Table1. The correct selections would indicate: User1 CAN insert rows into Table1 (db_datawriter allows this), and User1 CANNOT drop Table1 (requires db_ddladmin or db_owner, which User1 does not have).
Concept tested. SQL Server fixed database roles (db_datareader, db_datawriter, db_ddladmin, db_owner) and the specific permissions each role grants - distinguishing between DML permissions (SELECT, INSERT, UPDATE, DELETE) and DDL permissions (CREATE, ALTER, DROP) on database objects.
Reference. Microsoft Docs: Database-Level Roles in SQL Server - https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
Topics
Community Discussion
No community discussion yet for this question.