Security per database in SQL Azure Pool

Question

I am new to SQL Azure and need to plan on moving some of our SQL Server Databases.

We have a SQL Server Azure Pool which can take up to 50 databases.

I need to restrict certain users to specific databases inside the pool.

E.g. User1 can only Access Database Northwind -thus when he connects with SSMS to the Azure Pool, this is the only database he can see (he can't see the other 49)

Is setting up this possible - or is it a case that if you have access to the Instance (the whole pool) - then you will have access to all the DBs inside that pool?

This would mean that we would need to move each database to its own instance based on security? It will also have the implication that whatever SQL username/password is used in the applications web.config, that user in the config will have access to all the databases in the SQL instance (servername)

I am looking at the SQL Azure training on Pluralsight and seeing that there are database level firewalls based on IP - in this case we have multiple devs - each with same IP - but specific devs must only have access to specific databases. Thus one IP rule per database on the instance will not work since username is not evaluated.

Answer 1

You can restrict user per database. I will explain here using sql authentication and you can do the same if you have AD set up.

I have 3 database and 'taiobmdjamshed' is the server admin and have access to all the databases.

I create security1 login with db_owner privilege to security1 database and a user at master database.

CREATE LOGIN security1 
    WITH PASSWORD = 'S<<MM12345!@@!' 
GO

Create a user in master database.

CREATE USER security1
    FOR LOGIN security1
    WITH DEFAULT_SCHEMA = dbo
GO

Make a user in security1 database and make it a member of db_owner role.

CREATE USER security1
    FOR LOGIN security1
    WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'security1'
GO

If connecting to the server using security1 login and try to access security2 database this is what will happen because security1 login does not have a corresponding user or privilege in security2 database.

This link explains more in details.