0

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?

Hong Ooi
  • 56,353
  • 13
  • 134
  • 187
DaiKeung
  • 1,077
  • 1
  • 19
  • 38
  • 1
    The link in your question is for SQL 2008 and not relevant for Azure SQL Database. Not sure of your use case but consider adding users to individual databases and not creating a login at all. Users will then be authenticated at the database level and only see the current database. – Dan Guzman Feb 01 '19 at 02:44
  • If without login, how can people login to database? My aim is to create 2 databases for 2 teams that they are not allowed to see another DB. It is better for them to NOT know existence of another DB. What should I do? Thanks. – DaiKeung Feb 01 '19 at 02:54
  • I elaborated on my comment with an answer. – Dan Guzman Feb 01 '19 at 03:17

1 Answers1

1

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.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71