ODBC to MS SQL server permissions

[BenConner]

Hi,

Was recently granted access to tables in a MS SQL Server database.  Selects on tables work fine, but when I use a libname in odbc datasrc='...'; statement and run a proc contents data=in._all_; I get nothing back.

What permissions does proc contents need that I wasn't given?

Thanks!

--Ben

[snoopy369]

I just ran that on a libname I have relatively little permission on (just read), so I don't think it's necessarily a permissions issue.

Did you properly set the schema? If your database uses schemas other than DBO, it may work if you run selects but won't necessarily work in conjunction with the metadata-based SAS pieces (such as this) on datasets not in the DBO schema, unless you set which schema you're accessing.

[BenConner]

This is on a SAS server and the ODBC entry was pre-defined.  So I don't know what the schema was, unfortunately, much less if it was set properly.  I don't have access directly to the DB server.  So that's why I had to phrase the question as I did.

--Ben

[Ksharp]

You can use the following to get schema. but firstly you could try schema=dbo ,
maybe could solve your problem.

proc sql;
connect to odbc(.)
 select * from connection to odbc(ODBC::SQLTables);
quit; 

[BenConner]

Wow.  Didn't even know what this did.  That returned a large amount of information.  Will have the DBA compare it to what we should have assigned to us for a Table schema.  Thanks!

--Ben

[snoopy369]

You may have to talk to your SAS admin then who set up the ODBC connection, as I am fairly sure that's where the problem lies.

[BenConner]

Thanks!  Anything I should tell them to look for?

--Ben

[snoopy369 · Accepted solution]

They should know what to do, if you specify that the ODBC connection should include the specific schema you're looking for.  (and, they should be able to figure out what schema you mean if they know what tables you're accessing, so long as those table names aren't used in mulitple schemas)

You might look at https://support.sas.com/techsup/technote/ts765.pdf for more information about schemas (about halfway down the doc).  This also discusses the code KSharp included.

[BenConner]

Looks like the schema wasn't included in the default definition we use to access the DB; I have to include it explicitly. So the code KSharp provided told me what schema they had given to it.

Thanks to all with this!

--Ben