Question :
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?
Answer :
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
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/
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.