Azure Snack - Grant Access to your Azure SQL DB

Case

You want to give an user permission to connect to your database in Azure through SQL Server Management Studio. How do you do this?

Solution

In SQL Server Management Studio (SSMS) with an on-premise database you can do a lot with a GUI, like creating an user and giving this user the right permissions. In this case we use SQL Server Authentication. With a database in Azure you have to write code in Transact-SQL for this, instead of using GUI. Of course you can also use T-SQL in combination with an on-premise database, but for the convenience I use a GUI. Unfortunately, this GUI is not supported in an Azure database. Just like 'IntelliSense' for example.

When you connect to your database server and you want to create a new login, you will see two different windows: a GUI within an on-premise database and a new query window in an Azure database. We will continue with query's, which needs to be run in a particular order.

SSMS - SQL Server on-premise versus Azure SQL Server

1) Create new login
First we need to create a new login to grant access to the database server. Therefore you have to be administrator on the database server. Click on the Security folder and then on Logins to create a 'New Login...'.

Then we edit the generated query to this:

CREATE LOGIN [RobertSmith] 
WITH PASSWORD = 'Abcdefg123!' 
GO

Note:
The password must include letters, numbers, symbols and have a minimum length.

2) Create user 
When we have created the login, we can link this login to a specific database (user). Click on the Security folder in the specific database and then on Users to create a 'New User...'. The engine also generates a code at this point.

We change the generated query to:

USE [sensory]
GO

CREATE USER [RobertSmith]
FOR LOGIN [RobertSmith]
WITH DEFAULT_SCHEMA = [dbo]
GO

3) Permissions
Once the user has been created, you can further specify the authorization . For example, read access to one of more schemas. This can be done by the following query:

USE [sensory]
GO

GRANT select ON SCHEMA :: [analyse] TO [RobertSmith] 
GO

Now the user can login with SQL Authentication with the created credentials. Important is to select the right database, otherwise SSMS will automatically make connection to the master database. In this case we gave permissions to a specific database, so the attempt will fail.

SSMS - Select the database before connecting to server

Conclusion
This isn't very difficult to understand for anybody with some experience within SSMS, but this is not a daily action and in that scenario there is a chance that you do not have the query's ready. Anyway, for now you have to do it with T-SQL in Azure, but perhaps in the future also with an GUI.