5

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

7

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.

5

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.

  • Thanks...You both had similar answers and I am going to go that route – masksoverflow Feb 27 at 19:27
0

In principle, you should always work towards creating a role that fits your need in oppose to grant a user all the privileges required. Although this may be counter-intuitive, it will prove very valuable when "the user leaves", another user requires "the same privileges", "recreate the user" etc. An example to illustrate this, user A has worked for many years, is a top-notch person and creates and runs many jobs all "under its own personal user". One day he moves on and gets another job, HR asks you to remove immediately the user, next day jobs 1..n don't run.

You try to pass all the jobs to the new user assuming the role... nightmare #1 the list is extensive and you do not want to live those days.

Instead, you create a role or several roles, with all the privileges required and assign the user those (or a single) role. Once the users leave you drop it, create a new user, grant the same role(s) and away you go. Next day it is just another day in paradise.

Cheers

  • Well @Maly are you my coworker? Cos someone left and I am dealing with the exact same nightmare you are stating here – masksoverflow Mar 5 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.