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
26.9k438609883
accept rate: 21%

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:

×23

question asked: 23 Jun '14, 13:35

question was seen: 880 times

last updated: 23 Jun '14, 13:35