Question 103390 in Azure Question posted in
The official documentation to get you started can be found here.

How to hide databases in Azure SQL?

I know the question was asked, but non of the suggestions helped.
I have databases from multiple users on my Azure SQL server.

There is a login ‘USER1‘, and a user created from that login, which is owner of ‘DATABASE1‘.

There is a login ‘USER2‘, and a user created from that login, which is owner of ‘DATABASE2‘

When USER1 connects to the Server, it enumerates/sees the Database DATABASE2. It can’t open it, but sees it.

The following commands executed by the sysadmin on the master, or the user databases, don’t work:

DENY VIEW ANY DATABASE TO [PUBLIC]

or

DENY VIEW ANY DATABASE TO [USER1]

The error is:

'Securable class 'server' not supported in the server scope in this version of SQL Server.'
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The only possible way so far, with Azure SQL, seems to be the usage of the so called Contained User. A user has to be created at the Database level, without being linked to a login. Any Login in SQL is at the SQL server level and will be able to see the databases attached on that server. (Limitation in the Azure SQL)

    Creating the user by running the following commands on the Desired Database:

    CREATE USER <USERNAME> WITH PASSWORD= 'ExtremelySecretPAss123!';

ALTER ROLE db_datareader ADD MEMBER <USERNAME>; 
ALTER ROLE db_datawriter ADD MEMBER <USERNAME>;

    Now to connect to the database (not the SQL server), the following should happen: Enter the SQL server, username and password in SQL Management Studio SQL login

    Press the "Options" button and under the Connection Properties, enter the name of the database the user needs to login to.

    enter image description here


  2. 0

    You can follow below steps to restrict users in Azure SQL DB.

    When USER1 connects to the Server, it enumerates/sees the Database DATABASE2. It can’t open it, but sees it.

    USER1 has been granted the VIEW ANY DATABASE permission. If USER1 has been granted the VIEW ANY DATABASE permission on the Azure SQL server, they will be able to see all databases on the server, even if they don’t have permission to access them.

    Check the VIEW ANY DATABASE on Azure SQL Database using below script.

    SELECT  *  FROM fn_my_permissions(NULL, 'SERVER') WHERE permission_name =  'VIEW ANY DATABASE';

    enter image description here

    This is possible for a single user login as you can only assign
    ownership of a database to a single user and deny VIEW ANY
    DATABASE     as below, see the Ms
    Doc
    created by Hale

    1. Right click the users logon under the users database if they exist and remove.

    (Note: Do not remove the user from under the main security login)

    2.Run the below query.

    USE <customersdatabase> 
    ALTER AUTHORIZATION ON DATABASE::<customerdatabase> to <customerlogin>
    
    USE MASTER
    
    DENY VIEW ANY DATABASE TO <customerlogin>

    You can also disable public access on Azure SQL DB from portal as below.

    Go to your Azure SQL Server > Networking > Public Access.

    enter image description here

    I can be able to see only one database not other database once i logged in to the user.

    enter image description here

    Reference:
    How to hide Microsoft SQL databases that a user does not have access to created by – Daniel

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search