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: enter image description here

The error message appears when I click on the + at the left of DB_Name: enter image description here

• Learning_DBAdmin: First, you need to add the login as a user in the database(one you want to access) and then grant read or write and execute permission based on your business requirement. guru99.com/sql-server-create-user.html
• Dominique: @Learning_DBAdmin: as you can see in the attached screenshots, the user already exists, but apparently he does not have access to the entire database. It looks like connecting to "dbo" tables might be allowed, but how can he be allowed to some tables if the entire database is not even accessible?
• John K. N.: Is the account ANALYSE a Windows Authenticated Account that has been added as a SQL Server Login (without password) or a Native SQL Server Login (with separate password)?
• Dominique: @JohnK.N.: it is a native SQL Server Login with separate password, it is not a Windows authenticated account.
• J.D.: What kind of permissions do you want your ANALYSE User to have in the database?... read data, write data, execute procedures and functions, modify the objects in the database, modify the database itself, etc?

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:

Login Properties - User Mapping (Image referenced from SQLShack)

• Dominique: I'm sorry, but this seems not to work. Hereby a summary of the error message: Create failed for User 'ANALYSE'. For help, click: https://...FailedOperationExceptionText&EvtID=Create+User&LinkId=20476. User, group, or role 'ANALYSE' already exists in the current database. (Microsoft SQL Server, Error: 15023). For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-15023-database-engine-error
• J.D.: @Dominique That's because you manually added the User to the database before creating a mapped Login to it, effectively creating what's known as an orphaned User. You should delete the User from the Database Users folder first, then follow my instructions above to correct that. The above automatically creates the User for you.