Search code examples
sql-serverazure-sql-databasessmsincompatibletypeerrorsystem-databases

How to hide other databases from User in Azure SQL Database?

I am using SQL Server Management Studio v17.9.1. I followed guide of this post to use below command but failed with error.

REVOKE VIEW ANY DATABASE FROM PUBLIC

Securable class 'server' not supported in this version of SQL Server.

I tried below code as well with same error:

USE master;
GO
DENY VIEW ANY DATABASE TO TestUser;
GO

What's wrong for this?

Solution

  • In Azure SQL Database (and on-prem contained databases), users can be authenticate at the database level without a server-level login. For example, in the context of the Team1 database:

    CREATE User Team1User WITH PASSWORD='<complex-password-here>';

    And similarly for the other database:

    CREATE User Team2User WITH PASSWORD='<complex-password-here>';

    Users must specify the desired database when connecting and the sys.databases catalog view return only the current database.