-1

We're having an instance by AWS RDS with SQL Server 2016.

Lots of databases being in this instance. We need to create a login called test_user with public server role and db_datareader database role, and this user can only see one database only - called Test

We have been provided with a login non-grantor (setupadmin, processadmin and create any databases)

How should we have a way - I know no way but just for lucks if any?

Much appriciated.

1 Answer 1

0

In SSMS 16 you can right click on Databases and select Filter, this will allow you to write a filter to show\hide specific databases.

As far as restricting this to another user, you can deny the permission VIEW ANY DATABASE to the login, however this will restrict them from seeing all databases, including ones they have access to. It is not possible to grant visibility to only databases they can access.

EDIT: It is possible per @Tibor Karaszi's comment below that the database owner can see a database that they own even if they do not have VIEW ANY DATABASE, and not see any other databases, however this would involve setting the database owner to the required account which may or may not fit your security policy - the owner of a database cannot be denied any permissions inside that database.

3
  • 1
    ... but they can see the database(s) that they own, even when you deny VIEW ANY DATABASE. That ans contained authentication is the only way (I know) you can practically hide databases from somebody. Commented Jul 3, 2019 at 6:24
  • @TiborKaraszi thanks for the pointer, I hadn't considered that. I've added it to the answer as it's possible however I would have thought that setting the database owner to accounts in use by clients\applications would be a big no no in almost every security configuration?
    – Libertheme
    Commented Jul 3, 2019 at 7:02
  • Absolutely, I agree. In many cases, having that person being the owner is not an option because that would either get too high privilege, or because you have some other standard for who is the owner of each database (sa or a login per db dedicated to be the owner of that db). But in some cases it can apply... :-) Commented Jul 3, 2019 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.