How many of you have been in this situation? Your objective is to create a local SQL server login that has limited access to certain objects in your database. So you create your new login using something similar to the below script.
CREATE DATABASE McMatrix
CREATE LOGIN Neo WITH PASSWORD = 'redpill'
CREATE USER Neo FOR LOGIN Neo
Everything works as you suspected that it would. So, as a test, you open up a new instance of SQL Server Management Studio and log in as Neo with assuming he took the “red pill” as shown in figure 1.
Figure 1: Login as Neo
You are then prompted with a nasty error message like the one shown in figure 2.
Figure 2: Nasty Error
You click OK and try logging in again, only to be shown a yet another message like the one shown in figure 3.
Figure 3: Login Failed for Neo
At first, I thought that maybe I missed something and forgot to add the user. So I verified in SSMS that the user was added to the server and was enabled. I had indeed added the user as shown in figure 4 below.
Figure 4: Neo Login Properties
After pulling the last few hairs out of the top of my head and doing another set up Marine Corps pushups, I thought to myself…”Look at the SQL Server Error log you goof…Da”. That is what I did next and figure 5 shows what the root of the problem was.
Figure 5: SQL Server Error Log Detail
Since it was 1:12 in the morning and it was already a long day on the road, I thought that I would fix this real quick and hit the rack. So, what did I do to fix this you say? I am glad you asked. I did another set of pushups, RIGHT clicked the server name in SSMS and selected Properties. I navigated to the Security page. The setting was “Windows Authentication mode”, so I switched it to “SQL Server and Windows Authenticated mode” as displayed in figure 6.
Figure 6: Server Properties – Change to Mixed Mode
After you change this value, you will be given a message that states, your changes will not take effect until SQL Server is restarted. Restart your MSSQLSERVER service and try logging in again.
Thanks for reading,
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consultants
Email: firstname.lastname@example.org | Blog: BI Developer Network
Convert with DTS xChange | Develop with BI xPress | Process with TaskFactory | Document with BI Documenter