Azure SQL Add User to Database for Collaboration and Security

Adding users to a database in Azure SQL is a crucial step for collaboration and security. You can create a new database user by using the CREATE USER statement in SQL Server Management Studio (SSMS).

To add a user to a database, you'll need to provide a username and password. The username can be a combination of letters, numbers, and special characters, but it cannot be the same as an existing username in the database.

A database user can be mapped to a login in Azure Active Directory (Azure AD) or an on-premises Active Directory. This allows you to use Azure AD or Active Directory authentication for your database users.

You can also specify a default schema for the new user. This is useful if you want to control which schema the user can access.

User management is a crucial aspect of Azure SQL, and there are several ways to add users to your database.

To add a user to Azure SQL database, you can use SQL Server Management Studio (SSMS) or Azure Data Studio from on-premises, connect to the Azure SQL server as an admin, and create a login for the user in the Master database. This will allow the user to access the database where you need to create the user.

You can also add users to Azure SQL database from the portal by clicking on the 'Query editor (preview)' button, logging in to the database using SQL server authentication or Microsoft Entra authentication, and then performing the necessary steps to grant access and permissions.

Creating contained database users is another option, where you can create a user account in the database to which a user needs access, and this approach is preferred when the user must access multiple databases and you wish to keep the passwords synchronized.

Here are the different methods for creating logins and users:

Note that you can also use groups for efficient access management, where you put users into a security group, create a contained database user for the group, and add one or more database users to custom or built-in database roles with specific permissions.

You can create additional logins with full or partial administrative permissions in Azure SQL Database.

To create a Microsoft Entra administrator account with full administrative permissions, you can follow the steps outlined in Example 2. This involves enabling Microsoft Entra authentication and adding a Microsoft Entra admin.

Only the server admin account or the Microsoft Entra admin account can add or remove other logins to or from server roles in Azure SQL Database.

In SQL Managed Instance, you can create SQL logins with full administrative permissions, but note that the dbmanager and loginmanager roles do not pertain to Azure SQL Managed Instance deployments.

In Azure Synapse dedicated SQL pool and serverless SQL pool, you can create SQL logins with limited administrative permissions.

Here are the options for creating additional logins with administrative permissions in Azure SQL Database:

Security Administration is a crucial aspect of User Management in Azure SQL Database. Managing security at the database-level is almost identical to on-premises SQL Server, with differences only in the parameters available.

You can manage server-level security in the master database and through the Azure portal. To create a login and associated user that can create databases or other logins, you must be connected to the master database.

The loginmanager database role in Azure SQL Database has permission to create logins. Only the server-level principal login (created by the provisioning process) or members of the loginmanager database role can create new logins.

You can create a Service Principal for database authentication using the Azure CLI. This requires the Application Developer role, which can be enabled through the Azure Privileged Identity Management (PIM).

To create a Service Principal, you'll need to execute a command in the Azure CLI, and store the resulting information, such as the password, appId, displayName, and tenant.

Here's a summary of the key differences in security administration between on-premises SQL Server and Azure SQL Database:

Authentication is the process of proving the user is who they claim to be, and in Azure SQL, this is done using one of two authentication methods: SQL authentication or Microsoft Entra authentication.

SQL authentication involves submitting a user account name and password to establish a connection, with the password stored in the master database or the database containing the user accounts.

Microsoft Entra authentication, on the other hand, uses credential information stored in Microsoft Entra ID (formerly Azure Active Directory) to authenticate the user.

A login is an individual account in the master database that can be linked to a user account in one or more databases, with the credential information stored with the login.

A user account, by contrast, is an individual account in any database that might be linked to a login, with the credential information stored with the user account.

There are two types of logins: one stored in the master database and one stored in an individual database.

Here's a breakdown of the two authentication methods:

It's worth noting that Azure SQL Database only enforces password complexity for password policy, while Azure SQL Managed Instance has its own password policy.

To add a user to an Azure SQL database, you can use SQL Server Management Studio (SSMS) or Azure Data Studio from on-premises. Connect to the Azure SQL server as an admin via SSMS or Azure Data Studio.

You can grant roles to the user on the database, such as db_datareader and db_datawriter roles. To grant select access to a specific table, you can use a script.

You can also use managed identity to access the Azure SQL database. Managed identity is a service principal associated with resources in Azure, such as Azure Data Factory. Create a user in the Azure SQL database for Azure Data Factory and grant roles to this newly created user.

Alternatively, you can add a user to the Azure SQL database from the Azure portal. Click the 'Query editor (preview)' button on the left sidebar and login to the database using SQL server authentication or Microsoft Entra authentication.

When creating accounts for non-administrator users, you can create a login in the master database and create a user account in each database to which that user needs access. This approach is preferred when the user must access multiple databases and you wish to keep the passwords synchronized.

You can also create a user account in the database to which a user needs access, also called a contained user. With this approach, the user authentication information is stored in each database, and replicated to geo-replicated databases automatically.

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 and create a contained database user for the group.

Here is a summary of the steps to create a contained database user:

Note that you must use a strong password when creating a contained database user.

You can grant server-level permissions to a login by using the loginmanager and dbmanager database roles.

Only users in the master database can be added to these database roles. To create logins or databases, you must be connected to the master database.

To assign SQL Database server-level roles, you need to follow these steps:

The code example below shows how to create a login named login1, and a corresponding database user named login1User that can create databases or other logins while connected to the master database: