This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement data security and compliance
      --> Design and implement object-level permissions

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Securing data is one of the most important responsibilities of a SQL developer. While server-level and database-level permissions determine who can connect to SQL Server and access databases, object-level permissions determine what users can do with individual database objects such as tables, views, stored procedures, functions, sequences, and schemas.

The DP-800 certification expects candidates to understand how to implement the principle of least privilege, ensuring that users receive only the permissions required to perform their jobs.

Object-level permissions are a fundamental component of SQL Server security and are widely used in:

• Microsoft SQL Server
• Azure SQL Database
• Azure SQL Managed Instance
• Microsoft Fabric SQL Database
• SQL Database in Fabric Warehouses (where supported)

Understanding how permissions are inherited, granted, denied, revoked, and combined with roles is essential for designing secure database solutions.

What Are Object-Level Permissions?

Object-level permissions control access to individual database objects rather than the entire database.

For example, one user might:

• Read data from a table
• Execute a stored procedure
• Update rows in another table
• View metadata
• Create indexes

while another user has completely different permissions.

Unlike database-level permissions, object permissions provide very granular security.

Example:

Sales.Customers
Sales.Orders
Sales.Products
HR.Employees

A salesperson may have access to Sales tables but no access to HR tables.

Common Database Objects That Can Be Secured

Permissions can be assigned to numerous SQL Server objects, including:

• Tables
• Views
• Stored procedures
• Functions
• Schemas
• Sequences
• Synonyms
• External tables
• User-defined types
• XML schema collections
• Service Broker objects

DP-800 focuses primarily on:

• Tables
• Views
• Stored procedures
• Functions
• Schemas

Permission Hierarchy

Permissions exist at several levels.

Server
    ↓
Database
    ↓
Schema
    ↓
Object

Example:

Database
    Sales
Schema
    Sales
Table
    Orders

Permissions granted on the schema may automatically apply to objects within that schema.

Common Object Permissions

The most commonly used permissions include:

GRANT

GRANT gives permissions.

Example

GRANT SELECT
ON Sales.Orders
TO SalesUser;

The user can now query the table.

Example

GRANT INSERT, UPDATE
ON Sales.Orders
TO SalesUser;

Multiple permissions can be granted simultaneously.

Grant execute permission

GRANT EXECUTE
ON dbo.usp_ProcessOrders
TO SalesUser;

The user may execute the procedure without having direct table permissions.

DENY

DENY explicitly prevents access.

Example

DENY DELETE
ON Sales.Orders
TO SalesUser;

Even if another role grants DELETE, DENY overrides it.

This is one of the most important security concepts on the DP-800 exam.

REVOKE

REVOKE removes previously granted or denied permissions.

Example

REVOKE SELECT
ON Sales.Orders
FROM SalesUser;

REVOKE does not deny access.

It simply removes the explicit permission.

GRANT vs DENY vs REVOKE

Permission Precedence

SQL Server evaluates permissions using precedence rules.

Highest priority:

DENY

Lower priority:

GRANT

Example

User belongs to:

SalesRole

SalesRole:

GRANT SELECT

Another role:

DENY SELECT

Result:

User cannot SELECT.

DENY wins.

Granting Permissions to Roles

Best practice is to grant permissions to roles rather than directly to users.

Example

CREATE ROLE SalesReaders;

Grant permission

GRANT SELECT
ON Sales.Orders
TO SalesReaders;

Add user

ALTER ROLE SalesReaders
ADD MEMBER Alice;

This greatly simplifies administration.

Schema-Level Permissions

Instead of granting access to each table individually, permissions may be granted on an entire schema.

Example

GRANT SELECT
ON SCHEMA::Sales
TO SalesReaders;

The role receives SELECT permission on all objects within the Sales schema.

Stored Procedure Permissions

Applications often use stored procedures instead of direct table access.

Example

GRANT EXECUTE
ON dbo.usp_GetCustomerOrders
TO AppUser;

Users execute the procedure without needing direct permissions on the underlying tables (ownership chaining permitting).

Benefits include:

• Better security
• Reduced attack surface
• Easier auditing
• Centralized business logic

View Permissions

Views frequently expose only selected columns or rows.

Example

GRANT SELECT
ON Sales.vCustomerSummary
TO SalesReaders;

Applications query the view rather than the underlying table.

Advantages include:

• Hide sensitive columns
• Simplify queries
• Provide logical security boundaries

Function Permissions

Scalar and table-valued functions also require EXECUTE permission.

Example

GRANT EXECUTE
ON dbo.fn_CalculateDiscount
TO SalesUser;

Ownership Chaining

Ownership chaining occurs when objects owned by the same owner access one another.

Example

User
   ↓
Stored Procedure
   ↓
Table

If both objects share the same owner:

• SQL Server does not perform additional permission checks on the table.

Benefits:

• Simplifies application security
• Eliminates unnecessary table permissions
• Improves manageability

DP-800 frequently tests this concept.

Least Privilege Principle

One of Microsoft’s most important security recommendations.

Users should receive:

• Only the permissions required
• Nothing more

Poor example

db_owner

Better example

SELECT
EXECUTE

Grant only what is necessary.

