Cannot authenticate with Local windows user into SQL Server Express / Authentication choice disabled

Question

Hello.

My details here below. (I use SQL Server 2019 Express on Windows 10)

SQL Server Management Studio 15.0.18338.0
SQL Server Management Objects (SMO) 16.100.41011.9
Microsoft Analysis Services Client Tools 15.0.19205.0
Microsoft Data Access Components (MDAC) 10.0.17763.1
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.17763

What i am trying to do is trying to authenticate with a newly created local windows user account

Instead i can only authenticate , as per default, using the Domain Windows user account, This is my Company pc and it is the main account with my name.

If i click connect using "Connect to server" windows of SQL Server, i got

Server type: Database Engine

Server name (my server name)

Authentication: Windows Authentication

User Name: (greyed out, appears my personal domain account like xxxxx\myname

password: (greyed out)

But the abovee mentioned new local windows user is recognized by sql server.

I did this

connected with the main windows account

security >logins> new logins > general >search> and i selected the local user . clicked ok

i also created a credentials with same name and added to it as you see from the screenshot. nothing to do. the password remains greyed out as i cant use that credentials.

and as "Connection Properties" it appears always and always the main Domain windows user account.

is there a chance to authenticate using that local user windows account?
if i created a New Login using SQL server authentication, i dont have such this kind of issue.

Thanks for help in advance.

Answer 1

When you log in with Windows authentication, you must first have been authenticated by Windows. That is, you can only log in with your current Windows user. You cannot log on to SQL Server with a Windows login and password.

If you want to test permissions for a different login than your own, you can do this:

EXECUTE AS LOGIN = 'DOMAIN\user'
go
--- Run statements here
go
REVERT

You will now impersonate the given login until you execute the REVERT statement. Note that you must execute the REVERT statement in the same database as you executed EXECUTE AS LOGIN.

Answer 2

Hello ErlandSommarskog.
I appreciate your prompt-reply solution, I admit i was unaware of that but it is quite logical indeed that i have to be logged in with the same windows user.
Thanks again!
Paolo