Is there an option to prevent grant select on unknown user from stopping

[cellurl]

1                                                          The SAS System                              10:30 Wednesday, May 13, 2020

NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.
NOTE: All profile changes will be lost at the end of the session.
NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA. 
NOTE: SAS (r) Proprietary Software 9.4 (TS1M6) 
      Licensed to FEDEX CIA SRVR -TRANSATIONAL LICENSE SUPPLEMENT 53, Site 70024850.
NOTE: This session is executing on the SunOS 5.10 (SUN 64) platform.



NOTE: Analytical products:
      
      SAS/STAT 15.1
      SAS/ETS 15.1
      SAS/OR 15.1
      SAS/IML 15.1
      SAS/QC 15.1

NOTE: Additional host information:

SUN SunOS SUN 64 5.10 Generic_150400-63 sun4v 

You are running SAS 9. Some SAS 8 files will be automatically converted 
by the V9 engine; others are incompatible.  Please see 
http://support.sas.com/rnd/migration/planning/platform/64bit.html

PROC MIGRATE will preserve current SAS file attributes and is 
recommended for converting all your SAS libraries from any 
SAS 8 release to SAS 9.  For details and examples, please see
http://support.sas.com/rnd/migration/index.html


This message is contained in the SAS news file, and is presented upon
initialization.  Edit the file "news" in the "misc/base" directory to
display site-specific news and information in the program log.
The command line option "-nonews" will prevent this display.




NOTE: SAS initialization used:
      real time           0.52 seconds
      cpu time            0.30 seconds
      
1          ******************************************************************************************;
2          options nocenter ps=max mlogic mprint;
3          ******************************************************************************************;
4          
5          ******************************************************************************************;
6          * MACRO DRIVER                                                                            ;
7          ******************************************************************************************;
8          %macro driver;
9          
10         
11            %inc "./common";
12         
13         
14            proc sql;
15               connect to teradata (user=&ish_user password=&prsauto_pwd tdpid='edwapps.prod.xxxx.com' connection=global);
16               %do i=1 %to %sysfunc(countw(&permlist,%str( )));
17                  %let name=%scan(&permlist,&i,%str( ));
18                  execute(grant select on prsauto_work_prod_db.jim_prcng_summary to &name) by teradata;
19                  execute(commit) by teradata;
20               %end;
21               disconnect from teradata;
22         
23         %mend;
24         ******************************************************************************************;
25         
26         
27         %driver;
MLOGIC(DRIVER):  Beginning execution.
MPRINT(DRIVER):   ******************************************************************************************;
MPRINT(DRIVER):   ******************************************************************************************;
MPRINT(DRIVER):   * Define the parameters describing the Teradata connection. ;
MPRINT(DRIVER):   ******************************************************************************************;
MPRINT(DRIVER):   ******************************************************************************************;
MPRINT(DRIVER):   proc sql;
MPRINT(DRIVER):   connect to teradata (user=prsauto password="xxxxxxxxxxxxxxxxxxxxx" tdpid='edwapps.prod.xxxxx.com' 
connection=global);
MLOGIC(DRIVER):  %DO loop beginning; index variable I; start value is 1; stop value is 8; by value is 1.  
MLOGIC(DRIVER):  %LET (variable name is NAME)
MPRINT(DRIVER):   execute(grant select on prsauto_work_prod_db.jim_prcng_summary to MD5030147) by teradata;
MPRINT(DRIVER):   execute(commit) by teradata;
MLOGIC(DRIVER):  %DO loop index variable I is now 2; loop will iterate again.
MLOGIC(DRIVER):  %LET (variable name is NAME)
MPRINT(DRIVER):   execute(grant select on prsauto_work_prod_db.jim_prcng_summary to KA580944) by teradata;
MPRINT(DRIVER):   execute(commit) by teradata;
MLOGIC(DRIVER):  %DO loop index variable I is now 3; loop will iterate again.
MLOGIC(DRIVER):  %LET (variable name is NAME)
MPRINT(DRIVER):   execute(grant select on prsauto_work_prod_db.jim_prcng_summary to PRSAUTO) by teradata;
MPRINT(DRIVER):   execute(commit) by teradata;
MLOGIC(DRIVER):  %DO loop index variable I is now 4; loop will iterate again.
MLOGIC(DRIVER):  %LET (variable name is NAME)
MPRINT(DRIVER):   execute(grant select on prsauto_work_prod_db.jim_prcng_summary to CG482286) by teradata;
MPRINT(DRIVER):   execute(commit) by teradata;
MLOGIC(DRIVER):  %DO loop index variable I is now 5; loop will iterate again.
MLOGIC(DRIVER):  %LET (variable name is NAME)
MPRINT(DRIVER):   execute(grant select on prsauto_work_prod_db.jim_prcng_summary to NN1234567) by teradata;
ERROR: Teradata execute: Database 'NN1234567' does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(DRIVER):   execute(commit) by teradata;
NOTE: Statement not executed due to NOEXEC option.
MLOGIC(DRIVER):  %DO loop index variable I is now 6; loop will iterate again.
MLOGIC(DRIVER):  %LET (variable name is NAME)
MPRINT(DRIVER):   execute(grant select on prsauto_work_prod_db.jim_prcng_summary to KE436904) by teradata;
NOTE: Statement not executed due to NOEXEC option.
MPRINT(DRIVER):   execute(commit) by teradata;
NOTE: Statement not executed due to NOEXEC option.
MLOGIC(DRIVER):  %DO loop index variable I is now 7; loop will iterate again.
MLOGIC(DRIVER):  %LET (variable name is NAME)
MPRINT(DRIVER):   execute(grant select on prsauto_work_prod_db.jim_prcng_summary to KK5078688) by teradata;
NOTE: Statement not executed due to NOEXEC option.
MPRINT(DRIVER):   execute(commit) by teradata;
NOTE: Statement not executed due to NOEXEC option.
MLOGIC(DRIVER):  %DO loop index variable I is now 8; loop will iterate again.
MLOGIC(DRIVER):  %LET (variable name is NAME)
MPRINT(DRIVER):   execute(grant select on prsauto_work_prod_db.jim_prcng_summary to DT426854) by teradata;
NOTE: Statement not executed due to NOEXEC option.
MPRINT(DRIVER):   execute(commit) by teradata;
NOTE: Statement not executed due to NOEXEC option.
MLOGIC(DRIVER):  %DO loop index variable I is now 9; loop will not iterate again.
MPRINT(DRIVER):   disconnect from teradata;
NOTE: Statement not executed due to NOEXEC option.
MLOGIC(DRIVER):  Ending execution.
40         
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           5.28 seconds
      cpu time            0.36 seconds
      

