Lost write priveleges when upgraded to SAS EG 8.2 (ODBC Connect SQL database)

[StefanBacklin]

Hi,

I have just upgraded from EG 8.1 to EG 8.2 when my connection to SQL database started to fuss. First, it does not load all tables, especially not the new once. Secondly, I lost the write/read and now I have just read access.

Here is the code I use to set up the connection.

libname mydata odbc noprompt="driver=ODBC Driver 17 for SQL Server;
server=xxxxxxxxxx;
Trusted_Connection=Yes;
DATABASE=mydata;" read_lock_type=NOLOCK bcp=yes schema=dbo;

So, a lot of tables seems to be missing and I cant write to the database any longer.

Nota bene: With MS SQL Server Management studio I can see all tables and I can write to the database so it's nothing wrong there.

I have googled a lot but can't find an answer to this problem.

Please help me out.

Best Regards Stefan Backlin

[CaseySmith]

By "it does not load all tables", do you mean the tables do not appear in the Servers pane or file open dialog in EG?

If so, first I recommend running this code to make sure the tables you expect to see in that library are in fact in the library from the server's point-of-view:

proc sql;
select * from sashelp.vtable
where libname eq "MYDATA";
quit;

If all the tables you expect to see are in the results from running that code, then it may be a display issue in EG 8.2.  There were a couple bugs related to library and table population in the EG 8.2 Servers pane and file open dialog that were fixed in EG 8.2 Update 4.  If you don't already have 8.2 Update 4 (check in Help->About), I *highly* recommend you install it.  (You can install the latest update via Help->Check for updates or the more traditional SAS Deployment Manager method by downloading and install the update here: http://ftp.sas.com/techsup/download/hotfix/HF2/G5J.html)

Casey

[StefanBacklin]

Thank you for reaching out.

I liked that snippet of code you sent. Unfortunately, the same tables are also "missing" in sashelp.vtable. The funny part is that tables created after April are missing, it seems so anyway.
EG 8.2 update 4 is installed.

My fellow colleague has the same installation, run the same code to connect via odbc to SQL server and he can read and write and has access to all table in the library "MYDATA". Strange

[SASKiwi · Accepted solution]

Get your colleague to log in on your computer and repeat the same test. If that fixes the problem then the issue is user-related and nothing to do with SAS or EG.

Alternatively ask to log on to your colleague's computer and if you still have the same problem then it is definitely user-related - check with your SQL Server DBA.

[CaseySmith]

I agree, it sounds related to the user-permissions set in your DBMS, unrelated to SAS or EG.

Casey