How to Create a New User in a SQL Azure Database

SQL Azure is a cloud-based relational database service that offers high availability, scalability, and security for your data. SQL Azure supports the same Transact-SQL (T-SQL) syntax and features as SQL Server, so you can use the same tools and techniques to manage your databases. However, there are some differences and limitations that you need to be aware of, especially when it comes to creating and managing users and logins.

In this blog post, I will show you how to create a new user in a SQL Azure database, and how to grant or revoke permissions to the user. I will also explain some of the differences between SQL Azure and SQL Server regarding user management.

What is the Difference Between a Login and a User in SQL Azure?

A login is an identity that can connect to the SQL Azure server. A user is an identity that can access a specific database within the server. A login can have one or more users associated with it, but a user can only belong to one login.

In SQL Server, you can create logins and users using the CREATE LOGIN and CREATE USER statements, respectively. You can also use the SQL Server Management Studio (SSMS) graphical interface to create and manage logins and users.

In SQL Azure, however, you cannot create logins using T-SQL or SSMS. You can only create logins using the Azure portal or PowerShell. You can still create users using T-SQL or SSMS, but you need to specify the login name as the principal name for the user.

How to Create a New Login in SQL Azure?

To create a new login in SQL Azure, you need to use the Azure portal or PowerShell. You cannot use T-SQL or SSMS for this task.

To create a new login using the Azure portal, follow these steps:

Sign in to the [Azure portal] with your Azure account.
Navigate to SQL databases > your database > Query editor.
Enter your server admin login and password, and click OK.
In the query editor, enter the following statement:

CREATE LOGIN <login-name> WITH PASSWORD = '<password>';

1	CREATE LOGIN WITH PASSWORD = '';

Replace <login-name> with the name of the login you want to create, and <password> with a strong password that meets the SQL Azure password policy.

Click Run to execute the statement.

To create a new login using PowerShell, follow these steps:

Install and configure [Azure PowerShell] on your machine.
Open PowerShell and connect to your Azure account using the following command:

Connect-AzAccount

1	Connect-AzAccount

Select your subscription using the following command:

Select-AzSubscription -SubscriptionId <subscription-id>

1	Select-AzSubscription -SubscriptionId

Replace <subscription-id> with the ID of your Azure subscription.

Create a new login using the following command:

New-AzSqlDatabaseServerAdministrator -ResourceGroupName <resource-group-name> -ServerName <server-name> -Login <login-name> -Password <password>

1	New-AzSqlDatabaseServerAdministrator -ResourceGroupName -ServerName -Login -Password

Replace <resource-group-name> with the name of your resource group, <server-name> with the name of your SQL Azure server, <login-name> with the name of the login you want to create, and <password> with a strong password that meets the SQL Azure password policy.

How to Create a New User in a SQL Azure Database?

To create a new user in a SQL Azure database, you can use T-SQL or SSMS. You need to specify the login name as the principal name for the user.

To create a new user using T-SQL, follow these steps:

Connect to your SQL Azure database using SSMS or any other tool that supports T-SQL.
In the query window, enter the following statement:

CREATE USER <user-name> FOR LOGIN <login-name>;

1	CREATE USER FOR LOGIN ;

Replace <user-name> with the name of the user you want to create, and <login-name> with the name of an existing login that you want to associate with the user.

Execute the statement.

To create a new user using SSMS, follow these steps:

Connect to your SQL Azure database using SSMS.
In Object Explorer, expand Databases > your database > Security > Users.
Right-click on Users and select New User….
In the User – New dialog box, enter the following information:
- User name: Enter the name of the user you want to create.
- User type: Select SQL user with login.
- Login name: Enter the name of an existing login that you want to associate with the user.
Click OK to create the user.

How to Grant or Revoke Permissions to a User in a SQL Azure Database?

To grant or revoke permissions to a user in a SQL Azure database, you can use T-SQL or SSMS. You can grant or revoke permissions at the database level or at the object level.

To grant or revoke permissions at the database level, you can use the GRANT or REVOKE statements, respectively. For example, to grant the user user1 the permission to create tables in the database, you can use the following statement:

GRANT CREATE TABLE TO user1;

1	GRANT CREATE TABLE TO user1;

To revoke the same permission, you can use the following statement:

REVOKE CREATE TABLE FROM user1;

1	REVOKE CREATE TABLE FROM user1;

You can also use the database roles to grant or revoke permissions to a user. Database roles are predefined groups of permissions that you can assign to a user. For example, to grant the user user1 the db_datareader role, which allows the user to read data from all tables and views in the database, you can use the following statement:

ALTER ROLE db_datareader ADD MEMBER user1;

1	ALTER ROLE db_datareader ADD MEMBER user1;

To remove the same role, you can use the following statement:

ALTER ROLE db_datareader DROP MEMBER user1;

1	ALTER ROLE db_datareader DROP MEMBER user1;

To grant or revoke permissions at the object level, such as tables, views, stored procedures, etc., you can use the GRANT or REVOKE statements with the ON clause, respectively. For example, to grant the user user1 the permission to select data from the table table1, you can use the following statement:

GRANT SELECT ON table1 TO user1;

1	GRANT SELECT ON table1 TO user1;

To revoke the same permission, you can use the following statement:

REVOKE SELECT ON table1 FROM user1;

1	REVOKE SELECT ON table1 FROM user1;

You can also use SSMS graphical interface to grant or revoke permissions to a user. To do this, follow these steps:

Connect to your SQL Azure database using SSMS.
In Object Explorer, expand Databases > your database > Security > Users.
Right-click on the user you want to grant or revoke permissions and select Properties.
In the User Properties dialog box, select Securables in the left pane.
In the right pane, click Search… to select the objects you want to grant or revoke permissions.
In the Select Objects dialog box, select one of the following options:
- All objects of types…: Select this option if you want to grant or revoke permissions for all objects of certain types in the database. For example, if you want to grant or revoke permissions for all tables in the database, select this option and check Tables in the list below.
- Specific objects…: Select this option if you want to grant or revoke permissions for specific objects in the database. For example, if you want to grant or revoke permissions for a specific table in the database, select this option and click Object Types… to select Tables, then click Browse… to select the table from the list.
Click OK to return to the User Properties dialog box.
In the right pane, under Permissions, check or uncheck the permissions you want to grant or revoke for the selected objects.
Click OK to apply your changes.

Conclusion

In this blog post, I showed you how to create a new user in a SQL Azure database, and how to grant or revoke permissions to the user. I also explained some of the differences between SQL Azure and SQL Server regarding user management.

I hope you found this post helpful and learned something new. If you have any questions or feedback, please leave a comment below.