5

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]
2
  • Yes, I cannot see it from the drop-down in SSMS... however I had not tried to I had not tried executing a query against it... it seems I can, and from more tables than I was expecting to have rights to... – Chris Jan 18 '17 at 20:04
  • Ok, after spending a little more time with it I think the table rights are ok. So my only question is why wouldn't I see the databases (which I can access via query) through the SSMS tree-view? srutzky: is this normal because I haven't granted a server role? If that's the case than I would understand it, just not sure if I still have something that needs to be addressed... – Chris Jan 19 '17 at 12:58
8

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.

0

I also had this problem. My solution was to go to Security/Logins: SQLMAD / Securables, find “View any database” and GRANT the value, which generated this script. Once applied, the problem was fixed.

use [master]  
GO  
GRANT VIEW ANY DATABASE TO [sqlmad]  
GO  
-- Revert back for testing use  
use [master]  
GO  
REVOKE VIEW ANY DATABASE TO [sqlmad] AS [usb_sa]  
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.