SQL SERVER – Case Sensitive Database and Database User – Fix: Error: 15151 – Cannot find the user , because it does not exist or you do not have permission.

Jeff asked me another question!

If you do not know Jeff, you may read the following blog posts. You will get the idea of Jeff’s personality and who Jeff really is.

• SQL SERVER – Installation Log Summary File Location – 2012 – 2008 R2
• SQL SERVER – INNER JOIN Returning More Records than Exists in Table

This time, he sent me a screenshot. He was facing a very strange error. As his screenshot had confidential details, I created my own images which exactly simulate his issue for demonstration’s sake. Here are the partial details of his email. Please note that I have replaced his username and database name to protect his private information.

“Hi Pinal,

I think I am having one of those moments when I am feeling stupid or SQL Server is having fun with me.

Here is the scenario. I have two databases: AdventureWorks and MyAdventureWorks. I have a user called SQLAuthority in both  databases. Now when I try to grand access the user in one database it works fine, but when I try to do the same thing in another database, it gives me error. I am stunned as both the users are same. As a matter of fact, I had just created those users recently, and now I am not able to gain necessary permission.

Here is the screenshot where everything works fine:

Here is the screenshot that shows where I am getting crazy and gives following error:

Msg 15151, Level 16, State 1, Line 2
Cannot find the user ‘sqlauthority’, because it does not exist or you do not have permission.

Help me again. Why is this happening? I do not get it. HELP!”

That’s a very interesting question from him. For a moment, I was speechless as it is difficult for me to solve the problem for him remotely. After a while, I noticed that in his example, the case of the username did not match. In SSMS the username was SQLAuthority and in his T-SQL script it was sqlauthority. I told him to change the case of the T-SQL to match with the case of SSMS, and it right away solved his problem.

I asked him to run the following script which validated that his database was throwing an error because of case sensitivity. Read here how you can identify collation of any database: SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') AdventureWorks;
SELECT DATABASEPROPERTYEX('MyAdventureWorks', 'Collation') MyAdventureWorks;

The above query returns the following results:

Learning: Case sensitivity of a database does not only matter in the data of the table, but also in objects like tablename, columnname, and even username. If you know any other situation like this, please share your knowledge in the comments area.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)