How to Interpret Misleading Error Messages related to the GRANT statement

Introduction

SQL Server in general has very intuitive error messages but sometimes they are not clear at all, at least not at first glance. When dealing with permission management, you might come across the one or the other unintuitive on as well.

Misleading Error Example

Let's look at a typical example. Let's assume TestUser1 has been granted SELECT permission on a table, but accidentally without the WITH GRANT OPTION clause:

After confirming that the SELECT access has been granted, TestUser1 is trying to pass the same permission on to TestUser2:

Even though the SELECT statement succeeded, clearly proving the table's existence, this will result in an error with error number 15151 which states that the object 'tst' was not found:

One of the problems of the message is that it omits the schema name on the referenced object. That can lead to ambiguities that send you searching in the wrong place. But that is not even my main concern.

The statement that the object could not be found is just plain wrong. Now, there is actually more to this error message. After the comma it clarifies that the object either does not exist or that we "do not have permission". But many people stop reading after the first section and are left wondering why the object is missing from their database.

Now it gets worse. Assume, the WITH GRANT OPTION clause was used, but not on all columns:

The error message we get when attempting to select from the secret1 column is very detailed and helpful. The error we get on the GRANT statement on the other hand is the same as the error we got before:

That error in this context is even less helpful for enabling quick troubleshooting. It does not give even a little hint that the problem is due to missing permissions on a single column.

Summary

Error messages in SQL Server sometimes do not make sense or are at least not helpful at first glance. When you run into an error like that, make sure you go back and read the message in its entirety. There is often a hint in there telling us what might have caused the issue. In the example above it is given by the four words "do not have permission". Those words tell us that the object might actually be there but we do not have the correct permission to execute the action.