Intro

If you have used or are deciding on using an Azure hosted SQL Server database, you might also want the ability to govern the identities and credentials used to access your data.

When you create a SQL Database in SQL Azure, initially credentials are created for you, but they allow you to access and govern the entire SQL Azure instance. If you’d prefer to partition access to specific databases, how do you create and manage accounts? I’ll walk you through it.

Tools

There’s two ways you can manage credentials with SQL Azure, you can connect using SQL Management Studio or you can use the Windows Azure management console (browser based).

Each tool offers a slightly different value proposition – the browser based experience is a little slower and less intuitive, but can be accessed from anywhere without requiring SSMS to be installed. SSMS gives you integration with SSMS projects and also provides for more advanced tools, like query parsing.

No matter which tool you use, you’ll need to use TransactSQL to manage server logins and database user accounts. Let’s take a look at the T-SQL syntax you’ll need.

Managing Users – with T-SQL

SQL Azure unfortunately does not offer a graphical user interface for the management of logins and users. This means you’ll have to revert to running T-SQL queries if you wish to manipulate credentials.

Create a Login

Logins are server wide accounts which use a simple username + password combination.

CREATE LOGIN testlogin WITH password='<APassword>’;

To create logins, you need to connect to the SQL Azure Database’s master database the administrative account(which is created when you create the initial SQL Azure database). There are some restrictions on names you can use when creating logins, these are listed here: Login Name Restrictions.

Note that passwords are subject to certain complexity requirements.

Create a User

Users are created within the context of a single database and are linked to logins. You must connect to the database you want to create the user in, before running the following T-SQL:

CREATE USER testuser FROM LOGIN testlogin;

Managing Permissions

A user identity alone doesn’t grant any access or permissions, you’ll need to assign some permissions manually, once the user has ben successfully created. We do this as we do with standard SQL Server, by assigning database roles, e.g the following grants read/write access to the named user account for the database in context.

EXEC sp_addrolemember 'db_datareader', 'testuser';

EXEC sp_addrolemember 'db_datawriter', 'testuser';

..and conversely, to revoke a permission:

EXEC sp_droprolemember 'db_datareader', 'testuser';

EXEC sp_droprolemember 'db_datawriter', 'testuser';

Remove a User or Login

To remove a user or a login requires the execution of a T-SQL drop statement. To remove an unwanted login, use the following syntax on the master database:

drop user testuser;
drop login testlogin;

Note that if you are using SSMS to administer, the GUI gives you the usual options to remove/delete users via the Object Explorer, as you would with standard SQL Server.

Examples from Windows Azure’s Database Management Console

The easiest way to access SQL Azure is from the Azure Portal. Authenticate using your Microsoft Account and then locate the SQL Database tab:

You want the Server (top, right option) and click on the server name.

From here, click on the dashboard option, and make note of points #1-#4, as illustrated above. You can launch the management console by clicking on the link located at point #3.

Once it loads (can take a while) you need to enter the administrative login details (#2) and the correct password. If you can’t remember the password, you can reset it from the portal (#4), but bear in mind anything using the current password will presumably break, since the login and password are used in conjunction for access via ODBC/SSMS etc.

Once you’ve authenticated successfully, click the “Select a Database” option on the top left hand side, and pick “master” (to administer logins) or select a database to manage users/permissions.

You’ll usually get an error, but this is OK, you just want to be in the master database context (see top left).

Click on ‘New Query’ and you can then execute T-SQL to create/drop logins.

Create Login

Drop Login

Create user (not in master database)

Examples from SQL Management Studio (SSMS)

Fire up SQL Management Studio (2008 R2, 2012, 2014) and with the Database Engine connect window, use the following:

Connect to SQL Azure

Note that you’ll need access via port 1433. Don’t forget to authorise your origin IP address too! Otherwise you’ll get this sort of error message:

You can grant access by IP address via the Azure portal, go to the server (as above) but click on the “Configure” option. You can grant your current IP address easily enough. Don’t forget to click on the Save option before leaving!

Managing SQL Azure

Once you have successfully authenticated/connected, you can manage both the SQL Azure master database and any custom databases fairly easily. Note that you still need to execute T-SQL, but the SSMS option can generate the T-SQL for you:

You can also walk the Object Explorer to get to the Security branch, and manipulate the objects contained within. Reminder: there’s no GUI support as there is with standard SQL Server.

Summary

Hopefully this is all the info you need to successfully manage logins and users in SQL Azure.

Please bear in mind that Azure can change rapidly, these screenshots may not be valid at any point in the future. this article was published in November 2013, and the instructions were valid at this time.

Login and User Management in SQL Azure