Additional User Account, Windows Authentication in SQL Server Management Studio

Question

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:

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:

This resulted in the following error:

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?

Solution

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.

Comments

[James Dunn]

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. :)

[Vomit IT - Chunky Mess Style]

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