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]