Local SQL Server Logins

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.

USE Master

GO

CREATE DATABASE McMatrix

GO

USE McMatrix

GO

CREATE LOGIN Neo WITH PASSWORD = 'redpill'

GO

CREATE USER Neo FOR LOGIN Neo

GO

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: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter