3

I wonder if there's any way to allow a user to only view the specific databases they have access to. For example. Let's say we have a alwaysOn with one local only database and 10 databases that is added in the AO. The user need read rights to one of the 10 databases added in AO. But when giving user rights to that database and he connects to the AO he can view every database but only access the one he got rights to of course. And if the AO is active where the local database is located he will also see that one.

We have tried a few things but nothing successful and from what we've been reading it doesn't seem to be a way unless the user is granted dbowner rights.

Anyone got any ideas?

2
  • Would a contained database do this? I can't determine from the documentation but it is implied. Jun 13, 2017 at 7:48
  • Could you please add some of the "few things" you have tried so far? It would help us determine how to answer your question. It would also save us from posting answers you have already tried. Thank you.
    – John K. N.
    Jun 13, 2017 at 8:52

3 Answers 3

2

Starting with SQL Server 2012 you can use contained databases as described here: SQL Server 2012 Contained Database Feature

There is no need to create logins, it's a database that authenticates you. As consequence, the user sees nothing but his databases only

2
  • How does this work with Availability groups? Doesn't say anything about that in the instructions.
    – Jonathan
    Jun 13, 2017 at 10:59
  • I tried with AO and works fine. I believe that this is our best option. Is there any downsides to this that someone can think of? Performance for example?
    – Jonathan
    Jun 13, 2017 at 11:20
7

By default a login can see all the databases on a server.

You can DENY VIEW ANY DATABASE TO [your login or serverrole];, after which a login can only see the databases where he has access.

To verify what databases a login can see, try this when you're connected as an administrative user:

--- This changes the security context to the login "dummy"
EXECUTE AS LOGIN='dummy';

--- Lists the databases visible to that user:
SELECT * FROM sys.databases;

--- Important: changes the security context back again:
REVERT;

The VIEW ANY DATABASE permission is assigned to the server-level principal (the login, i.e. not the user, which is the database-level principal), and therefore, you'll need to apply it to each availability group replica. I would recommend that you apply (deny) this permission on a server role rather than individual users to keep it manageable.

More detailed reading on the subject:

https://www.mssqltips.com/sqlservertip/3399/verify-the-databases-a-sql-server-login-can-see--and-why/

1
  • 1
    Maybe worth noting that if you have DENY VIEW ANY DATABASE for a user they can only see their default db in SSMS object explorer, which is a bit of a pain.
    – Rory
    Mar 25, 2022 at 11:23
-3

Use DCL command to operate on specific database.

Grant select on db_name.table_name to user identified by password

Then commit();

So user will only read the database object.

1
  • 4
    Wont that give the user access to a specified table on a database only?
    – Jonathan
    Jun 13, 2017 at 8:47

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.