Pre-Assigned Library Appears in SAS EG, Registered Tables Do Not

[shoin]

Pre-Assigned ODBC DB library, shows up with the expected icon (yellow folder), however registered tables underneath (visible in SMC), do not.

1. The end user could do a data step calling one of the reg. table and could access no issue. They just do not appear
2. SYS DSN valid, connection test is successful
3. Permissions and SAS Folder permission are as expected
4. Checked for the schema (dbo) and ensured it was there in library properties.
5. I am working with the DBA who assures that there is no authentication and READ GRANT issue.
6. Issue is for everyone who has access to this library.

I found this link of a previously answered note: https://communities.sas.com/t5/General-SAS-Programming/pre-assigned-libraries-in-SAS-94-are-not-load... however, I do not see any such option in the .bat file for WSS nor in SMC.

I am lacking what else to look at.  Note two weeks ago when the pre-assigned was established same user was able to see all those registered tables show up in EG and was able to drag n drop a table (able to use).  To re-word, they still show only in SMC library pre-assigned icon shows up in EG but no registered tables underneath.

Any insights are appreciated an thank you in advance,

Shoin

[gwootton]

It sounds like the users do not have ReadMetadata permission on the registered tables, did you check those as well as the permissions on the library? Table permissions are inherited from their parent folder, rather than the library.

[shoin]

Hi @Greg the security is very simple (few users) and all are members of SASUSERS and it has the default permissions RM, WMM, read.

TY for your suggestion

[SASKiwi]

Do you have a special need to register the database tables? If you instead assign an extended attribute on the pre-assigned library like so:

then all tables will show without registering.

[shoin]

Hi and TY.

The registration is not the issue.  The tables in EG do not how up, same registered tabled show in SMC.  In the EG sesison, pre-assigned shows up proper yellow icon.  However, no registered tables, yet end user could do a data step using any one of the reg. tables.

[SASKiwi]

So did you try my solution to see if the tables showed in EG with no registration required?

[shoin]

Hi, yes I actually created a different library, assigned extended attribute AssignMode 2, and results were the same.

[SASKiwi]

No tables showing? Is the library is pre-assigned? It won't work otherwise. Also check the database and schema definition.

[shoin]

Hi, yes library is pre-assigned.

I am stumped.

1. For user1, in EG can see the library folder icon (pre-assigned) > Folder show no tables (shows in SMC) > can write a small data step accessing one of the registered table successfully. This means:
   1. SYS DSN working
   2. GRANTs on DB tables working
2. User1 has simple group and roles memberships (EG Advance, AMO advance, SAS Studio Usage)
3. Library has RM, R in SAS Folder for SASUSERS
4. WSS also has appropriate permissions for SASUSERS
5. Do not see any options in Object spawner or SASApp logical servers to have any thing denying pre-assigned
6. Libname string copied (display libname) and running in BASE and or EG works.
7. Connection servers all normal 

[SASKiwi]

@shoin  - I'm running out of ideas too. We use metadata-defined pre-assigned libraries with AssignMode = 2 exclusively and there are no problems with tables showing.

What happens if you assign the same external database in a LIBNAME statement? Do the tables show in EG (need to refresh the Server List after running the LIBNAME)?

[shoin]

I am sorry that I did not reply. Yes in a EG session if run a libname directly it would show. 2 weeks ago, we cleaned up this issue but then suddenly the reg. tables etc disappeared and have been banging head against it. I will compile what I have tried so far (I did try your suggestion too @SASKiwi) but to no avail. We checked if there was GRANT issue on the target DB, the DBA swears it is not. Checked authentication of end users at DB not an issue either.

Wish me luck.

[SASKiwi]

If your EG LIBNAME works fine then it is your metadata data library settings that are causing the problem. I would try these tests:

• Confirm EG database LIBNAME still works and tables in the Server List display.
• Create new metadata data library set up the same as the LIBNAME statement.
• Ensure  the library is pre-assigned (although this shouldn't be essential to work in EG only batch mode) and set AssignMode = 2. Do NOT register any tables.
• Do the tables appear when you expand the library in the Server List?

[gwootton]

You may wish to open a technical support track on this issue so we can take a look at the behavior.

[Sajid01]

Hello
Please check if the tables were registered as unrestricted user account.
If yes, that could be an issue.

[shoin]

Will check that. Thx.