SAS reading MS SQL View Table

[Handihusen]

hi, i need some help regarding SAS reading MS SQL View Table:

i'm using SAS 9.4 for Windows,

i already set up ODBC Connection

i want to read View Table: v_CRM_DATA_DAILY from MS SQL server

this is the captured form MS SQL Server:

i'm using this sas code but failed to get v_CRM_DATA_DAILY

LIBNAME X ODBC datasrc=CRMDATA user=xxxx pwd=xxxxxx;

proc sql;
connect using x;
create table CRMDAILY as select * from connection to X
( select * from v_CRM_DATA_DAILY );
quit; 

sas log:

ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'v_ISEE_STAFF_DATA', database 'CXXXX_iSee', schema 'dbo'.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.17 seconds
cpu time 0.00 seconds

Thanks in advance for the response and help.

[andreas_lds]

Contact the mssql admin, afaik granting read access to a view is not sufficient, read access to the used tables is necessary, too.

[Handihusen]

If access has been granted, then the SAS code used is as above that I use?

[LinusH]

You could, but writing explicit pass through is not necessary in this case. Simpler coding:

LIBNAME X ODBC datasrc=CRMDATA user=xxxx pwd=xxxxxx;

proc sql;
create table CRMDAILY as select * 
   from X.v_CRM_DATA_DAILY
;
quit; 

[Handihusen]

still cannot get the CRM_DATA_OUTBOX, somehow SAS select another table v_ISEE_STAFF_DATA, as shown in the logs.

[Handihusen]

sorry typo: 'CRM_DATA_OUTBOX', i mean 'v_CRM_DATA_DAILY'

[SASKiwi]

If you are still getting SELECT permission errors, then only your organisation's database administrators can fix that for you.  

[Handihusen]

noted & thanks,