September 26, 2012 at 7:57 am
How can i make sure any login on the server can view only the database that they have access to.
I did remove "view any database" permissions at the server level for the "public" role and provided read access to a login hoping that this login would be able to see only the database that he has read access but it seems like this login could not see any database except master nad tempdb.
how do i do this correctly?
September 26, 2012 at 10:23 am
Tara-1044200 (9/26/2012)
How can i make sure any login on the server can view only the database that they have access to.I did remove "view any database" permissions at the server level for the "public" role and provided read access to a login hoping that this login would be able to see only the database that he has read access but it seems like this login could not see any database except master nad tempdb.
how do i do this correctly?
In short, you can't. They'll see the list of all databases if they have VIEW ANY DATABASE or they'll see only the ones they own (i.e. ones they are dbo of) if they do not.
This recent thread ran it down quite well with a solid detour that it is worth watching out for:
http://www.sqlservercentral.com/Forums/Topic1355727-1526-1.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2023 at 12:53 am
I know this is old, but is there STILL no way to do this?
I am finding it difficult finding any updated info on this.
March 28, 2023 at 10:30 am
I know this is old, but is there STILL no way to do this?
Maybe.
You could try looking at row level security (RLS) on sys.databases but I suspect this will fail as the system databases are in the Resource db so are read only. (ie I suspect you could not create a security policy for sys.databases.)
If you can live with the restrictions of a partially contained database then the user will only be able to see the db they have access to. I quickly did the following on Docker Desktop server:2022-latest (CU2):
USE [master];
GO
CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE DATABASE db3;
GO
EXEC sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
CREATE DATABASE dbP CONTAINMENT = PARTIAL;
GO
USE dbP;
GO
CREATE USER UserP WITH PASSWORD = 'AStrongP4ssword!';
GO
I was then able to select the dbP database in SSMS and login as UserP. The only database I could see was dbP.
I think partially contained dbs have been available since SQL2012.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply