Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics
Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics
In this article, you learn about:
- Configuration options for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics that enable users to perform administrative tasks and to access data stored in these databases.
- Access and authorization configuration after a new server is initially created.
- How to add logins and user accounts in the
master
database and then grant these accounts administrative permissions. - How to add user accounts in user databases, either associated with logins or as contained user accounts.
- Configure user accounts with permissions in user databases by using database roles and explicit permissions.
Important
Databases in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse are referred to collectively in the remainder of this article as databases, and the server is referring to the logical server that manages databases for Azure SQL Database and Azure Synapse.
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
Authentication and authorization
Authentication is the process of proving the user is who they claim to be. A user connects to a database using a user account. When a user attempts to connect to a database, they provide a user account and authentication information. The user is authenticated using one of the following two authentication methods:
-
With this authentication method, the user submits a user account name and associated password to establish a connection. This password is stored in the
master
database for user accounts linked to a login or stored in the database containing the user accounts not linked to a login.Note
Azure SQL Database only enforces password complexity for password policy. For password policy in Azure SQL Managed Instance, see Azure SQL Managed Instance frequently asked questions (FAQ).
Microsoft Entra authentication
With this authentication method, the user submits a user account name and requests that the service use the credential information stored in Microsoft Entra ID (formerly Azure Active Directory).
Logins and users: A user account in a database can be associated with a login that is stored in the master
database or can be a user name that is stored in an individual database.
- A login is an individual account in the
master
database, to which a user account in one or more databases can be linked. With a login, the credential information for the user account is stored with the login. - A user account is an individual account in any database that might be, but does not have to be, linked to a login. With a user account that is not linked to a login, the credential information is stored with the user account.
Authorization to access data and perform various actions are managed using database roles and explicit permissions. Authorization refers to the permissions assigned to a user, and determines what that user is allowed to do. Authorization is controlled by your user account's database role memberships and object-level permissions. As a best practice, you should grant users the least privileges necessary.
Existing logins and user accounts after creating a new database
When you first deploy Azure SQL, you can specify a login name and a password for a special type of administrative login, the Server admin. The following configuration of logins and users in the master and user databases occurs during deployment:
- A SQL login with administrative privileges is created using the login name you specified. A login is an individual account for logging in to SQL Database, SQL Managed Instance, and Azure Synapse.
- This login is granted full administrative permissions on all databases as a server-level principal. The login has all available permissions and can't be limited. In a SQL Managed Instance, this login is added to the sysadmin fixed server role (this role does not exist in Azure SQL Database).
- When this account signs into a database, they are matched to the special user account
dbo
(user account, which exists in each user database. The dbo user has all database permissions in the database and is member of thedb_owner
fixed database role. Additional fixed database roles are discussed later in this article.
To identify the Server admin account, open the Azure portal, and navigate to the Properties tab of your logical server or managed instance:
Important
The name of the Server admin account can't be changed after it has been created. To reset the password for the server admin, go to the Azure portal, select SQL Servers, select the server from the list, and then select Reset Password. To reset the password for the SQL Managed Instance, go to the Azure portal, select the instance, and select Reset password. You can also use PowerShell or the Azure CLI.
Create additional logins and users having administrative permissions
At this point, your server or managed instance is only configured for access using a single SQL login and user account. To create additional logins with full or partial administrative permissions, you have the following options (depending on your deployment mode):
Create a Microsoft Entra administrator account with full administrative permissions
Enable Microsoft Entra authentication and add a Microsoft Entra admin. One Microsoft Entra account can be configured as an administrator of the Azure SQL deployment with full administrative permissions. This account can be either an individual or security group account. A Microsoft Entra admin must be configured if you want to use Microsoft Entra accounts to connect to SQL Database, SQL Managed Instance, or Azure Synapse. For detailed information on enabling Microsoft Entra authentication for all Azure SQL deployment types, see the following articles:
In SQL Managed Instance, create SQL logins with full administrative permissions
- Create an additional SQL login in the
master
database. - Add the login to the sysadmin fixed server role using the ALTER SERVER ROLE statement. This login will have full administrative permissions.
- Alternatively, create a Microsoft Entra login using the CREATE LOGIN syntax.
Note
The
dbmanager
andloginmanager
roles do not pertain to Azure SQL Managed Instance deployments.- Create an additional SQL login in the
In SQL Database, create SQL logins with limited administrative permissions
- Create an additional SQL login in the
master
database. - Add the Login to the
##MS_DatabaseManager##
,##MS_LoginManager##
and##MS_DatabaseConnector##
server level roles using the ALTER SERVER ROLE statement.
Members of special
master
database roles for Azure SQL Database have authority to create and manage databases or to create and manage logins. In databases created by a user that is a member of thedbmanager
role, the member is mapped to thedb_owner
fixed database role and can log into and manage that database using thedbo
user account. These roles have no explicit permissions outside of themaster
database.Important
You can't create an additional SQL login with full administrative permissions in Azure SQL Database. Only the server admin account or the Microsoft Entra admin account (which can be a Microsoft Entra group) can add or remove other logins to or from server roles. This is specific to Azure SQL Database.
- Create an additional SQL login in the
In Azure Synapse dedicated SQL pool, create SQL logins with limited administrative permissions
- Create an additional SQL login in the
master
database. - Create a user account in the
master
database associated with this new login. - Add the user account to the
dbmanager
, theloginmanager
role, or both in themaster
database using the sp_addrolemember statement.
- Create an additional SQL login in the
In Azure Synapse serverless SQL pool, create SQL logins with limited administrative permissions
- Create an additional SQL login in the
master
database. - Add the SQL login to the sysadmin fixed server role using the ALTER SERVER ROLE statement.
- Alternatively, create a Microsoft Entra login using the CREATE LOGIN syntax.
- Create an additional SQL login in the
Create accounts for non-administrator users
You can create accounts for non-administrative users using one of two methods:
Create a login
Create a SQL login in the
master
database. Then create a user account in each database to which that user needs access and associate the user account with that login. This approach is preferred when the user must access multiple databases and you wish to keep the passwords synchronized. However, this approach has complexities when used with geo-replication as the login must be created on both the primary server and the secondary server(s). For more information, see Configure and manage Azure SQL Database security for geo-restore or failover.Create a user account
Create a user account in the database to which a user needs access (also called a contained user).
- With SQL Database, you can always create this type of user account.
- With SQL Managed Instance supporting Microsoft Entra server principals, you can create user accounts to authenticate to the SQL Managed Instance without requiring database users to be created as a contained database user.
With this approach, the user authentication information is stored in each database, and replicated to geo-replicated databases automatically. However, if the same account exists in multiple databases and you are using SQL authentication, you must keep the passwords synchronized manually. Additionally, if a user has an account in different databases with different passwords, remembering those passwords can become a problem.
Important
To create contained users mapped to Microsoft Entra identities, you must be logged in using a Microsoft Entra account in the database in Azure SQL Database. In SQL Managed Instance, a SQL login with sysadmin
permissions can also create a Microsoft Entra login or user.
For examples showing how to create logins and users, see:
- Create login for Azure SQL Database
- Create login for Azure SQL Managed Instance
- Create login for Azure Synapse
- Create user
- Creating Microsoft Entra contained users
Tip
For a security tutorial that includes creating users in Azure SQL Database, see Tutorial: Secure Azure SQL Database.
Using fixed and custom database roles
After creating a user account in a database, either based on a login or as a contained user, you can authorize that user to perform various actions and to access data in a particular database. You can use the following methods to authorize access:
Fixed database roles
Add the user account to a fixed database role. There are 9 fixed database roles, each with a defined set of permissions. The most common fixed database roles are: db_owner, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, and db_denydatareader. db_owner is commonly used to grant full permission to only a few users. The other fixed database roles are useful for getting a simple database in development quickly, but are not recommended for most production databases. For example, the db_datareader fixed database role grants read access to every table in the database, which is more than is strictly necessary.
To add a user to a fixed database role:
- In Azure SQL Database and Azure Synapse serverless SQL pool, use the ALTER ROLE statement. For examples, see ALTER ROLE examples
- In Azure Synapse dedicated SQL pool, use the sp_addrolemember statement. For examples, see sp_addrolemember examples.
Custom database role
Create a custom database role using the CREATE ROLE statement. A custom role enables you to create your own user-defined database roles and carefully grant each role the least permissions necessary for the business need. You can then add users to the custom role. When a user is a member of multiple roles, they aggregate the permissions of them all.
Grant permissions directly
Grant the user account permissions directly. There are over 100 permissions that can be individually granted or denied in SQL Database. Many of these permissions are nested. For example, the
UPDATE
permission on a schema includes theUPDATE
permission on each table within that schema. As in most permission systems, the denial of a permission overrides a grant. Because of the nested nature and the number of permissions, it can take careful study to design an appropriate permission system to properly protect your database. Start with the list of permissions at Permissions (Database Engine) and review the poster size graphic of the permissions.
Using groups
Efficient access management uses permissions assigned to Active Directory security groups and fixed or custom roles instead of to individual users.
When using Microsoft Entra authentication, put Microsoft Entra users into a Microsoft Entra security group. Create a contained database user for the group. Add one or more database users as a member to custom or builtin database roles with the specific permissions appropriate to that group of users.
When using SQL authentication, create contained database users in the database. Place one or more database users into a custom database role with specific permissions appropriate to that group of users.
Note
You can also use groups for non-contained database users.
You should familiarize yourself with the following features that can be used to limit or elevate permissions:
- Impersonation and module-signing can be used to securely elevate permissions temporarily.
- Row-Level Security can be used limit which rows a user can access.
- Data Masking can be used to limit exposure of sensitive data.
- Stored procedures can be used to limit the actions that can be taken on the database.