Allowing an applicaiton to create it's own tables

I have recently come across a situation where someone has asked me to
give an application the ability to create it's own data tables, stored
procs and other items in SQL Server 2000 if they do not exist in the
database that the client has pointed the application too. I find this
very troubling and have come up with a number of reasons why I don't
think this is a good idea, but would like some input from any DBA's or
Microsoft MVPs that care to comment on this situation. I was
considering posting my own ideas, but I think I would rather compare my
idea's to what input I recieve from the community afterwards so that I
have not 'lead the witness' or tainted the input from the community.
Thank you all in advance for any input, I appreciate any professional
opinions I can get my hands on.
Sincerely
russI'm not an MVP, but I have an opinion. Not sure what kind of
applicaiton you're writing. If you're writing a database utility
(Enterprise Manger, Embarcadero, etc.) then sure, this sounds like
something that's well in the scope of that. If you're writing any sort
of app that's going to be used by a a non-developer/non-DBA, then why
would they need access? If you have a one user application and that one
user is the DBA, then yes - maybe. But - in general, very bad idea. I
agree with you. The user/application could wreak havoc on the DB.
rhaley@.axys.com wrote:
> I have recently come across a situation where someone has asked me to
> give an application the ability to create it's own data tables, stored
> procs and other items in SQL Server 2000 if they do not exist in the
> database that the client has pointed the application too. I find this
> very troubling and have come up with a number of reasons why I don't
> think this is a good idea, but would like some input from any DBA's or
> Microsoft MVPs that care to comment on this situation. I was
> considering posting my own ideas, but I think I would rather compare my
> idea's to what input I recieve from the community afterwards so that I
> have not 'lead the witness' or tainted the input from the community.
> Thank you all in advance for any input, I appreciate any professional
> opinions I can get my hands on.
> Sincerely
> russ|||If this is for an end user, (?) granting an end user the ability to write
his own Stored Procs would be out of the question for most DBA's. I don't
know what these SP's may do, how much resources they may consume, what
security I would need to assign, what blocking/ deadlocking they may
introduce, etc.
<unc27932@.yahoo.com> wrote in message
news:1123175440.352742.216950@.g14g2000cwa.googlegroups.com...
> I'm not an MVP, but I have an opinion. Not sure what kind of
> applicaiton you're writing. If you're writing a database utility
> (Enterprise Manger, Embarcadero, etc.) then sure, this sounds like
> something that's well in the scope of that. If you're writing any sort
> of app that's going to be used by a a non-developer/non-DBA, then why
> would they need access? If you have a one user application and that one
> user is the DBA, then yes - maybe. But - in general, very bad idea. I
> agree with you. The user/application could wreak havoc on the DB.
> rhaley@.axys.com wrote:
>|||The customer wants an installation program? That's fine. The customer
wants an installation program that runs each time he starts up the app?
That's a bit more unusual! It seems to me that if the tables and procs
weren't there then I'd want to know about it, not have the DB
automatically create a clean installation.
What is the application for and why wouldn't the tables be there when
the app is run?
Also, if this is a multi-user app and you roll out a new release then
how will you ensure that everyone gets the new release simultaneously?
Otherwise an extant prior release could end up replacing your new
version of the database with an older one.
David Portas
SQL Server MVP
--|||The idea from the client was that they would be able to point this
application at any database and use it as an 'Add On' application.
Although this idea is neat, it's kinda half baked (as he has agreed
since I showed him some comments).
All really great points guys, thank you very much. As you have all
pointed out, it's rather silly to have an application just re-create
tables and other DB items 'willy nilly' when it doesn't find them. Some
other issues I felt were of concern were:
- The only way to create tables in a database is to give the
application SA or CREATE TABLE rights on the database or database
instance, which is very uncool for an application that users have
access too.
- How do you ensure that the tables and SPs are of the same version
that the application needs without using System Tables or
INFORMATION_SCHEMA and itterating through every bloody column and
comparing data types and field lengths?
- What if user JSmith creates the tables and then later they change to
a different user? The application won't be able to find the tables
anymore and will re-create them, effectively 'losing' the old data
- "duh... Master sounds like a good database to put this in..."
- And last but not least, I'm on a small network and there are 17
exposed instances of SQL server/MSDE kicking around... have fun finding
the right 'version' of the tables ever again.
I think the solution is probably what David was hinting at - create a
separate installation program for the database and/or tables that
prompts for credentials (UID/PWD) so that an administrator with valid
sa or Create Table rights can take care of things. This will save alot
of headaches and keep a truck load of DBAs from coming over to my
office and lynching me!
Cheers guys!
Russ