Min SQL user privilege to query sys.objects and sys.partitions in Azure SQL-CodePudding

I would like to have SQL user which can perform following SQL, but not able to read contents of other schemas.

What is best way to implement?

SELECT (Schema_name(A.schema_id)   '.'   A.NAME ) AS TableName,
       Sum(B.rows) AS RecordCount
FROM sys.objects A
         INNER JOIN sys.partitions B
                 ON A.object_id = B.object_id
WHERE A.type = 'U'
GROUP BY A.schema_id, A.NAME

CodePudding user response：

For columns, there is a row for every permission that is different from the corresponding object-level permission. If the column permission is the same as the corresponding object permission, there is no row for it and the permission applied is that of the object.

Note: Column-level permissions override object-level permissions on the same entity.

Specifies a permission that can be granted on a schema. Here.

In rare circumstances, combining ALTER and REFERENCE rights may allow the grantee to access data or perform prohibited operations. For instance, a user with ALTER access on a table and REFERENCE permission on a function can build and execute a calculated column over a function. The user must also have SELECT permission on the calculated column in this situation.