How do I grant view permissions so a SQL Login can see only one database?

Question

I want to create a database (SAMPLE) with an system administration account and then access it using a SQL Login, similar to a service account.

That SQL Login should not be able to see the other databases in my server instance. Online, there are many articles that offer one of three variations on a solution.

Option 1. do the following a) DENY ANY DATABASE to the user and then b) grant permissions on the database in the User Mappings. This does not work.

Option 2. do the following a) DENY ANY DATABASE to the user and then b) ALTER AUTHENTICATION on the database. This works, but changes the ownership of the database to the user SQL Login. I don't want this. The system admin account used to create the database needs to remain its owner. The SQL Login should be able to view it.

Option 3. involves using "contained" databases, which is not an option for me.

Conceptually, I am thinking there should be a fourth option that looks something like:

Option 4. do the following a) DENY ANY DATABASE to the user and then b) GRANT VIEW ON DATABASE::SAMPLE TO SAMPLE_USER. This would deny blanket visibility while restoring itemized visibility, but I have not figured it out.

Testing Results:

This is what the SAMPLE_USER sees when I implement Option 1. It does not expose the SAMPLE database to the SAMPLE_USER Login, even though it is mapped. This does not work.

This is what SAMPLE_USER sees when I implement Option 2. This works, however, it changes the database ownership which I cannot allow.

I am going to add a follow on with details of how this is configured. But, the question is, how do I grant view permissions to ONE database that does not change the ownership of that database?

Answer 1

Bottom line: We cannot limit what the SQL Login sees without changing is role in an unacceptable way, but we can control what it can access.

https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/

Per the article,

Conclusion
…there are limited options to hiding databases. Once you hide all databases the only logins that can see the databases are the logins that are the owners of the database or if the login is a sysadmin. Also, each database can only have one owner, so you can’t assign multiple owners to the same database.

Solution:

Start with a database (SAMPLE), a database level user (SAMPLE_USER) that is linked to a server level login (SAMPLE_LOGIN).

Use the server level login (SAMPLE_LOGIN) to set its Mappings to include the SAMPLE database.

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

In contrast, when SAMPLE_LOGIN tries to access a database that it is mapped to (as SAMPLE_USER is a database level user account that links to the server level SAMPLE_LOGIN), it can see the objects inside that database that have been granted to it under database user’s Securable permissions.

Answer 2

Background:

Here is my source database.

When we look at the database properties, it has my domain\user account as the owner, because I created it.

I then created a SAMPLE_USER account with login to allow me to connect into SQL server (using SQL Authentication and the SAMPLE_USER login).

However, when I connect using that account, the database is not visible.

The SAMPLE_USER database user account is linked to the server level SQL Login of the same name.

It owns no schemas. This is a viewer, not an owner…

It has db_datareader permissions…

It has connect permissions to the database…

Looking at the SQL Login level

It is in the public role.

It is mapped to the SAMPLE database. Note:

Finally, the SQL Login has connect permissions on my local machine.

When I connect to the server, I use the SAMPLE_USER login…

So, this represents Option 1.

This is the script for Option 2.

As mentioned previously, this works, but…

It changes the ownership. This is not allowable. I don’t want SAMPLE_USER owning it, but simply connecting and viewing it.

Answer 3

You can't really do that.

Please see:
https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/