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.