14

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?

erop
  • 1,510
  • 1
  • 14
  • 27
  • did you fing any solution? – Akmal Salikhov Dec 15 '16 at 09:23
  • 1
    My solution is the code snippet above. But if you meant desired "smarter way"... no, unfortunately not. – erop Dec 15 '16 at 18:36
  • Seems like you figured it out. My 2 cents is I would go on to say that the account you want as the ADMIN for SQL Server you should setup the account as an ADMIN on the actual machine and login with that account (you can always lower the permissions later, however I never do) because I am assuming you want the SQL services ran with this particular account and install SQL Server. Then add the Security Logins after you have setup SQL Server, as you did above. – Moojjoo Jun 21 '17 at 14:56
  • alas, this didn't work for me, i get "Windows NT user or group 'MicrosoftAccount\...' not found. Check the name again." – Ruslan Jan 16 '20 at 15:31

2 Answers2

2

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.

Jan Christoffersen
  • 103
  • 7
0

For those who tried the solution above but are still encountering this issue: change your connectionstring within your appsettings by replacing the Server=localhost value with the server name you see when logging onto MSSQL Management Studio.

Mitchell Appelman
  • 1
  • 1