Lets say user1 has access to Execute macros but in one of the macro there were two tables referenced and he doesnt have access to those two tables. Will he still be able to execute the macro and get the desired results or will it error out?
PS: I dont have access to create a macro in my Test environment.
Yes, the database in which that Macro resides should have access to the underlying tables. Without which you cant compile a Macro. And all you need is the right to execute Macro to get the desired results.