We created a Role on an Azure SQL database that we want to set up with limited access to only a subset of our tables.
Doing this was easy enough and it works perfectly, apart from one little issue. When a user has logged in with this Role, ideally we don't want them to be able to "discover" what they don't have access to.
A simple example:
SELECT * FROM [dbo].[Foo]
This produces the error "Msg 208 Invalid object name 'dbo.foo'"
However, when the user runs
SELECT * FROM [dbo].[ATableTheyDoNotHaveAccessTo]
They get the error "Msg 229 The SELECT permission was denied on the object " (etc....)
The problem with that is that if one is secretive enough about the database schema, it's not acceptable for the user to get "Msg 229". They may not be able to see the data, but getting that message will prove to them that they hit on a potential source of data, and that may invite them to try harder to get to those data which is something we'd like to avoid.
I know it's a bit paranoid but if there is anything we could do to make it output the same thing in both cases it would help. I had a look at sp_altermessage but I don't believe that that changes the message ID (208 v 229) so somebody who knows SQL Server or Azure SQL well enough will still get the hint from that Message ID.
No, you can't change these errors, they are fundamental to the product. Object permission errors and invalid object errors are distinctly different errors. They are also handle quite differently. A permission error is non-aborting, meaning the batch will continue after the error is met, however, an invalid object error is aborting, and any statements later in the batch will not be executed (ignoring TRY...CATCHes). See this example db<>fiddle.
You cannot change the values of system error messages as well, especially in an environment like an Azure SQL Database. Doing so would not be a good idea even if you could, and I would very strongly advice against it, if it were somehow possible.
There is, in truth, no work around for this. I also don't really see it as a concern. If a USER did manage to guess the name of an object they don't have access to then "congrats" to them; they still can't do anything with that object. If they managed to compromise another LOGIN, then they would easily be able to see what objects they have access to with that LOGIN by querying the system objects.
There are only a couple of ways I could see it being advantageous to them. The first would be if you are using object names that provide important context, such as a client's or 3rd party's name; if that's the case perhaps you should rethink your object names, or consider not using different tables, with the same definition, for different parties and implement row level security.
The other would be if you have processes that run dynamic statements with elevated permissions (as chaining is lost with dynamic SQL), and those statements are liable to injection. Then the root problem is not the person using the LOGIN that got access to the objects, but that you have vulnerable code.