2

I am transitioning my work laptop to a coworker (who we'll call Mary Smith) who is taking my place on my team as I move on to other things. One of the tools we use is Microsoft SQL Server Management Studio. We have always used windows Authentication to access our local databases. It has always worked for me, like so:

enter image description here

I had Mary log into the laptop with her active directory credentials, and it proceeded to setup her account on the laptop. Later I logged back in with my credentials, and gave Mary admin privileges on the laptop.

Later, with Mary logged in again, I was going to walk her through the database schemas, and had her attempt to connect with Windows Authentication: enter image description here

This resulted in the following error:

enter image description here

I am thinking this either has something to do with some settings in user accounts on the laptop, or the database (or MS SQL Server) settings themselves need to be changed... or a combination of the two. How can I solve this error so Mary can successfully connect to her local MS SQL Server with Windows Authentication?

5

You need to add the user with his/her required roles and permissions:

  • In the SQL Server Management Studio, open Object Explorer
  • Click Server_instance_name > Security > Logins
  • Right-click Logins and select New Login
  • On the General page, in the Login name field, type the name of a Windows user
  • Select Windows authentication
  • Depending on the role and permissions that you want to assign to this user, complete one of the following tasks:
    • On the Server Roles page, assign the sysadmin role to the new login ID
    • If you do not want to assign the sysadmin role to the user, grant selected permissions.

For more information see Microsoft's Create a Login.

  • That worked!!! Thanks so much @harrymc. I logged in with my account, followed your instructions, then Mary logged back in and was able to authenticate with the local SQL Server. :) – James Dunn Dec 20 '18 at 23:00
  • Harry, that's a cool wizard hat you have on there. Good answer; clear and concise!! You would't happen to be an IT guy would you.... LOL – Pimp Juice IT Dec 21 '18 at 2:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.