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: [email protected]
website: www.bccss.bham.ac.uk
****************************************************
-----Original Message-----
From: [email protected] [mailto:[email protected]] 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/
