DATA Step, Macro, Functions and more

ODBC to MS SQL server permissions

Accepted Solution Solved
Reply
Regular Contributor
Posts: 169
Accepted Solution

ODBC to MS SQL server permissions

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


Accepted Solutions
Solution
‎12-02-2016 09:15 AM
Regular Contributor
Posts: 244

Re: ODBC to MS SQL server permissions

[ Edited ]

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.

View solution in original post


All Replies
Regular Contributor
Posts: 244

Re: ODBC to MS SQL server permissions

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.
Regular Contributor
Posts: 169

Re: ODBC to MS SQL server permissions

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

Super User
Posts: 9,691

Re: ODBC to MS SQL server permissions

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; 


Regular Contributor
Posts: 169

Re: ODBC to MS SQL server permissions

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

Regular Contributor
Posts: 244

Re: ODBC to MS SQL server permissions

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.

Regular Contributor
Posts: 169

Re: ODBC to MS SQL server permissions

Thanks!  Anything I should tell them to look for?

 

--Ben

Solution
‎12-02-2016 09:15 AM
Regular Contributor
Posts: 244

Re: ODBC to MS SQL server permissions

[ Edited ]

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.

Regular Contributor
Posts: 169

Re: ODBC to MS SQL server permissions

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 384 views
  • 1 like
  • 3 in conversation