Creating Users in Azure SQL

I have been working quite a lot in Azure recently and have done some work with SQL Azure PaaS databases.

I am not an SQL DBA by any stretch of the imagination, so some tasks have taken a bit of Googling to conquer!

There are some key differences between a full SQL install and Azure SQL.  One of the main differences with Azure SQL Azure PaaS databases is that you need to do so much more through Transact-SQL.  My limited experience of SQL has all been based through GUI prompts, so this initially proved a problem for me.

One of the tasks which I have had to complete a fair amount recently is to create database users.  In the Azure Portal you can create administrator accounts for the whole server, but this doesn't match the requirements of the business in most occasions.

I came across these blog posts on the Microsoft website

https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

So to create a read-only user for a single database, the Transact-SQL you need are

USE [master]
GO
CREATE LOGIN readonlyuser WITH password='supersecurepassword1';
USE [specificDB]
GO
CREATE USER readonlyuser FROM LOGIN readonlyuser;
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';

Now....when a user tries to connect with this account, they will most likely get this error.

The reason this error occurs is because you haven't added the user to the master database.  The trouble is that if you add the user to the master database, they will get Guest access and be able to see all of the databases on the server.  They will not be able to access the contents, but they will be able see their presence.

If you are not fussed by this, you can run the following Transact-SQL to solve your issue.

USE [master]
GO
CREATE USER readonlyuser FROM LOGIN readonlyuser;

Alternatively, you can inform the user to type in the specific database in the options of SQL Studio

Now, all of you SQL on-prem DBAs are probably yelling at the screen "use the default_database" T-SQL.  Well, guess what?  It isn't supported in Azure (yes, this is mentioned in the first link on the page).

I hope this is useful!

Sam