topic Re: ODBC to MS SQL server permissions in SAS Programming

ODBC to MS SQL server permissions

BenConner — Wed, 30 Nov 2016 17:04:55 GMT

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

Re: ODBC to MS SQL server permissions

snoopy369 — Wed, 30 Nov 2016 20:29:45 GMT

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.

Re: ODBC to MS SQL server permissions

BenConner — Thu, 01 Dec 2016 01:33:30 GMT

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

Re: ODBC to MS SQL server permissions

Ksharp — Thu, 01 Dec 2016 09:54:18 GMT

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;

Re: ODBC to MS SQL server permissions

snoopy369 — Thu, 01 Dec 2016 14:59:29 GMT

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.

Re: ODBC to MS SQL server permissions

BenConner — Thu, 01 Dec 2016 20:20:34 GMT

Thanks! Anything I should tell them to look for?

--Ben

Re: ODBC to MS SQL server permissions

BenConner — Thu, 01 Dec 2016 20:22:07 GMT

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

Re: ODBC to MS SQL server permissions

snoopy369 — Thu, 01 Dec 2016 20:28:29 GMT

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.

Re: ODBC to MS SQL server permissions

BenConner — Fri, 02 Dec 2016 14:17:54 GMT

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