This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 46%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER4%3C/text%3E%3C/svg%3E)
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?
Additional SQL Server features and topics not covered by specific categories
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(134.4, 28.999999999999996%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
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.
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.
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.
In addition, these are all my databases.
If you want to change the owner, just use:
ALTER AUTHORIZATION ON DATABASE::[dbname] TO [newowner];
LucyChen the missing part was the DB owner. Once I changed it to the user, everything worked perfectly....
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.
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
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.
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?
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.
Great feedback... I am going to test this out and let you know how i make out.
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!
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.
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
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