1

I've tried this:

AS
    DECLARE @sql nvarchar(15000)
BEGIN
    IF @X=1
    BEGIN
       SELECT * FROM X_TABLE_NAME
       return
    END
    ELSE IF @X=2
    BEGIN
       SET @sql = N'SELECT '+@COLUMNS + ' FROM X_TABLE_NAME '
       EXEC (@sql)    
END
return

But I get this error:

sybase_query(): Sybase: Server message: SELECT permission denied on object X_TABLE_NAME

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Karen
  • 11
  • 2
  • the obvious (?) issue appears to be that your login does not have permissions to run a `SELECT` against a table; should your login have access? are you using the wrong login? which Sybase RDBMS product (ASE? IQ? SQLAnywhere? Advantage?)? What's the actual query in `@sql`? do you get the same error message when you log into 'sybase' and run the query (that's in `@sql`)? – markp-fuso Nov 05 '21 at 22:23
  • I modified my question. I have ASE 12.5.3. In Sybase I can execute the procedure, but in web I get the error. It's a problem with the dynamic sql because in web case @x=1 run well – Karen Nov 05 '21 at 23:38
  • you're saying the code works as expected when you run it directly in Sybase (eg, via `isql`) for both @x=1 and @x=2? but when run through the 'web' (??) @x=1 works but @x=2 generates the error? could you modify the select as follows: `SELECT suser_id(),user_id(),db_name()` (for both @x=1 and @x=2), and post back here with the output from the query (for both @x=1 and @x=2); it sounds like the @x=2/exec() scope change is running under a different user/login or possibly in a different database? – markp-fuso Nov 06 '21 at 00:14
  • if you log into ASE via `isql` and `use` your database, then run `sp_help` ... how many tables and/or views have the name `X_TABLE_NAME`? and what are the owner names of each? – markp-fuso Nov 06 '21 at 00:20

0 Answers0