Skip to main content

"Login failed for User" in SQL Server - Reason II

I hope you already read my previous post for the "Login failed for User" - Reason 1. And you have very idea on that how a user was foiled due to default database assigned to associated User. After default database changed User was success to login in SQL Server instance. If you have not read that post, then please go ahead with it. In this post I am going to demonstrate another reason for the "Login failed for user".

What is the story?
I have created one login for "DBA". After successfully created with it, I have assigned sysroles to the user. I am very happy here because this created user is for me and I am going to use this user only which have full access to the SQL Server instance and all the objects of that instance.

After creating a user I have tried to connect the SQL Server instance. But SQL Server has given me one surprise gift. This gift was " Login failed for user 'dba'.

What i did?
I have checked the login properties and check the assigned roles, checked its status as enable or not. Also checked granted permission, but everything is OK.

All are OK. Then why i was not able to connect the instance?
Finally, I have checked SQL Server instance properties and reviewed Security section. And here I found my solution.

See the screenshot, you can see the SQL Server Authentication was "Windows authentication". That's why it was not allowed to connect instance through SQL users.

After changing SQL Authentication to SQL Server and Windows Authentication, I was able to connect a SQL Server instance. Please share your comments if you suffered from this issue.


  1. Thanks paresh you save my time. Very good blog.



Post a Comment