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

I want a user to see all the tables and views in the each database on the server. I also want them to see the view definitions. By that I want to also be able to see how the views are created. That is, the query inside the view not the results

What server level access should I grant them to be able to do that? We have several databases and a huge amount of views and tables in each database. I don't want to do that manually one by one

Thanks

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://learn.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.

StackExchange Database Administrators Q#260755, answer score: 13