SQL Server – Permissions Needed to See View Definitions via Query

sql serversql-server-2016ssmsview

I have full admin rights on our database and hence can query and see view definitions. I want however query the views with a read-only user in a JDBC Jenkins job magic. The problem: Unlike my admin-user, the read-only user does not see the code/definitions of a view.

This query give me all the view definitions and the meta data I need for all views when I act as Admin:

SELECT name AS VIEW_NAME,
definition,
create_date,
modify_date  
FROM [my_database].[sys].[all_views]
JOIN [my_database].[sys].[sql_modules]
ON [my_database].[sys].[all_views].object_id = [my_database].[sys].[sql_modules].object_id

As a result when executing the query as admin I get entries like:

name        | definition        | create_date | modify_date 
sample_view | SELECT * FROM bla | 01.01.2017 | 02.01.2017

However, not so much when I do it with my read-only user, I get

 name        | definition        | create_date | modify_date 
 sample_view | null              | 01.01.2017  | 02.01.2017

Here you can see my permission configuration for the read only user. Although I have granted him the necessary permission, the view definitions are not visible to the user in a result set.

It is furthermore really strange that after allowing the user to do SELECT and VIEW definition statements and saving the config, a second entry for SELECT and VIEW DEFINITION was added to the config table.

Read_only_user_permission

Best Answer

Simply granting VIEW DEFINITION and SELECT permissions on INFORMATION_SCHEMA and sys schema wont give you rights to see the definition of the view.

Permissions that you added will provide you information about all objects in sys schema and information_schema.

I suppose you want to see the view definition which are created in some other schema, in which case you would have to provide the user withVIEW DEFINITION to that particular schema.

Such as : GRANT VIEW DEFINITION ON SCHEMA::SchemaWhereViewsAreCreated TO [YourUser]

Related Question