Have you ever encountered a situation where you were unable to grant access to a user in one database, even though the user existed in another database with the same name? This can be a frustrating experience, but fear not, as we will explore the reason behind this issue and how to resolve it.

Recently, I received an email from a reader named Jeff who was facing a similar problem. He had two databases, AdventureWorks and MyAdventureWorks, with a user named SQLAuthority in both databases. However, when he tried to grant access to the user in one database, it worked fine, but in the other database, he received an error message:

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

Jeff was puzzled as both users had the same name and he had recently created them. After analyzing the issue, I noticed that the case of the username in his T-SQL script did not match the case in SQL Server Management Studio (SSMS). In SSMS, the username was “SQLAuthority,” while in his script, it was “sqlauthority.”

I advised Jeff to change the case of the T-SQL script to match the case in SSMS, and voila, the problem was solved. This incident highlighted an important concept in SQL Server – case sensitivity.

Case sensitivity in SQL Server not only applies to the data in tables but also to objects such as table names, column names, and even usernames. In Jeff’s case, the case sensitivity of the database caused the error.

If you encounter a similar situation, it is essential to ensure that the case of your T-SQL script matches the case of the objects in SSMS. To validate the case sensitivity of your database, you can use the following script:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') AS AdventureWorksCollation;
SELECT DATABASEPROPERTYEX('MyAdventureWorks', 'Collation') AS MyAdventureWorksCollation;

The above query will return the collation of each database, which can help you identify if case sensitivity is the cause of the issue.

Remember, when working with SQL Server, paying attention to case sensitivity can save you from unnecessary headaches. If you have encountered any other situations related to case sensitivity, feel free to share your knowledge in the comments section below.