Statalist The Stata Listserver


[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

RE: st: Using ODBC


From   David Winter <d.l.winter@bham.ac.uk>
To   <statalist@hsphsun2.harvard.edu>
Subject   RE: st: Using ODBC
Date   Thu, 22 Feb 2007 07:41:35 +0000

Many thanks for the help with this!

****************************************************
Mr David L. Winter, HNC
I.T. Manager
Centre for Childhood Cancer Survivor Studies
Department of Public Health & Epidemiology
University of Birmingham
Edgbaston
Birmingham
B15 2TT
UK
tel.: +44 (0)121 414 6766
fax.: +44 (0)121 414 7923
email: d.l.winter@bham.ac.uk
website: www.bccss.bham.ac.uk
****************************************************
-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Joseph Coveney
Sent: 21 February 2007 14:56
To: Statalist
Subject: Re: st: Using ODBC

David Winter wrote:

I use SQL server 2000 to store my data.  I have set up DSNs to the various
databases and pushed them to the workstations on my network.  The SQL server
security settings are different for each user and database.  I have recently
discovered the -odbc- command.  My questions are:

Are the security settings from the underlying SQL tables inherited by Stata.
For example, I have a database for reference with read-only tables. Will
Stata still allow records/variables to be added or data overwritten?

How does the -odbc load- command work? It appears to load the data as a
read-only view.

What I want to be able to do allow users to load a reference file, create
new variables and run analyses but not alter the original data in any way.

--------------------------------------------------------------------------------

The security settings on the database and its tables would be independent
of Stata.  Stata's -odbc- will use the DSNs that you've set up for the
users.  If you're using Windows security mode, then SQL Server 2000 should
see a login reflecting the log-in that the user is running Stata under.  If
you're using SQL Server authentication, then the SQL Server will see
whatever the user types into the dialogue options (-user()-
and -password()-) in -odbc-, unless you've already set those in the DSN.

My understanding and experience is that -odbc load- essentially does a
SELECT on the table, giving the Stata user a snapshot at the time the
command is executed.  It does not create an updatable view; so, changing a
variable's contents in Stata after -odbc load- does nothing to the table in
the database.

As far as I'm aware, you can rely upon conventional RDBMS techniques (data
control language, groups and rôles, views, attending to ownership, etc.) to
prevent Stata users from altering the tables with SQL Server 2000:  as far
as SQL Server 2000 is concerned, Stata is just another analytical
application.

Joseph Coveney

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index