What grants should I give a user if I want the user to see all tables and views (view definitions) in every database in the server

GRANT VIEW DEFINITION TO [user]; will allow the user to see the definitions of structures in the database, including tables, views, stored procedures, etc.

You'll need to do that for every database on the instance. There is no server-wide equivalent, out of the box, that limits permissions to just the database level.

If you need to limit it to just a single schema, then you'd do:

GRANT VIEW DEFINITION ON schema::[name_of_schema] TO [user];

If you only want them to be able to see the definitions of tables and views (and not stored procs etc), then you'll need to do a single grant for each object you want them to see the definition for. You could generate the GRANT statements for just tables and views at the database level with the following query:

SELECT
      ObjectType = o.type_desc
    , ObjectName = o.name
    , GrantStatement = N'GRANT VIEW DEFINITION ON ' + QUOTENAME(o.name) + N' TO [user];'
FROM sys.objects o
WHERE o.type = N'U'
    OR o.type = N'V'
ORDER BY o.type
    , o.name;

See the Microsoft Docs for more info at https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-ver15

If you're comfortable assigning a lot more permissions to the login, at the server level, you could do:

GRANT VIEW ANY DEFINITION TO [name_of_login];

That will allow them to see the definition of any object in any database they have access to, and also will allow them to see definitions of things like endpoints, at the server level.


You cannot assign permissions at the server level to see only the table and view definitions, so the following set of server level grants aren't exactly what you want, but it is the minimum server level permissions to get what you want. The VIEW ANY DEFINITION will allow the user to see definitions on all database objects, not just the tables and views. So the login will be able to see definitions of stored procedures, functions, etc.

GRANT CONNECT ANY DATABASE TO [user]
GRANT CONNECT SQL TO [user]
GRANT VIEW ANY DATABASE TO [user]
GRANT VIEW ANY DEFINITION TO [user]

If you want the user to be able to see only table and view definitions, you will need to grant VIEW DEFINITION on each view and table. This could be automated to ease the pain, but if the additional permissions to view the definition of any object is OK, then the grants listed above on the server will meet your need.