SQL Server: The server principal is not able to access the database under the current security context / windows authentication for the login

Question

I saw a couple of other questions like this one but no one could solve my issue.

I have a SQL 2016 Express and a database mydatabase. I created a login using Windows Authentication and I used my AD user domain\myuser. In mydatabase -> security -> Users I created a user called myuser (User type = Windows user, Login name = domain\myuser). Default Schema is dbo and in the Securables menu I added mytable and gave the following rights: Alter, Delete, Insert, Select and Update.

If I open SQL Management Studio as domain\myuser, connect to that SQL server, run use mydatabase and select * from mytable, everything works fine.

Now, using a vbscript, from a command prompt running as domain\myuser, it will fail. The relevant part of the myscript.vbs script is this:

strConn = "PROVIDER=SQLOLEDB;Server=myserver\myinstance;Integrated Security=SSPI;"
Set cxn = Wscript.CreateObject("ADODB.Connection")
cxn.Open strConn
cxn.CommandTimeout = 0 
strSqlQuery = "use mydatabase"
Set rs=cxn.Execute(strSqlQuery)

This will fail with:

myscript.vbs(6, 1) Microsoft OLE DB Provider for SQL Server:
The server principal "domain\myuser" is not able to access the
database "mydatabase" under the current security context.

I tried to add the database=mydatabase in the SQL connection string but it didn´t work either.

Looking at some of the answers in other questions, some people asked to run the following command in the SQL Management Studio:

sp_change_users_login  @Action='Report';

It was not clear if that was with a db_owner or the affected user, so I ran with both. With the db_owner the result was the columns UserName and UserSID empty, whereas with domain\myuser it said:

Msg 15247, Level 16, State 1, Procedure sp_change_users_login, Line 51 [Batch Start Line 10]
User does not have permission to perform this action.

Is my Integrated Security part in my script connection string wrong? How do I solve this?

Answer 1

For my case when I go into depth, I have found that users have super rights on SQL Server & on MSDB are working fine.

Reason in my case: CONNECT permissions are denied from PUBLIC role.

2 possible solutions: 1. Provide connect permissions to all user sepratly 2. Provide CONNECT permission to PUBLIC role