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://blog.sqlauthority.com)
17 Comments. Leave new
Gud One sir!!! Now a days i read SQLAuthority articles everyday in place of News Paper… :)
Thanks Chandra – love your comment and we are motivated.
I found the same problem in previous week and i had wasted my half day to find error. My mind was out of coverage at that time, eventually my college just tried by changing the case and it solved.
I was smiled at my self and looking at the screen
This article becomes very helpful for other.
Thanks Mr. Pinal
The afternoon is off to a great start for me! I was half-way through the post, looking at the pair of screenshots and almost immediately thought – “hmmm…AdventureWorks seems to be a case-sensitive database, and the script uses a different case for the user name.” As a I scrolled down, that’s exactly what it was!
Anyway, case sensitive collations may end up being a lot of trouble for someone who has never worked with them. At work, the databses that I work with are case-insensitive; whereas the AdventureWorks sample database is case-sensitive which sometimes takes me on a wild-goose chase.
I sometimes wonder whether all databases should be made case sensitive or not? To think of it, doing so would make lives of code reviewers so much easier (one would be forced to use Intellisense when developing scripts, rather than remember the exact case of an object name).
Nakul,
Absolutely excellent comment. I think in future we should put up a poll that about it. I think those who are confused due to case sensitive nature of database, will be not confused any more if everything is case sensitive.
However, there are some clear needs of the case sensitive database as well and collation have special place in the database.
Hi pinal sir,
I’m too like chandra, I used to start a day with your post, i put bookmark to your blog in my toolbar’s both laptop and office desk so there is no chance to miss your post even a single day…!!!!
Ganesh,
Love your dedication my friend. Your support make me go day by day.
Recently I have read in one of your blogpost that you are now MS employee.
MS must be shamed after seeing this post from you that by seeing this simple error you not able to judge that this is only and only the case of case sensitivity or in technical term database collation.
Did someone took your interview before hiring you in MS?
I know you are the author of this blog and you will not let other know about it. But my intention is not to make aware about you, but to make you aware about your knowledge.
Thanks for your concern. The goal of the post is to share experience.
Chris,
May be people can forget small,small things some time even they are experts in resolving big ones,this post is for ‘jeff’ who is new to this field, ‘pinal’ explained the actual problem in the way of what normal person thinks in this situation like ‘Jeff’,
If ” Pinal ” says like this “Oh!! this is very very easy problem why this guy jeff not having this much of knowledge who hired him..???”
What we think if we see this words in this post..?????
So the aim is to explain the problem in such a way of easy understanding for every one.!!!!
I’m sorry if did anything wrong here…!!!
Thank you Chris.
Chris,
I think you have not got the point of this blog post. It is not about Pinal’s knowledge but rather a good example of true mentor and story teller.
How easily he tells us a story, which we can all related to ourself and my co-workers. After reading this post, I will never ever forget this issue.
He is great great educator. A small request to you – please stay positive to life.
Pinal – keep up a good work.
Really great sir,
Thanks Pinal, I got some valuable information today…..
Can someone tell me when to consider collation (case sensitivity) of SQL Server? I have not come across a scenario where case sensitive SQL Server is preferred over case insensitive. Please Help.
Good to know. However I experience some strange behaviours with various SQL Server installations. At least I find it inconsistant. My connection string is
server=ServerName;database=SS1
If I try database ‘ss1′ connection would fail which is fine as the DB is case sensitive. When I run
SELECT name AS “Database” FROM master..sysdatabases
SS1 appears in upper case.
However, when I run any query and that I want to specify the database prefixing the table name, it requires the SS1 to be ss1.
Example:
SELECT VBELN FROM SS1.VBAK;// returns [Invalid object name ‘SS1.VBAK’]
SELECT VBELN FROM ss1.VBAK;// runs fine
On another install case insensitive this one, I have to remove the database reference completely in order to have the query running.
What are the reasons for these differences? Thanks for any help.
How about when you add a domain login to MSSQL 2012 and you use Windows (AD) authentication? AD usernames are not case sensitive. The AD account login name has a leading uppercase letter when created in AD (via script with capitalized first and last name records). When the AD user account is selected as a login to MSSQL using windows authentication, the uppercase letter is used. The SQL username then becomes case sensitive and will not be granted access until the user logs in with the leading uppercase letter of their AD username proper. They will then have access to the MSSQL server. It just seems that there would be some sort of standardization between these MS products. I have just been modifying the username directly in visual studio. It seems to work but I have no experience with MSSQL in general and this may be a common known issue but I thought it was interesting, or I am doing it very wrong.
Dear Pinal Dave
Thank you very much for your article.
It Really helped me.
Really thank you.