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

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
rr-4098 2,331 Reputation points

2024-04-12T14:23:02.38+00:00

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.
Was this answer helpful?
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
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
rr-4098 2,331 Reputation points

2024-04-11T18:18:17.6533333+00:00

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.
Was this answer helpful?
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....
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Hide SQL DB's Users do Not have Access to

8 answers

Your answer