Azure SQL Database – How to Create a Second Login and Deny Access

azure-sql-databasesql server

I have an Azure database and I have created another one on the same server. After that, I have created a user and a corresponding login (see the script below) with the intention to use it for the second database only. After running the script I have googled (below), the second user can access both databases. How to deny the second user access to the first database? There are DENY and REVOKE, that deny specific rights, but it is not obvious to me which one should be used.

Bonus question: was it possible to create a user that can access the second database only?

IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE ('[' + name + ']' = '[Elmah5]'))
    CREATE DATABASE Elmah5;
GO

IF NOT EXISTS (SELECT 1 FROM sys.sql_logins  WHERE name = 'ElmahAdmin')
    CREATE LOGIN ElmahAdmin WITH PASSWORD = ''
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals  WHERE name = 'ElmahAdmin')
    CREATE USER ElmahAdmin FOR LOGIN ElmahAdmin WITH DEFAULT_SCHEMA=[dbo]
GO

enter image description here

HOW IT SHOULD HAVE BEEN DONE

  1. Connect using your primary account.

  2. Switch your current database to MASTER (circled in, just type in MASTER and hit ENTER) and run

    IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE ('[' + name + ']' = '[SecondDB]'))
    CREATE DATABASE SecondDB;
GO  
IF NOT EXISTS (SELECT 1 FROM sys.sql_logins  WHERE name = 'SecondDBAdmin')
    CREATE LOGIN SecondDBAdmin WITH PASSWORD = ''
GO

enter image description here

  1. Still under your first login, switch to SecondDB and run 

    CREATE USER SecondDBAdmin FOR LOGIN SecondDBAdmin WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'SecondDBAdmin'
GO

enter image description here

Best Answer

If you don't want the user to have access to the database at all make sure your user does not have system administrator privileges which would allow them access to every database. Baring that, on the database you don't want the 2nd user to have access to, run the following:

USE [1stDatabase]
DROP USER [2ndUser]
GO

To create an exclusive user for the second database:

USE MASTER
CREATE LOGIN [login_name here] WITH PASSWORD = '[password here]'
GO

USE [2ndDatabase]
CREATE USER [login name here] FOR LOGIN [login name here]
GO
Related Question