How do you stop the wrong person from accessing your database while making sure the right person can do their job? That is exactly the problem that identity and access management solves, and it is a core topic on the DP-300 exam because every real database must keep data safe from unauthorised eyes and accidental damage.
Jump to a section
A simple way to picture Managing Identity and Access for Azure SQL
A nightclub's front door is a checkpoint that enforces two separate rules: who is allowed in (identity) and what they can do once inside (access). The club owner does not personally check every guest; instead, they hire a bouncer and set rules that the bouncer follows automatically. This setup mirrors exactly how Azure SQL manages its security.
The bouncer checks each guest's ID against a list of approved names. In Azure SQL, this identity check uses a login — a digital ID that proves who you are. The login can be created inside the SQL server itself (SQL authentication) or linked to a corporate directory (Microsoft Entra ID authentication). Just as a club might accept driver's licences or passports, Azure SQL accepts these two forms of identity.
Once inside, the bouncer’s job changes. Now they enforce what each guest can do — the VIP lounge? The dance floor? The bar? This is authorisation, and in Azure SQL it is managed by permissions and roles. A user might be a 'db_owner' (full access, like a club manager who can go anywhere and touch anything) or a 'db_datareader' (only allowed to read data, like a guest who can only look at the decorations but not touch them). The bouncer never guesses or makes exceptions — he follows the owner’s exact, written policy. This guarantees that only the right people get in, and once they are in, they can only do what their role allows.
When you create an Azure SQL database, it does not live on your personal computer. It lives in Microsoft’s cloud, on a server that you do not physically control. This means you need a way to prove who is asking to connect and what they are allowed to do once they connect. That is the entire job of identity and access management.
First, let us define identity. An identity is a digital representation of a person, application, or service that needs to connect to the database. In the world of Azure SQL, identities come in two main flavours: SQL logins and Microsoft Entra ID principals. A SQL login is created inside the SQL server itself. Think of it as a username and password that SQL Server stores and checks. When a human or an application presents this username and password, SQL Server checks its own internal list to see if the details match. If they do, the identity is verified — that is authentication.
Microsoft Entra ID, on the other hand, is Microsoft’s cloud-based directory service. It stores identities for an entire organisation. If your company uses Microsoft 365 for email, you already have an identity in Microsoft Entra ID. When you set up Azure SQL to use Microsoft Entra ID authentication, the database does not check a local password. Instead, it asks Microsoft Entra ID to verify the user. This is more secure because Microsoft Entra ID supports features like multi-factor authentication — where you need your password plus a code from your phone — and conditional access policies that can block logins from strange locations or devices.
Once the identity is authenticated, the database needs to decide what that identity is allowed to do. That is authorisation. Authorisation in Azure SQL is managed through permissions and roles. A permission is a single right — for example, the right to read data from a table, or the right to delete a table. A role is a named collection of permissions. Instead of giving every user individual permissions, which becomes messy very quickly, you put users into roles. There are built-in roles like db_owner, db_datareader, and db_datawriter. There are also server-level roles like sysadmin and dbcreator.
Let us walk through a concrete example. Suppose your company has a database that stores customer orders. You have three people who need access: Alice, who manages the database; Bob, who enters new orders; and Charlie, who runs reports but should never change any data. You would create a Microsoft Entra ID user for each person (or use their existing company email identity). Then, in the database, you would add Alice to the db_owner role so she can change schema and fix issues. You would add Bob to db_datawriter, which lets him insert, update, and delete data. You would add Charlie to db_datareader, which only lets him read data. Now, when Bob accidentally tries to delete the entire Orders table, the database will refuse because his role does not include that permission.
Another critical concept is the difference between logins and users. A login is a server-level identity — it lets you connect to the SQL Server instance. A user is a database-level identity — it is mapped to a login and defines what you can do inside a specific database. So you might have one login (the same username and password) that is mapped to a user in the Sales database and a different user in the HR database, each with completely different roles. This separation means you can control access independently for every database, even if the person uses the same credentials to connect.
Why does all of this matter for the DP-300 exam? The exam will ask you to choose the correct authentication method for a given scenario, to assign the right role to a user, and to understand how to migrate from SQL authentication to Microsoft Entra ID authentication. You will need to know the exact syntax for creating logins and users, and you will need to know that Microsoft Entra ID authentication is the recommended modern approach because it centralises identity management and adds layers of security like multi-factor authentication.
Choose authentication method
Decide whether to use SQL authentication or Microsoft Entra ID authentication. For most modern environments, choose Microsoft Entra ID because it supports multi-factor authentication and centralised user management.
Create server-level login (if using SQL authentication)
Use the CREATE LOGIN T-SQL command to create a username and password that allows connection to the SQL Server instance. This does not grant access to any database yet.
Create database-level user
In the target database, use CREATE USER to map a user to an existing login (or to a Microsoft Entra ID identity). This step creates a database identity that can be assigned permissions.
Assign roles or permissions
Use ALTER ROLE to add the user to a built-in role like db_datareader or db_datawriter, or use GRANT to give specific permissions. This step defines exactly what the user can do inside the database.
Test and audit access
Ask the user to connect and perform their tasks. Enable auditing to log all login attempts and queries. Review logs regularly to catch unauthorised access attempts or permission gaps.
An IT professional tasked with managing a company’s Azure SQL databases will spend a significant portion of their day managing who has access to what. Here is a realistic scenario.
Your company, FreshBake, runs an online bakery ordering platform. The database holds customer names, addresses, credit card details, and order history. The company has five employees: Sam (the CTO), Priya (a developer), Lina (a customer support agent who needs to view order details), Jake (a data analyst who runs sales reports), and an automated application called OrderAPI that places new orders from the website. Your job is to set up access so that everyone can do their job without any risk of data leaking or being corrupted.
You begin by deciding on the authentication method. Because your company already uses Microsoft 365 for email, you choose Microsoft Entra ID authentication for all human users. This means Sam, Priya, Lina, and Jake will log into the database using their existing work email and password, plus a multi-factor authentication prompt on their phones. For the OrderAPI application, you cannot use a human identity, so you create a managed identity — a special identity that Azure manages automatically — for the application to use.
Next, you create logins. In the master database of your Azure SQL server, you run a command to create a Microsoft Entra ID login for Sam, Priya, Lina, and Jake. You also configure the server to allow Microsoft Entra ID authentication. Then, for the OrderAPI application, you create a contained database user that maps directly to the managed identity, so it can connect without needing a server-level login.
Now comes authorisation. In the database that holds order data, you create users for each of these identities. For Sam, you add him to the db_owner role because he needs full control for maintenance. For Priya, you add her to the db_ddladmin role so she can create and alter tables, but not delete data. For Lina, you add her to db_datareader so she can look up orders for customers. For Jake, you also add him to db_datareader, but you also grant him explicit permission to execute a specific stored procedure that generates monthly sales summaries. For the OrderAPI application, you add it to db_datawriter and db_datareader so it can both read and write order records.
Finally, you monitor access. You enable auditing in Azure SQL, which logs every login attempt and every query. When Lina calls you saying she cannot see a new order, you check the audit logs and discover she is trying to query a table she does not have permission to access. You adjust her role to fix the issue. A few weeks later, a security audit reveals that Jake was running suspiciously large exports of customer data. Because you used Microsoft Entra ID, you can immediately revoke Jake’s access from the Azure portal — you do not need to change anything in the database itself. The entire security model is working as intended, and the company passes its compliance review.
The DP-300 exam tests your understanding of identity and access for Azure SQL in several specific ways. You should expect multiple-choice questions where you must choose between SQL authentication and Microsoft Entra ID authentication for a given scenario. The clear pattern is this: if the question mentions multi-factor authentication, conditional access, or centralised user management, the answer is almost always Microsoft Entra ID authentication. If the question involves legacy applications that cannot use modern authentication or isolated environments without internet access, the answer might be SQL authentication.
Another common trap is the difference between a server-level login and a database-level user. The exam loves to present a scenario where a user connects to the server but cannot see any databases. The answer is often that the login exists but no user has been created in the target database, or the user has not been mapped to the login. You need to remember that creating a login alone does not grant access to any database — you must then create a user in the specific database and assign roles.
The exam also tests built-in database roles heavily. Memorise the following: - db_owner — full control over the database - db_datareader — read all data - db_datawriter — insert, update, delete all data - db_ddladmin — run DDL (create, alter, drop) commands - db_securityadmin — manage permissions and roles inside the database
Expect questions that ask you to assign a role to a user for a specific job function. For example: 'A support agent needs to view customer data but must never change it. Which role should you assign?' The answer is db_datareader.
Another frequent topic is contained databases. A contained database is a database that stores its own user information, rather than relying on server-level logins. This makes the database easier to move to another server. The exam will ask when you should use contained database users versus server-level logins. The answer typically relates to portability — if you plan to move the database to a different server frequently, contained users are better.
Watch out for questions that mention 'Azure SQL Managed Instance' versus 'Azure SQL Database'. The authentication and authorisation concepts are the same, but Managed Instance supports more SQL Server features, including Windows authentication. The exam will test whether you know which features are available in each service tier.
Finally, you must know how to use T-SQL commands for identity management. You may be asked to identify the correct syntax for creating a login, creating a user, or granting a permission. For example:
CREATE LOGIN MyLogin WITH PASSWORD = 'Str0ng!Pass'
CREATE USER MyUser FOR LOGIN MyLogin
ALTER ROLE db_datareader ADD MEMBER MyUser
If you can write these commands from memory and understand when to use each one, you will be well prepared for this section of the exam.
Authentication is the process of verifying who you are, and authorisation is the process of determining what you are allowed to do after authentication succeeds.
Azure SQL supports two authentication methods: SQL authentication (username and password stored in SQL Server) and Microsoft Entra ID authentication (identity verified by Microsoft's cloud directory).
A server-level login lets you connect to the server, but you must create a database-level user mapped to that login before you can access any specific database.
Built-in database roles like db_owner, db_datareader, and db_datawriter simplify permission management by grouping common sets of permissions into named roles.
Contained database users store identity information inside the database itself, making the database portable across servers without needing to recreate logins.
Microsoft Entra ID authentication is the recommended approach for production environments because it supports multi-factor authentication and centralised user management.
These come up on the exam all the time. Here's how to tell them apart.
SQL Authentication
Username and password stored inside SQL Server
No multi-factor or conditional access support
Requires manual password management and rotation
Microsoft Entra ID Authentication
Identity verified by Microsoft Entra ID cloud service
Supports multi-factor authentication and conditional access
Centralised password policies and automated security features
Server-Level Login
Created at the server level in the master database
Grants ability to connect to the SQL Server instance
Single login can map to multiple users in different databases
Database-Level User
Created inside a specific user database
Grants access only to that specific database
Must be created separately for each database
db_datareader Role
Allows SELECT (read) on all tables and views
Cannot insert, update, or delete data
Used for reporting or read-only access scenarios
db_datawriter Role
Allows INSERT, UPDATE, DELETE on all tables
Cannot read data unless combined with db_datareader
Used for applications or users that need to write data
Mistake
Once I create a SQL login, that person automatically has access to all databases on the server.
Correct
A SQL login only grants access to connect to the server. To access a specific database, you must create a user for that login inside each database and assign permissions.
People confuse the server-level login with database-level access because in many simple setups, the administrator creates a login and a user in the same step.
Mistake
Microsoft Entra ID authentication is only for human users, not for applications.
Correct
Applications can use managed identities or service principals in Microsoft Entra ID to authenticate without human interaction.
Because Microsoft Entra ID is commonly associated with employee login pages, people overlook its application identity features.
Mistake
If I give a user the db_datareader role, they can also update data because reading includes writing.
Correct
db_datareader only grants SELECT permission. A separate role, db_datawriter, is required for INSERT, UPDATE, and DELETE operations.
The word 'reader' sounds passive, but beginners often assume read access implies the ability to change data, which it does not.
Mistake
SQL authentication is more secure than Microsoft Entra ID authentication because the passwords are stored inside the database.
Correct
Microsoft Entra ID authentication is more secure because it supports multi-factor authentication, conditional access policies, and centralised password management.
Beginners mistakenly think local storage is safer than cloud storage, not realising that Microsoft Entra ID uses enterprise-grade security and encryption.
Mistake
Contained database users are the same as server-level logins, just named differently.
Correct
Contained database users are stored entirely inside the database and do not require a server-level login. This makes the database portable to other servers.
The similar purpose of granting database access leads people to conflate two fundamentally different implementation methods.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
A login is a server-level identity that lets you connect to the SQL Server instance. A user is a database-level identity that is mapped to a login and defines what you can do inside a specific database.
Yes, you can use a managed identity assigned to the virtual machine, or a service principal, to authenticate without storing any credentials in the application code.
Use CREATE USER with the WITH PASSWORD = 'password' clause inside a contained database. This creates a user that does not require a server-level login.
Members of the db_owner role have full control over the database, including the ability to modify schema, add users, and delete data.
Microsoft Entra ID authentication provides features like multi-factor authentication, conditional access policies, and centralised password management, making it more secure and easier to manage for organisations already using Microsoft 365.
No, db_datareader only grants SELECT permission. To modify data, the user needs db_datawriter or explicit INSERT, UPDATE, DELETE permissions.
You've finished Managing Identity and Access for Azure SQL. Continue through the DP-300 study guide to build a complete picture of the exam.
Done with this chapter?