Azure SQL Server Database - Deny View Any Database Error

Question

Azure SQL Server Database - Deny View Any Database Error

Context:
I had two azure sql server databases on an azure sql server. I created a user and login and need to deny the user from seeing one of the databases. I tried to use the following tsql via ssms:
REVOKE VIEW ANY DATABASE TO [public]
DENY VIEW ANY DATABASE TO [public]

Got this error message when I tried either:
Msg 40521, Level 16, State 1, Line 18
Securable class 'server' not supported in the server scope in this version of SQL Server.

I'm the Azure Active Directory Admin on the server.

Please advise how I can restrict the user access from seeing all other databases on the server including with the master.
Thanks

Answer 1

Alberto Morillo 26,126 MVP

In Azure SQL Database and SQL Server, users can be authenticated at the database level without a server-level login. For example, in the context of the Team1 database:

CREATE USER User1 WITH PASSWORD='<complex-password-here>';

Then user must specify the desired database when connecting and the sys.databases catalog view return only the current database. They cannot see other databases.

You can create the user on the databases he is suppossed to have access. You don't have to create the user at master database level, you don't have to create a server login for that user.

Cherise D Woods 1 Reputation point

2022-02-10T21:46:34.3+00:00

Authenticating with SQL Server User: I need the user to be able to login to sql via ssms. They can't do that with just a user/password. They would need a login, correct?
Alberto Morillo 26,126 Reputation points MVP

2022-02-10T21:59:57.62+00:00

Azure SQL Database (and SQL Server also) allows you to create what is called contained users that you can create on the database, and is a login that only exists at the database level but not on the master database. You connect to the specific database where the user is supposed to have access and you create the user as:

CREATE USER User1 WITH PASSWORD='<complex-password-here>';

EXEC sp_addrolemember N'db_owner', N'User1'
GO

Instead of creating the user as shown below on the master database of the Azure SQL logical server.

CREATE USER User1 FOR LOGIN User1

On Azure SQL Database is recommended to create contained users instead of creating logins on the master for later create the user on the user databases based on the login.
Cherise D Woods 1 Reputation point

2022-02-10T22:11:42.883+00:00

Alberto,
When I create User1 I create the user on the desired database as specified in your comment but when I attempt to login via ssms using the newly created user I get the following error:
TITLE: Connect to Server

Cannot connect to abbottazuresqlserver.database.windows.net.

ADDITIONAL INFORMATION:

Login failed for user 'User1'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

BUTTONS:

OK
Cherise D Woods 1 Reputation point

2022-02-10T22:15:03.16+00:00

Does the user already have to be authenticated at the server level in order for your suggestion to work?

Currently I'm trying to get my user to login via ssms using the user name and password I provide with SQL Server Authentication.

The only way that I've been able to get the user into the server is via a login.
Alberto Morillo 26,126 Reputation points MVP

2022-02-10T22:27:00.07+00:00

You need to provide the user and password, then make a click on Options

And specify the database.
GeethaThatipatri-MSFT 17,131 Reputation points Microsoft Employee

2022-02-14T15:47:39.57+00:00

Hi, @Alberto Morillo Thanks for your continued contribution.

@Cherise D Woods Thank you for visiting Microsoft QA forums! Please let us know if you find the above reply useful. if yes please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community

Regards
Geetha

Azure SQL Server Database - Deny View Any Database Error

1 answer

Activity