ERROR: Errors printed on page 1.

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
      real time           5.95 seconds
      cpu time            0.75 seconds
      

[ballardw]

Likely you need to show us how the macro variable &permlist is created.

You are placing a name into that list that does not exist. So you need to revise the process that creates the list.

Note: it is often not a good idea to have macro variables just appear in the body of your macro instead of passing them explicitly as a parameter as debugging can get pretty hard in some cases.

[cellurl]

%let permlist=  
               MD5030147 KA580944 PRSAUTO CG482286
               NN1234567 
               KE436904 KK5078688 DT426854;

[cellurl]

is there a noexec-continue, or trycatch or using syserr to continue?? I know having valid users is proper, but I want to see if I can fix this in sas as well.

[ballardw]

Since you placed a value in a list, then what was the rule you used to add a non-existent file to the list?

Most DBMS systems have tools to get lists or create data sets with member, or properties of, data sets. (SAS Dictionary.tables for example) I would work with your Terradata DBA to get the syntax to build a set with the members you want (i.e. exist), read that data and use it to create the code you want. If you have the members you want in a data set it may be preferred to use that data with SAS call execute statement to create the calls you want.

[DavePrinsloo · Accepted solution]

I know my way around Oracle and DB2 pass-through and not really Terradata but I can imagine similar solutions work.

Create a SAS ACCESS library that references the same Terradata schema.

Then in your macro you can do something like:

%if %sysfunc(exist(terralib.&table) %then %do;
    execute ( 
<Native terradata syntax that assumes &table exists>
) by terradata;
%end;

or you can query the terradata data dictionary to determine the existence of the tables.   

Querying database dictionary tables can generally only be done using pass-through,