Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

In SQL Anywhere Network Server Version 16.0.0.1915, Sybase Central won't display the views for a user id that otherwise has permissions to use the views:

GRANT CONNECT TO ADHOC IDENTIFIED BY "SQL"; -- no other permissions other than SELECT granted later
GRANT MEMBERSHIP IN GROUP DBA TO ADHOC;
GRANT GROUP TO ADHOC;
CREATE VIEW alert AS SELECT * FROM rroad_alert;
GRANT SELECT ON alert TO ADHOC;

Here's what Sybase Central says...

The set of views could not be read from the database.
Permission denied: you do not have permission to execute the procedure 
"sa_materialized_view_can_be_immediate"
[Sybase][ODBC Driver][SQL Anywhere]Permission denied: you do not have 
permission to execute the procedure "sa_materialized_view_can_be_immediate"
SQLCODE: -121
SQLSTATE: 42000
SQL Statement: SELECT T.table_id, T.creator, T.object_id, T.table_name, 
U.user_name, T.table_type, J."status", D.dbspace_id, D.dbspace_name, 
V.mv_use_in_optimization, V.mv_refresh_type, IF T.table_type = 2 THEN 
COALESCE( IF V.mv_refresh_type = 1 THEN '0' ENDIF, IF J."status" = 4 
THEN '1' ENDIF, IF V.mv_last_refreshed_at IS NOT NULL THEN '2' ENDIF, 
IF U.user_name = CURRENT USER OR dbo.sp_has_role( 'ALTER ANY MATERIALIZED VIEW' ) 
= 1 OR dbo.sp_has_role( 'ALTER ANY OBJECT' ) = 1 THEN (SELECT FIRST 
SQLStateVal FROM dbo.sa_materialized_view_can_be_immediate( T.table_name, 
U.user_name) ORDER BY 1) ELSE '-2' ENDIF ) ENDIF, V.mv_last_refreshed_at, 
V.mv_known_stale_at, T."encrypted", T.pct_free, T.count, R.remarks FROM 
SYS.SYSTAB T JOIN SYS.SYSUSER U ON U.user_id = T.creator JOIN SYS.SYSOBJECT 
J ON J.object_id = T.object_id JOIN SYS.SYSVIEW V ON V.view_object_id = 
T.object_id LEFT OUTER JOIN SYS.SYSDBSPACE D ON D.dbspace_id = T.dbspace_id 
AND T.table_type = 2 LEFT OUTER JOIN SYS.SYSREMARK R ON R.object_id = 
T.object_id WHERE U.user_id NOT IN (SELECT creator FROM dbx_filter_list) 
ORDER BY T.table_name, U.user_name

Here is the workaround...

GRANT EXECUTE ON sa_materialized_view_can_be_immediate TO ADHOC;

asked 23 Jun '14, 13:35

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28

question asked: 23 Jun '14, 13:35

question was seen: 2,429 times

last updated: 23 Jun '14, 13:35