hiding irrelevant databases in SQL Server

I'm going to put my actual test results first.

Problem

I have a SQL Server instance, which has hundreds of databases. Navigating the database tree in SSMS is a pain and I were wondering if there was a by-a-to-limit the list of databases that I See in SSMS?

Solution

SQL Server consolidation is becoming more popular these days to reduce costs and therefore more and more databases be Bei Ng put on one instance. It is very common to host multiple databases on a consolidated instance from multiple applications and departments and SOM Etimes application owners want to hide their databases to other users of the instance. They does not want to make their the database visible to others. This tip would give you a understanding on what databases can be hidden.

Setup

Suppose there is databases A and B from the different applications and they is hosted on the same SQL Server Instanc E. The users of database A is not allowed to see database B and vice versa. Here we'll create the different logins user_a and user_b and give them appropriate rights to their own databases.

CREATE database agocreate Database bgocreate login user_a with password= ' [e-mail protected] ' gocreate LOGIN user_b with Pass word= ' [email protected] ' Gouse agocreate USER user_a for login user_a; Goexec sp_addrolemember ' db_owner ', ' user_a ' gouse bgocreate user user_b for login user_bgoexec sp_addrolemember ' db_owner ', ' user_b '

Note:-do not make changes in PRODUCTION without PROPER TESTINGS on Lower-life CYCLE envirnomentshiding all user databases For all logins

Suppose want to hide all databases for all logins. Generally we hide our databases for security purposes. We can run the below statements to hide all databases for all logins. The databases is and only is visible to the sysadmin logins or owners of the database.

Use Mastergodeny VIEW any DATABASE to Publicgo

Once you run the above statement, you'll not be able to see any databases in SQL Server Management Studio unless A sysadmin or your login is the owner of a database (s).

Here's can see in the below screen shot, I had connected using logins user_a and User_b and none of the user databases is showing after running the Deny View access to public.

SysAdmins and database owners can see databases

To allow the logins to see their databases, I'll make both logins the owners for their respective databases. User_a would be the owner of database A and User_b would be the owner of database B. Run the below statements to change the data Base owners.

Use Agosp_changedbowner [User_a]gouse bgosp_changedbowner [User_b]

We can check the database owners by running sp_helpdb. As you can see in the below screenshot, the database owners has been changed for both databases.

Now we can connect to the SQL Server instance again using both logins and see the changes compared to before. Here we can see the only one database was visible for both logins. Database A is the visible to user_a and database B are visible to user_b. This is because both logins was now the database owners of these databases.

Does making a user a db_owner work

Now we'll create a new login user_c and assign db_owner access to both databases and check whether these databases is V Isible to this new login.

CREATE LOGIN user_c with password= ' [e-mail protected] ' gouse agocreate user user_c for LOGIN user_c; Goexec sp_addrolemember ' db_owner ', ' user_c ' gouse bgocreate user user_c for login user_cgoexec sp_addrolemember ' db_owner ', ' user_c '

As we can see below, neither of these databases is visible for login user_c. So from this we can see the and have the the database owner to be able to see the databases in SQL Server Management St Udio if the DENY VIEW any DATABASE was enabled for public.

Steps to hide databases for a specific login

Suppose we don ' t want to does this across the board, and only does this for a specific login. We can run the below statement instead of the DENY VIEW any DATABASE to public. After running the below statement, this login won ' t being able to see databases except for any database that this login is th E database owner, but all other logins can see the database as a long as you didn't also deny view to public.

Use mastergogrant VIEW any DATABASE to public; --Turn this back on if it is Offgodeny VIEW any DATABASE to user_a; GO

Steps to view all databases

By default, the VIEW any DATABASE permission are granted to the public role. Therefore, by default, every user this connects to a instance of SQL Server can see all databases in the instance. To grant the VIEW any DATABASE permission to a specific login or to all logins run the following query:

--to Grant the VIEW any DATABASE permission to a specific login. Use mastergogrant VIEW all DATABASE to [login_name]; Go--to Grant the VIEW any DATABASE permission to public. Use mastergogrant VIEW any DATABASE to public; Go

Note If you use the "Deny view to public" This overrides the setting for a individual login, so if you DENY view to PU BLIC and GRANT VIEW to a specific login this login is still not being able to see the databases.

If you are using the DENY VIEW to public and you want a login to still is able to see all databases without making that login  A sysadmin can do the following.  Make the login a user with the master database and make the user a db_owner of the master database.  This isn't a very good option from a security perspective, but this does work. This-a-to-a login can see all databases without has to be a sysadmin.

Conclusion

As can see from the above, there is limited options to hiding databases. Once You hide all databases the only logins so can see the databases is the logins that is the owners of the database  Or if the login is a sysadmin. Also, each database can only has one owner, so you can ' t assign multiple owners to the same database.

Next Steps

• Follow this process to the hide your databases in SQL Server Management Studio.
• Read More SSMS Tips

Reference material: How to hide SQL Server user databases in SQL Server Management Studio

hiding irrelevant databases in SQL Server