Adding Users to Your SQL Azure Database

[This article was contributed by the SQL Azure team.]

When you generate a SQL Azure server via the SQL Azure portal, you generate a user name and password at the same time. This is your administrative account it has access to all databases on that server. However, you might want to give other people access to some or all of your databases on that server, with full or restricted permissions. This article will show you how to generate additional user accounts on your SQL Azure databases.

Currently, the SQL Azure portal does not allow you to administrate additional users and logins, in order to do this you need to use Transact-SQL. The easiest way to execute Transact -SQL against SQL Azure is to use the SQL Server Management Studio 2008 R2. Learn more about attaching it to SQL Azure here. SQL Server Management Studio 2008 R2 will list the users and logins associated with the databases; however, at this time it does not provide a graphical user interface for creating the users and logins.

Generating Logins

Logins are server wide login and password pairs, where the login has the same password across all databases. Here is some sample Transact-SQL that creates a login:

 CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';

You must be connected to the master database on SQL Azure with the administrative login (which you get from the SQL Azure portal) to execute the CREATE LOGIN command. Some of the common SQL Server logins can be used like sa, Admin, root, for a complete list click here.

Creating Users

Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database. Here is some sample Transact-SQL that creates a user:

 CREATE USER readonlyuser FROM LOGIN readonlylogin;

User Permissions

Just creating the user does not give them permissions to the database. You have to grant them access. In the Transact-SQL example below the readonlyuser is given read only permissions to the database via the db_datareader role.

 EXEC sp_addrolemember 'db_datareader', 'readonlyuser';

Deleting Users and Logins

Fortunately, SQL Server Management Studio 2008 R2 does allow you to delete users and logins. To do this traverse the Object Explorer tree and find the Security node, right click on the user or login and choose Delete.

More Information

One thing to note is that SQL Azure does not allow the USE Transact-SQL statement, which means that you cannot create a single script to execute both the CREATE LOGIN and CREATE USER statements, since those statements need to be executed on different databases.

There is additional information about Managing Databases and Logins in SQL Azure on MSDN.

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.