Controlling and granting database access to SQL Database and SQL Data Warehouse
This topic applies to Azure SQL server, and to SQL Database and SQL Data Warehouse databases created on the Azure SQL server. For simplicity, SQL Database is used when referring to both SQL Database and SQL Data Warehouse.
For a tutorial, see Secure your Azure SQL Database.
Unrestricted administrative accounts
There are two administrative accounts (Server admin and Active Directory admin) that act as administrators. To identify these administrator accounts for your SQL server, open the Azure portal, and navigate to the properties of your SQL server.
- Server admin
When you create an Azure SQL server, you must designate a Server admin login. SQL server creates that account as a login in the master database. This account connects using SQL Server authentication (user name and password). Only one of these accounts can exist.
- Azure Active Directory admin
One Azure Active Directory account, either an individual or security group account, can also be configured as an administrator. It is optional to configure an Azure AD administrator, but an Azure AD administrator must be configured if you want to use Azure AD accounts to connect to SQL Database. For more information about configuring Azure Active Directory access, see Connecting to SQL Database or SQL Data Warehouse By Using Azure Active Directory Authentication and SSMS support for Azure AD MFA with SQL Database and SQL Data Warehouse.
The Server admin and Azure AD admin accounts has the following characteristics:
- Are the only accounts that can automatically connect to any SQL Database on the server. (To connect to a user database, other accounts must either be the owner of the database, or have a user account in the user database.)
- These accounts enter user databases as the
dbouser and they have all the permissions in the user databases. (The owner of a user database also enters the database as the
- Do not enter the
masterdatabase as the
dbouser, and have limited permissions in master.
- Are not members of the standard SQL Server
sysadminfixed server role, which is not available in SQL database.
- Can create, alter, and drop databases, logins, users in master, and server-level firewall rules.
- Can add and remove members to the
- Can view the
Configuring the firewall
When the server-level firewall is configured for an individual IP address or range, the SQL server admin and the Azure Active Directory admin can connect to the master database and all the user databases. The initial server-level firewall can be configured through the Azure portal, using PowerShell or using the REST API. Once a connection is made, additional server-level firewall rules can also be configured by using Transact-SQL.
Administrator access path
When the server-level firewall is properly configured, the SQL server admin and the Azure Active Directory admin can connect using client tools such as SQL Server Management Studio or SQL Server Data Tools. Only the latest tools provide all the features and capabilities. The following diagram shows a typical configuration for the two administrator accounts.
When using an open port in the server-level firewall, administrators can connect to any SQL Database.
Connecting to a database by using SQL Server Management Studio
For a walk-through of creating a server, a database, server-level firewall rules, and using SQL Server Management Studio to query a database, see Get started with Azure SQL Database servers, databases, and firewall rules by using the Azure portal and SQL Server Management Studio.
It is recommended that you always use the latest version of Management Studio to remain synchronized with updates to Microsoft Azure and SQL Database. Update SQL Server Management Studio.
Additional server-level administrative roles
In addition to the server-level administrative roles discussed previously, SQL Database provides two restricted administrative roles in the master database to which user accounts can be added that grant permissions to either create databases or manage logins.
One of these administrative roles is the dbmanager role. Members of this role can create new databases. To use this role, you create a user in the
master database and then add the user to the dbmanager database role. To create a database, the user must be a user based on a SQL Server login in the master database or contained database user based on an Azure Active Directory user.
- Using an administrator account, connect to the master database.
Optional step: Create a SQL Server authentication login, using the CREATE LOGIN statement. Sample statement:
CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';
Use a strong password when creating a login or contained database user. For more information, see Strong Passwords.
To improve performance, logins (server-level principals) are temporarily cached at the database level. To refresh the authentication cache, see DBCC FLUSHAUTHCACHE.
In the master database, create a user by using the CREATE USER statement. The user can be an Azure Active Directory authentication contained database user (if you have configured your environment for Azure AD authentication), or a SQL Server authentication contained database user, or a SQL Server authentication user based on a SQL Server authentication login (created in the previous step.) Sample statements:
CREATE USER [email@example.com] FROM EXTERNAL PROVIDER; -- To create a user with Azure Active Directory CREATE USER Ann WITH PASSWORD = '<strong_password>'; -- To create a SQL Database contained database user CREATE USER Mary FROM LOGIN Mary; -- To create a SQL Server user based on a SQL Server authentication login
Add the new user, to the dbmanager database role by using the ALTER ROLE statement. Sample statements:
ALTER ROLE dbmanager ADD MEMBER Mary; ALTER ROLE dbmanager ADD MEMBER [firstname.lastname@example.org];
The dbmanager is a database role in master database so you can only add a database user to the dbmanager role. You cannot add a server-level login to database-level role.
If necessary, configure a firewall rule to allow the new user to connect. (The new user might be covered by an existing firewall rule.)
Now the user can connect to the master database and can create new databases. The account creating the database becomes the owner of the database.
The other administrative role is the login manager role. Members of this role can create new logins in the master database. If you wish, you can complete the same steps (create a login and user, and add a user to the loginmanager role) to enable a user to create new logins in the master. Usually logins are not necessary as Microsoft recommends using contained database users, which authenticate at the database-level instead of using users based on logins. For more information, see Contained Database Users - Making Your Database Portable.
Generally, non-administrator accounts do not need access to the master database. Create contained database users at the database level using the CREATE USER (Transact-SQL) statement. The user can be an Azure Active Directory authentication contained database user (if you have configured your environment for Azure AD authentication), or a SQL Server authentication contained database user, or a SQL Server authentication user based on a SQL Server authentication login (created in the previous step.) For more information, see Contained Database Users - Making Your Database Portable.
To create users, connect to the database, and execute statements similar to the following examples:
CREATE USER Mary FROM LOGIN Mary; CREATE USER [email@example.com] FROM EXTERNAL PROVIDER;
Initially, only one of the administrators or the owner of the database can create users. To authorize additional users to create new users, grant that selected user the
ALTER ANY USER permission, by using a statement such as:
GRANT ALTER ANY USER TO Mary;
To give additional users full control of the database, make them a member of the db_owner fixed database role using the
ALTER ROLE statement.
ALTER ROLE db_owner ADD MEMBER Mary;
One common reason to create a database user based on a logical server login is for users that need access to multiple databases. Since contained database users are individual entities, each database maintains its own user and its own password. This can cause overhead as the user must then remember each password for each database, and it can become untenable when having to change multiple passwords for many databases. However, when using SQL Server Logins and high availability (active geo-replication and failover groups), the SQL Server logins must be set manually at each server. Otherwise, the database user will no longer be mapped to the server login after a failover occurs, and will not be able to access the database post failover. For more information on configuring logins for geo-replication, please see Configure and manage Azure SQL Database security for geo-restore or failover.
Configuring the database-level firewall
As a best practice, non-administrator users should only have access through the firewall to the databases that they use. Instead of authorizing their IP addresses through the server-level firewall and giving them access to all databases, use the sp_set_database_firewall_rule statement to configure the database-level firewall. The database-level firewall cannot be configured by using the portal.
Non-administrator access path
When the database-level firewall is properly configured, the database users can connect using client tools such as SQL Server Management Studio or SQL Server Data Tools. Only the latest tools provide all the features and capabilities. The following diagram shows a typical non-administrator access path.
Groups and roles
Efficient access management uses permissions assigned to groups and roles instead of individual users.
When using Azure Active Directory authentication, put Azure Active Directory users into an Azure Active Directory group. Create a contained database user for the group. Place one or more database users into a database role and then assign permissions to the database role.
The database roles can be the built-in roles such as 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 usually more than is strictly necessary. It is far better to use the CREATE ROLE statement to create your own user-defined database roles and carefully grant each role the least permissions necessary for the business need. When a user is a member of multiple roles, they aggregate the permissions of them all.
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 the
UPDATE 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.
Considerations and restrictions
When managing logins and users in SQL Database, consider the following:
- You must be connected to the master database when executing the
- The database user corresponding to the Server admin login cannot be altered or dropped.
- US-English is the default language of the Server admin login.
- Only the administrators (Server admin login or Azure AD administrator) and the members of the dbmanager database role in the master database have permission to execute the
- You must be connected to the master database when executing the
CREATE/ALTER/DROP LOGINstatements. However using logins is discouraged. Use contained database users instead.
- To connect to a user database, you must provide the name of the database in the connection string.
- Only the server-level principal login and the members of the loginmanager database role in the master database have permission to execute the
ALTER LOGIN, and
- When executing the
CREATE/ALTER/DROP DATABASEstatements in an ADO.NET application, using parameterized commands is not allowed. For more information, see Commands and Parameters.
When executing the
CREATE/ALTER/DROP LOGINstatements, each of these statements must be the only statement in a Transact-SQL batch. Otherwise, an error occurs. For example, the following Transact-SQL checks whether the database exists. If it exists, a
DROP DATABASEstatement is called to remove the database. Because the
DROP DATABASEstatement is not the only statement in the batch, executing the following Transact-SQL statement results in an error.
IF EXISTS (SELECT [name] FROM [sys].[databases] WHERE [name] = N'database_name') DROP DATABASE [database_name]; GO
When executing the
CREATE USERstatement with the
FOR/FROM LOGINoption, it must be the only statement in a Transact-SQL batch.
- When executing the
ALTER USERstatement with the
WITH LOGINoption, it must be the only statement in a Transact-SQL batch.
CREATE/ALTER/DROPa user requires the
ALTER ANY USERpermission on the database.
- When the owner of a database role tries to add or remove another database user to or from that database role, the following error may occur: User or role 'Name' does not exist in this database. This error occurs because the user is not visible to the owner. To resolve this issue, grant the role owner the
VIEW DEFINITIONpermission on the user.
- To learn more about firewall rules, see Azure SQL Database Firewall.
- For an overview of all the SQL Database security features, see SQL security overview.
- For a tutorial, see Secure your Azure SQL Database.
- For information about views and stored procedures, see Creating views and stored procedures
- For information about granting access to a database object, see Granting Access to a Database Object