Only show databases to user in which he has db_owner rights

Question

Hello,

Is there still no way for a user to only see their own database in SSMS.

I also tried edit the MS SQL role called "Public". Set the property "View any database" to "Deny". Then no databases are showing.

Thanks in Advance.

Answer 1

If you deny or revoke VIEW ANY DATABASE, users will only be able to see the system databases and databases they own. But if they have access to a database, they will not see it, even if they are db_owner in the database. They can still use "USE db", and once they have done that, the database will display.

Yes, this is a long-standing request that Microsoft has yet to come up with a solution to. (It is not entirely easy, because to check if a user has access to a database, you need to look in that database. May seem a like a slim thing, but on a server with 1000 databases all in auto-close, this is not fun at all.)

Answer 2

Hi，sourav dutta

I also tried edit the MS SQL role called "Public". Set the property "View any database" to "Deny". Then no databases are showing.

After doing that ，Run this in a role of sysadmin：

Use [WH_R]//The database
GO
EXEC dbo.sp_changedbowner N'user'//The username