Hide SQL DB's Users do Not have Access to

Question

Hide SQL DB's Users do Not have Access to

rr-4098 2,331

I know I can set the permission to Deny "View all DB's" but I need users who are mapped to their DB's to be able to see them in SSMS. Thoughts?

Anonymous

2024-04-23T07:18:23.5933333+00:00

Hi @rr-4098,

Do you have further question on this, could we offer more support?

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. :)

Thanks for your understanding.
Mark Malakanov 5 Reputation points

2025-02-11T21:55:05.48+00:00

Microsoft should just fix their sys.databases view to show any database a user has any access to, i.e. has permission to select from a table.
Simple like that.
VIEW ANY DATABASES permission should be granted only to certain server roles like sysadmin, and not to public.
I do not understand why MS SQL team cannot make it, knowing the issue exists many years.

8 answers

Your answer

Anonymous

2024-04-23T07:18:23.5933333+00:00

Hi @rr-4098,

Do you have further question on this, could we offer more support?

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. :)

Thanks for your understanding.
Mark Malakanov 5 Reputation points

2025-02-11T21:55:05.48+00:00

Microsoft should just fix their sys.databases view to show any database a user has any access to, i.e. has permission to select from a table.
Simple like that.
VIEW ANY DATABASES permission should be granted only to certain server roles like sysadmin, and not to public.
I do not understand why MS SQL team cannot make it, knowing the issue exists many years.

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 2

rr-4098 2,331

If a user is now a owner of a DB, doesn't this mean they have full rights to the DB? If this is the case this will be a problem.

Anonymous

2024-04-15T05:52:15.31+00:00

Hi @rr-4098,

Thanks for your information.

Database owners and system administrators are the only users who can grant object creation permissions to other users. The database owner has full privileges to do anything inside that database and must explicitly grant permissions to other users with the grant command.

Perhaps you don't want this 'user' which changed to 'owner' have the full rights to the DB? Here is an article about contained database. It has limitations to use, and you will connect to only the contained database. You should not be able to see any of the other databases or instance features. Hope this can help you well.

Best regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications
Anonymous

2024-04-16T07:30:25.1233333+00:00

Hi,

https://www.codeproject.com/Tips/1103206/SQL-Server-Grant-Permission-to-Particular-Table, this article shows how to share only one table of SQL Server database with a user.
rr-4098 2,331 Reputation points

2024-04-16T14:01:10.01+00:00

LucyChen-MSFT thank you for the great feedback. I am reviewing the article on contained DB's. Can you convert a regular DB into a contained DB?
Anonymous

2024-04-17T10:00:43.2+00:00

Hi @rr-4098,

I don't know why my picture cannot upload, this is the step 6, and you can enter the database name manually if you cannot browse.
rr-4098 2,331 Reputation points

2024-04-17T14:31:38.8566667+00:00

Great feedback... I am going to test this out and let you know how i make out.
Anonymous

2024-04-19T03:39:18.67+00:00

Hi @rr-4098,

Is this issue solved now? If you have any questions, please feel free to share with us.

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thanks for your understanding!
rr-4098 2,331 Reputation points

2024-04-19T15:57:04.6666667+00:00

The contained DB seems to be working so far. On issue I noticed is that I cannot alter the permission on the migrated SQL account.
Anonymous

2024-04-22T02:42:00.8266667+00:00

Hi @rr-4098,

Thanks for your feedback. Glad to hear your contained DB working well so far.

In fact, I think the later issue is beyond the scope of this thread. However, I found this article of how to alter the permission of the users of the contained database account in SQL Server. Hope this can help you well.

https://www.c-sharpcorner.com/article/contained-database-no-more-need-for-server-level-logins/.

In addition, I don't want to nag you, but maybe my answer was helpful, could you think of Accepting it as the Answer? Your action would be helpful to other users who encounter the same issue and read this thread. Thanks for your understanding.

Best regards,

Lucy Chen

Answer 3

rr-4098 2,331

Thank you for the feedback. I did the same steps as you. The user can only access the DB they have access to and get errors on the other DB's. The problem is we do not want to user the "see" the other DB's. Please note, when I deny "view all db's" they can still query the DB, but need to see it since they are not sure of all the table names.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Anonymous

2024-04-12T08:32:40.6066667+00:00

In addition, these are all my databases.
Anonymous

2024-04-12T09:09:15.7266667+00:00
If you want to change the owner, just use:

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [newowner];
rr-4098 2,331 Reputation points

2024-04-12T13:44:21.4333333+00:00

LucyChen the missing part was the DB owner. Once I changed it to the user, everything worked perfectly....

Answer 4

Hi @rr-4098,

Thanks for your information.

Here is my database for test.

User's image

I checked the database properties, it has my domain\user account as the owner, because I created it.

User's image

Then I created a Test account with login to allow me to connect into SQL Server (Using SQL Authentication and the Test login)

User's image

During I created the account, I Use the server level login (Test) to set its Mappings to include the Test database.

User's image

Here you can see what my Test sees at the server level (everything) while it throws an error if I try to access any database.

User's image

However, when Test tries to access a database that it is mapped to, it can see the objects inside that database that have been granted to it under database user's Securable permissions.

User's image

Please feel free to share the issue more detailed here If you have any other questions, thank you.

Best regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Anonymous

2024-04-11T05:50:02.4033333+00:00

Another test:

Create a new database:

It throws an error when I try to access the database.

It owns no schemas, it has db_datareader permissions.

There are limited options to hiding databases. In addition, each database can only have one owner, you can’t assign multiple owners to the same database.

Regards,

Lucy Chen

Answer 5

Guoxiong 8,146

Make sure the user has the right to view the databases:

USE [master];  
GO  
GRANT VIEW ANY DATABASE TO [TestUser];
GO

rr-4098 2,331 Reputation points

2024-04-10T19:28:57.0866667+00:00

But if I want to hide the DB's the user doesn't have access to, why would I grant them the View any DB permission?
Guoxiong 8,146 Reputation points

2024-04-11T13:10:55.71+00:00

It is not possible to hide the user databases that the user doesn't have access to. The databases are listed but you can deny the user to access to them.