I asked the OP if the code is intended to be executed on the whole server, i.e. in all databases or in some of them and still no anwer, so I suppose he needs the code to be executed in ALL databases:
managing an application with a large number of SQL Server 2014 databases I give the solution to all the server.
First, your login should access all the databases, and you accomplish this by granting him CONNECT ANY DATABASE PERMISSION
Then your login should access object definitions without accessing the data (here is a trade off: or you give the read access on all your tables, or you give the access on all definitions, this means all modules included, but no data access in this case, so I prefer the last one), this is given by VIEW ANY DEFINITION to login
These two permissions:
CONNECT ANY DATABASE PERMISSIONVIEW ANY DEFINITION
granted to login at the server level is the minimum set of permissions to find out all sizes of all indexes on the server, just change you code to use sys.allocation_units instead of sys.dm_db_partition_stats
But if you insists on your code using sys.dm_db_partition_stats it requires VIEW DATABASE STATE in every database, and you grant it by granting only 1 time VIEW SERVER STATE permission to your login
............................................ In reply to this one:
sys.indexes contains no rows if the user does not have permissions on any tables...
My user l has no SELECT permission in a database, but it "sees" all the tables(and other objects as well) in a database, and has no problem with sys.indexes while he cannot read any data; all he has in that database is VIEW DEFINITION permission
