CLI Connectivity Error

[David_Billa]

I'd like to know why in the error message it shows 'KSS_EXTERNAL' as it is not mentioned any where in the code? If UVW_ISM_TRANSACTIONS_RV_PIVOT is a view, we may get error message as shown below?

proc sql noprint;
  connect to sqlsvr(dsn=KGG_SC  
                    user="SAS_PU_UK" 
                    pwd="test@123" 
                    readbuff=32000);
 
  create table TRANSACTIONS_RV_PIVOT as
  select * from connection to sqlsvr 
    (                                                      
      select *
        
      from KGG_SC.dbo.UVW_ISM_TRANSACTIONS_RV_PIVOT);
 
  disconnect from sqlsvr;
quit;
 

Log:

26         proc sql noprint;
27           connect to sqlsvr(dsn=KGG_SC
28                             user="SAS_PU_UK"
29                             pwd=XXXXXXXXXXXXX
30                             readbuff=32000);
31         
32           create table TRANSACTIONS_RV_PIVOT as
33           select * from connection to sqlsvr
34             (
35               select *
36         
37               from KGG_SC.dbo.UVW_ISM_TRANSACTIONS_RV_PIVOT);
ERROR: CLI describe error: [SAS/ACCESS][ODBC SQL Server Legacy Driver][SQL Server]The server principal "SAS_PU_UK" is not 
       able to access the database "KSS_EXTERNAL" under the current security context.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
38         
39           disconnect from sqlsvr;
NOTE: Statement not executed due to NOEXEC option.
40         quit;

[ChrisNZ]

You're showing a SQL Server error message mentioning SQL Server architecture components, and this question is for your SQL Server DBAs.