Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

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

Connect To Server

You are then prompted with a nasty error message like the one shown in figure 2.

Figure 2: Nasty Error

Error 1

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

Error 2

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

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

SQL Error Log

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

SQL Server Properties

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, MCSDBrian 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

 

 

Print
Categories: Miscellaneous
Tags:
Rate this article:
No rating

briankmcdonaldbriankmcdonald

Other posts by briankmcdonald

Please login or register to post comments.