User handling in MS-SQL database

Question

I have an MS-SQL installation, controlling different databases. This can be accessed using Microsoft SQL Server Management Studio 18. For doing that, I use Windows authentication.

In there, I have different databases. One of them has the following permissions (while checking "Properties", "Permissions"):

Users or roles:
Name          Type
ANALYSE       User

Permissions for ANALYSE:
Permission    Grantor     Grant    With_Grant    Deny
...
Connect       <empty>     False    False         False
...
Connect       dbo         True     False         False

I have modified the "Grant" column checkbox, but that modification is not taken into account.

I'm doing this because I want "ANALYSE" to be capable of accessing that particular database (I'm working with a connectionstring in a C# Entity Framework related program and I'd like to perform as least as possible source code modifications). When I access that database while having logged in as "ANALYSE" I get the following error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------
The database <database_name> is not accessible. (ObjectExplorer)
------------------------------
BUTTONS:
  OK
------------------------------

Two questions:

• In order to access that database, is it correct trying to check the "connect" feature in the "permissions" page? If not, what else do I need to do?
• When I click a checkbox, how can I make sure that this gets stored instead of not taken into account?

Edit: screenshots for better understanding
As can be seen in the following screenshots, one "Connect" permission is granted, and all tables are indeed called "dbo.<table_name>", nevertheless simply opening the database is not working:

Permissions screenshot:

The error message appears when I click on the + at the left of DB_Name:

Answer 1

In order to access that database, is it correct trying to check the "connect" feature in the "permissions" page? If not, what else do I need to do?

You also need to grant some permission for example SELECT to be able to read data from this table. CONNECT gives you only possibility to connect and that is all.

Of course make sure that User is mapped to some Login.

When I click a checkbox, how can I make sure that this gets stored instead of not taken into account?

In your case connect is already granted and 'dbo' is grantor of this permission and that is ok. If some permission (like connect) is granted you will see it twice in management studio. So don't pay attention to that. If you click on some checkbox and then OK and you don't get any error you can be sure it is saved. You can also use button "Script" on the top of the window to have a script ready to be executed in SSMS query window so you can also use that option.

Answer 2

Based on your answer in the comments, you want to provision your ANALYSE User to have the ability to read and write data to any tables in your database. It's easiest to accomplish this by using the db_datareader and db_datawriter database security roles.

I find it simplest to set these in SSMS by expanding the Security folder (at the Server level), then expanding the Logins folder, right clicking the Login you want to provision permissions for, and clicking on Properties.

Then in the Login Properties window, go to the User Mapping section, and check off the database you want them to have access to. Then in the "Database role membership" section, check off the roles db_datareader and db_datawriter. Finally, click ok to save the changes (which will take effect immediately).

An example Login Properties window below:

(Image referenced from SQLShack)