70-465 · Question #156
70-465 Question #156: Real Exam Question with Answer & Explanation
The correct answer is B: CONTROL. SQL Server Security Assignment — Drag-and-Drop Explanation This question builds T-SQL code to assign security to dev_role1. The complete code likely spans two or three statements: ``sql CREATE SCHEMA dev_schema1 AUTHORIZATION dev_role1 GRANT CONTROL ON SCHEMA::dev_schema1 TO de
Question
Drag and Drop Question You need to assign security to dev_role1. How should you complete the code? To answer, drag the appropriate elements to the correct locations. Each element may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. Answer:
Options
- AAUTHORIZATION
- BCONTROL
- Cdev_role1
- Ddev_schema1
- EOBJECT_ID
- FSCHEMA
- GSELECT
Explanation
SQL Server Security Assignment — Drag-and-Drop Explanation
This question builds T-SQL code to assign security to dev_role1. The complete code likely spans two or three statements:
CREATE SCHEMA dev_schema1 AUTHORIZATION dev_role1
GRANT CONTROL ON SCHEMA::dev_schema1 TO dev_role1
GRANT SELECT ON OBJECT::OBJECT_ID TO dev_role1
Item-by-Item Breakdown
A → AUTHORIZATION
Used in CREATE SCHEMA dev_schema1 AUTHORIZATION dev_role1. The AUTHORIZATION keyword designates the owner of the schema being created. Without it, the schema defaults to the current user, not the role.
B → CONTROL
Maps to GRANT CONTROL ON SCHEMA::.... CONTROL is SQL Server's highest schema-level permission — it implies all other permissions on that schema. This gives dev_role1 full authority over dev_schema1.
C → dev_role1
The principal — the database role receiving all grants. It appears as the AUTHORIZATION target and as the TO recipient in GRANT statements. The question notes elements can be reused; dev_role1 appears in multiple positions.
D → dev_schema1
The securable object (the schema). It appears in both the CREATE SCHEMA statement and the GRANT ... ON SCHEMA::dev_schema1 clause.
E → OBJECT_ID
Refers to a specific database object (table, view, etc.) being secured. Used in the GRANT SELECT ON OBJECT::OBJECT_ID statement, targeting a precise object rather than the entire schema.
F → SCHEMA
The securable class in the GRANT CONTROL ON SCHEMA::dev_schema1 statement. SQL Server requires you to specify the class (SCHEMA, OBJECT, DATABASE, etc.) before the double-colon :: separator.
G → SELECT
A granular permission granted at the object level. Unlike CONTROL (which is broad/schema-wide), SELECT is a specific DML permission applied to individual objects via GRANT SELECT ON OBJECT::....
Common Mistakes
| Mistake | Why It's Wrong |
|---|---|
Using OBJECT instead of OBJECT_ID for the object-level grant | OBJECT_ID is the actual object reference; OBJECT is the class keyword |
Swapping CONTROL and SELECT | CONTROL is schema-wide authority; SELECT is a narrow, object-level read permission |
Omitting AUTHORIZATION in CREATE SCHEMA | The schema would be owned by the executing user, not dev_role1 |
Mixing up SCHEMA and OBJECT as securable classes | SCHEMA:: precedes schema names; OBJECT:: precedes table/view names |
Putting dev_schema1 where dev_role1 should be | The TO clause always names the principal (role/user), never the object |
Topics
Community Discussion
No community discussion yet for this question.