I have Windows 10 Pro machine with SQL Server 2016 Dev installed. There are two local user accounts on the machine: [WORK\admin] and [WORK\erop]. [WORK\erop] account was created at first without connecting to Microsoft. But later I connect it to my MS account. [WORK\erop] account was added to sysadmin role when installing SQL Srv.
Now I try to connect to SQL Srv instance with SSMS as [WORK\erop] but receive:
Login failed for user 'MicrosoftAccount\<my_MS_account>'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
and
Error: 18456, Severity: 14, State: 5.
which means "Invalid userid".
As I guess SSMS is launched under MS account and since MS account has no login on SQL Srv instance it refuses connection.
I executed
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 OK. But I wonder are there any smarter way to map MS_account to local user that has a login on SQL Srv instance?
Right click on the database server and go to properties. Choose the security option and check "SQL Server and Windows authentication mode". Enable TCP/IP connection in SQL Configuration Manager. Restart your SQL server.
Select Windows Authentication from the Authentication drop-down, then click Connect to log in to SQL Server. Right-click on the server name (ie COMPUTERNAME\ACCTIVATE) and choose Properties . Click Security in the list on the left. Select SQL Server and Windows Authentication mode on the right and click OK.
I wouldn't call any solution smarter than yours, depends on your situation and what you want. But alternatively you could have reverted to a local user, assuming you don't care about the OneDrive and such functions.
Start > Avatar button > Change account settings > Your email and accounts > Sign in with local account instead.
But adding the login again is the smartest and easiest solution.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With