The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.6k418575824
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:

×22

question asked: 23 Jun '14, 13:35

question was seen: 708 times

last updated: 23 Jun '14, 13:35