How to disable tables without SAS-metadata in SQL Server library?

[Miron]

Hello,

to get data directly from SQL Server, I have created a new Server in MC, new AuthDomain, library etc.

Additionally I used preassigned library option.

The connection works fine, when I click - register tables in MC, I can see the list of SQL Server DB tables, and I can register tables I want. Everything is fine.

But, when I expand this library in Enterpise Guide, I can see all tables and views that are in the DBMS, as they are already registered in SAS. But they are not! (They do not have metadata)

My question, why are they displayed? And how can I avoid displaying long list of unregistered tables?

Thank you!

Regards,

Miron

[RichardinOz]

I think the answer is along these lines:

SAS EG was developed initially to correspond to similar rules regarding access to files and tables as Base SAS.  In Base SAS if you specify a destination in a libname, all tables in that location are available to be processed.  SAS does not check whether the tables have ben registered or not.

SAS DI Studio, on the other hand, is metadata driven.  If you have DI Studio you will not be able to asee the tables until they are registered.

Richard

[LinusH]

I don't think this is entirely true for this case. I think the key here is how the sql server libname is assigned. If the library is not pre-assigned or assigned using the meta engine, only registered tables should be visible.  You can check this by looking which tables are visible in the folders pane.

[jakarman]

I would not classify it as a problem but needing more understanding of:
a/ the users, what is the business goal.

b/ the technical backgrounds and all the interactions.

c/ The functionality in all kind of SAS tools (and beyond)

ad a/ If you are thinking your role is the Muppetplayer (big brother) and the users (analists) are just marionets, there is some mismatch in expectations.   

ad b/ If you are thinking SAS metadata is overruling the OS-Systems policies and options? Come back out of those dreams to the real life. Accept OS levels are necessary 

ad c/ There are a lot of tools with SAS and the targeted users are different. You have developpers (code builders), metadata/data stewards, Analist and the well known classic user.    

EnterpriseGuide is a switch knife for the developer/data stewards Analists, not for the classic user. It has even an autoexec flow for starting up. A promoted use is to put libnames in that location so out of control by IT-staff.

That works marvelous for the analists. You could define limitations on that, but what you will do in that case is trying to block their environment they need to do their job.  

Analists commonly do not know wicht data exactly is needed what they will use and what they will find to investigate further. They have a weakly described business goal but that can be very important. 
These uncertaintities are requiring a mindswitch to really help them.

When the analists are allowed to get to the DBMS (decision made by business) let them.

The only thing to bother about is the technical approach: aditable traceable etc. (no password in code) working by their personal key. These are normal IT-requiremetns to be solved.  

The answer will be: When they have access to the DBMS by their personal key and that is limited by what they are allowed to do, It is not your problem.  

[Patrick]

As points out the way you assign the library is relevant. Have a read of this document: http://support.sas.com/documentation/onlinedoc/guide/EG43MetaLibraries.pdf

It's SAS 9.2 but still very valid for what you need (there are now also metadata bound libraries since SAS9.3 - but that's more for covering special requirements).