topic Re: Pre-Assigned Library Appears in SAS EG, Registered Tables Do Not in Administration and Deployment

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

shoin — Tue, 11 May 2021 15:01:07 GMT

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

The end user could do a data step calling one of the reg. table and could access no issue. They just do not appear
SYS DSN valid, connection test is successful
Permissions and SAS Folder permission are as expected
Checked for the schema (dbo) and ensured it was there in library properties.
I am working with the DBA who assures that there is no authentication and READ GRANT issue.
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-loading-during-users/td-p/146946 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

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

gwootton — Tue, 11 May 2021 15:04:44 GMT

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.

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

shoin — Tue, 11 May 2021 15:08:43 GMT

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

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

SASKiwi — Tue, 11 May 2021 19:57:48 GMT

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.

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

shoin — Tue, 11 May 2021 20:29:30 GMT

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.

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

SASKiwi — Tue, 11 May 2021 21:06:36 GMT

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

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

shoin — Tue, 11 May 2021 22:58:24 GMT

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

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

SASKiwi — Tue, 11 May 2021 23:13:08 GMT

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

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

Sajid01 — Tue, 11 May 2021 23:50:11 GMT

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

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

shoin — Tue, 11 May 2021 23:59:28 GMT

Hi, yes library is pre-assigned.

I am stumped.

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
User1 has simple group and roles memberships (EG Advance, AMO advance, SAS Studio Usage)
Library has RM, R in SAS Folder for SASUSERS
WSS also has appropriate permissions for SASUSERS
Do not see any options in Object spawner or SASApp logical servers to have any thing denying pre-assigned
Libname string copied (display libname) and running in BASE and or EG works.
Connection servers all normal

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

SASKiwi — Wed, 12 May 2021 01:23:45 GMT

@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)?

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

gwootton — Wed, 12 May 2021 13:59:49 GMT

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

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

shoin — Mon, 07 Jun 2021 16:19:12 GMT

Will check that. Thx.

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

shoin — Mon, 07 Jun 2021 16:23:22 GMT

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.

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

SASKiwi — Mon, 07 Jun 2021 20:08:31 GMT

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?

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

shoin — Fri, 11 Jun 2021 17:51:57 GMT

Hi, no it was done by a user w' sas administrators group membership.

We checked the TLS setting on the compute server, it was at 1.2, target DB is sql server 2016, so we downloaded the MS SQ Sever driver (17) that supports. The connection was successful.

The user testing ran a libname against it was able to see tables. I need to check if registered tables appeared in SMC. However, in a new EG session the same user if expands the said library, sees table but could not access. The setup is very simple.
0 custom groups.
permissions are SASUSERS > RM.
Library is set w native engine
No autoexec no hidden config. Zilch.
Target DB set w NT authentication/AD so everyone is using their own set of credentials.
Also it is ACCESS/MS SQL SRVR module.
SYS DSN test to target DB also successful.

Like to jump off the first floor.

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

Sajid01 — Fri, 11 Jun 2021 18:47:03 GMT

Hi, no it was done by a user w' sas administrators group membership.
Hope they don't have the unrestricted permission.

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

shoin — Fri, 11 Jun 2021 19:52:54 GMT

🙂 no, member sas admin group

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

Sajid01 — Tue, 15 Jun 2021 15:44:50 GMT

Try registering the tables with sasdemo account.

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

shoin — Thu, 19 Aug 2021 22:38:46 GMT

After some further investigation turned out there was schema error, relevant schema was not chosen for the library. Once we obtained the schema listing of all tables in a DB and identified what was relevant, we were okay.

TY all for your kind help.