Updating SAS metadata is best done in SAS DATA steps, not in SQL, as you have a lot more control and can use the metadata read and update functions.
Here is the SAS Guide for this: Metadata Interface Guide
Please bear in mind that the metadata data model is horribly complicated, and unraveling it is a steep learning curve. There is always the danger of corrupting the metadata repository if your updates don't work correctly. You should ALWAYS create a backup before you update - easy to do in SMC.
Personally I prefer to stick to configuring metadata in SMC. It's a lot easier and a lot less dangerous. However I'm lucky in that we only have a small number of SAS users so manual maintenance does not take long.
If you have a LOT of SAS users then automated metadata updates make a lot more sense. In this case you should be syncing your metadata users from Active Directory or Unix equivalent as that will save a huge amount of work and possibly avoid some of the permissions errors you are seeing. SAS provides programs for doing this.
@lgsucupira - The link I sent you contains examples of reading and updating SAS metadata. They are a good place to start. I also suggest you Google for SAS metadata examples also. The only supported programs I'm aware of are those mentioned in the documentation and the LDAP / AD account synchronisation with SAS metadata:
@lgsucupira - I have yet to find many use cases where the long and steep learning curve is worth it 😃. Good luck! Also please note that if you are planning to move to SAS Viya in the near future like many SAS users then the problem disappears - there is no metadata in Viya. We are planning such a move ourselves so it is pointless for us to spend a lot of time on understanding metadata structure.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.