As a test of theory (because I want to brush up on my SQL Server Security understanding) I want to set up a SQL Server Login that can only see a few fields on one table in one database.
I have not assigned any roles to this login except the default public. I don't want the hypothetical user to have any server role (ie not sysadmin, dbcreator)… and I don't want to use any standard role for the database (db_datareader etc)…
The login has a default database, and the login is mapped to the database (default schema dbo). Now in the user securables I have granted access to one table and used the column permissions to specify the columns but I'm really not getting this far when testing the login.
When I log into the SSMS with SQL Server authentication and the new login, I don't see any databases except the master/tempdb…
Should I be seeing my default/mapped database? I don't see any other questions asked quite like this so I am guessing I am missing something completely obvious.
In order to log into SSMS with a sql login, do you HAVE TO HAVE an assigned server role to see non system databases? I can see the databases when I add sysadmin but then I am right back to over granting.
CREATE LOGIN [Chris_Test2] WITH PASSWORD=N'¯\Ï<ÞZcÅÎ5¿ûY!ËDåÜ''¨Äs»¾Þja7',
DEFAULT_DATABASE=[Finance_Receivables_TEST],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
USE [Finance_Receivables_TEST]
GO
/****** Object: User [Chris_Test2] Script Date: 01/18/2017 14:40:21 ******/
GO
CREATE USER [Chris_Test2] FOR LOGIN [Chris_Test2] WITH DEFAULT_SCHEMA=[dbo]
Best Answer
It sounds like you have removed the View Any Database permission from the Public role. If the Public role doesn't have the View Any Database permission an account has to be the owner of the DB to see it's row in sys.databases.