Avoid Granting db_owner

Many organizations incorrectly solve permission issues by granting db_owner.

Problems:

• Full database control
• Can drop objects
• Can change security
• Can alter schemas
• Increased security risk

Instead:

• Create custom roles
• Grant only required permissions

Object Permissions and AI Applications

Modern AI-enabled SQL solutions frequently access databases through:

• APIs
• Stored procedures
• Semantic search
• Retrieval-Augmented Generation (RAG)
• Microsoft Fabric
• Copilot applications

Best practice:

AI applications should never connect using highly privileged accounts.

Instead:

• Create service accounts.
• Grant only EXECUTE on required procedures or SELECT on approved views.
• Avoid direct access to sensitive tables.
• Combine object permissions with Row-Level Security (RLS), Dynamic Data Masking (DDM), and Always Encrypted where appropriate.

This approach reduces the risk of exposing sensitive information through AI-assisted applications.

Best Practices

Microsoft recommends:

• Grant permissions through roles.
• Follow least privilege.
• Prefer views over direct table access.
• Use stored procedures for data modifications.
• Avoid granting db_owner.
• Regularly audit permissions.
• Remove unused permissions.
• Use schema-based permissions when appropriate.
• Minimize explicit DENY statements unless required.
• Combine object permissions with other SQL Server security features.

DP-800 Exam Tips

Candidates should know how to:

• Grant object permissions
• Revoke permissions
• Deny permissions
• Understand permission inheritance
• Secure stored procedures
• Secure views
• Grant schema permissions
• Use database roles
• Explain ownership chaining
• Apply least privilege
• Understand permission precedence
• Determine the effect of GRANT, DENY, and REVOKE
• Design secure access models for AI-enabled database applications

Practice Exam Questions

Question 1

A database developer wants users to read data from the Sales.Orders table but prevent any modifications. Which permission should be granted?

A. EXECUTE

B. SELECT

C. ALTER

D. CONTROL

Correct Answer: B

Explanation:
The SELECT permission allows users to read rows from a table without permitting INSERT, UPDATE, or DELETE operations.

Question 2

A user belongs to two database roles. One role grants SELECT permission on a table, while the other role explicitly denies SELECT permission. What is the result?

A. SQL Server ignores the DENY.

B. SQL Server randomly selects one permission.

C. The user can still read the table.

D. The user cannot read the table.

Correct Answer: D

Explanation:
DENY takes precedence over GRANT. An explicit DENY overrides any granted permissions from other roles.

Question 3

Which statement is the recommended method for assigning permissions to multiple users?

A. Grant permissions directly to every user.

B. Add every user to db_owner.

C. Create database roles and grant permissions to the roles.

D. Use only server-level permissions.

Correct Answer: C

Explanation:
Assigning permissions to roles simplifies administration, improves consistency, and aligns with Microsoft security best practices.

Question 4

Which command removes a previously granted permission without explicitly denying access?

A.

REVOKE

B.

DENY

C.

REMOVE

D.

DROP

Correct Answer: A

Explanation:
REVOKE removes an existing GRANT or DENY. It does not prohibit future access unless another permission remains in effect.

Question 5

An application should execute a stored procedure but should not have direct access to the underlying tables. Which permission should be granted?

A. SELECT on every table

B. CONTROL on the database

C. EXECUTE on the stored procedure

D. ALTER on the schema

Correct Answer: C

Explanation:
Granting EXECUTE on the stored procedure allows users to perform approved operations without direct table access, leveraging ownership chaining when applicable.

Question 6

Which permission allows a user to modify the definition of an existing table?

A. ALTER

B. SELECT

C. EXECUTE

D. REFERENCES

Correct Answer: A

Explanation:
The ALTER permission enables changes to an object’s definition, such as adding or removing columns from a table.

Question 7

A database administrator grants SELECT permission on an entire schema. What is the primary benefit?

A. It encrypts every table in the schema.

B. It automatically creates new users.

C. It applies permissions to objects within the schema, simplifying administration.

D. It replaces Row-Level Security.

Correct Answer: C

Explanation:
Schema-level permissions reduce administrative effort by applying permissions to objects contained within the schema, rather than requiring individual grants on each object.

Question 8

Which principle recommends granting users only the permissions they require to perform their jobs?

A. Defense in depth

B. Separation of duties

C. Ownership chaining

D. Least privilege

Correct Answer: D

Explanation:
The principle of least privilege minimizes security risks by limiting permissions to only those necessary for a user’s responsibilities.

Question 9

Why is granting the db_owner role to application accounts generally discouraged?

A. It prevents applications from executing stored procedures.

B. It provides unnecessary administrative privileges and increases security risk.

C. It disables ownership chaining.

D. It prevents schema-level permissions from working.

Correct Answer: B

Explanation:
The db_owner role grants full control over the database, which violates the principle of least privilege and can expose the database to accidental or malicious changes.

Question 10

Which database object permission is required to run a user-defined function?

A. SELECT

B. UPDATE

C. EXECUTE

D. ALTER

Correct Answer: C

Explanation:
User-defined functions, like stored procedures, require the EXECUTE permission to be invoked by users or applications.

Go to the DP-800 Exam Prep Hub main page