3

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]
  • 1
    Script out the user and the login and add the script to your post. – Tara Kizer Jan 18 '17 at 19:16
  • What do you mean by "I don't see any databases except the master/tempdb"? Do you mean in the database drop-down in SSMS? Can you execute: USE [Finance_Receivables_TEST]; when logged in as Chris_Test2 without any error? – Solomon Rutzky Jan 18 '17 at 20:00
  • 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
  • you should see the database in SSMS because any login created is assigned the public server role and this role has the VIEW ANY DATABASE securable. Unless you have somehow removed this role from your login. – hot2use Jun 14 '17 at 13:51
4

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.

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.