SQL Srv 2016: Login failed for user "MicrosoftAccount \ ..."

Question

I have a Windows 10 Pro machine with SQL Server 2016 Dev installed. There are two local accounts on the computer: [WORK \ admin] and [WORK \ erop]. First, an account [WORK \ erop] was created without connecting to Microsoft. But later I will connect it to my MS account. The [WORK \ erop] account was added to the sysadmin role when installing SQL Srv.

Now I'm trying to connect to an SQL Srv instance with SSMS as [WORK \ erop], but get:

Login failed for user 'MicrosoftAccount\<my_MS_account>'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>] Login failed for user 'MicrosoftAccount\<my_MS_account>'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>] as well as Error: 18456, Severity: 14, State: 5. , which means "Invalid user ID."

I think SSMS runs under the MS account, and since the MS account does not have a login in the SQL Srv instance, it refuses to connect.

I performed

 CREATE LOGIN [MicrosoftAccount\<MS_account>] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [MicrosoftAccount\MS_account] GO 

and everything is in order. But I'm wondering if there is a more reasonable way to map MS_account to a local user who has an account on an SQL Srv instance?

Answer 1

I would not call the solution smarter than yours, it depends on your situation and what you want. But, alternatively, you could return to the local user if you do not care about OneDrive and such features.

Start> Avatar Button> Change Account Settings> Your Email Address and Accounts> Log in to your local account.

But adding a login is again the smartest and easiest solution.