|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Beating a dead horse - Drop table from procI have a DROP TABLE command in a proc. The proc fails on DROP TABLE, if I run it under any user other than sa. Even with the execute permission. My understanding is, that procs are run with the permission of the proc owner. So this should be fine. I've been through google groups, and it looks to me that if the proc has a DROP TABLE statement, only DDLAdmins can run it ... is that correct? Is there any other way to do this? BTW-I've been through archived postings for the last hour, and I really can't determine if my conclusion is accurate. Or how I can accomplish the same thing without the DROP TABLE command. Thanks in advance., John ---------------------- create proc dbo.TestSec as begin /* drop table if it already exists */ if exists ( select * from dbo.sysobjects where id = object_id(N'[testtbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table dbo.testtbl /* create new table */ select top 10 * into dbo.testtbl from dbo.companies end grant execute on dbo.TestSec to public Why don't you use Trancate table and Insert Into instead of Drop table and
select ... Into. Something like: create proc dbo.TestSec as begin truncatetable dbo.testtbl Insert into dbo.testtbl select top 10 * from dbo.companies end Show quote "John" wrote: > Hi, > > I have a DROP TABLE command in a proc. > > The proc fails on DROP TABLE, if I run it under any user other than sa. > Even with the execute permission. > > My understanding is, that procs are run with the permission of the proc > owner. So this should be fine. > > I've been through google groups, and it looks to me that if the proc has a > DROP TABLE statement, only DDLAdmins can run it ... is that correct? > > Is there any other way to do this? > > BTW-I've been through archived postings for the last hour, and I really > can't determine if my conclusion is accurate. Or how I can accomplish the > same thing without the DROP TABLE command. > > Thanks in advance., > John > > ---------------------- > create proc dbo.TestSec > as > begin > /* drop table if it already exists */ > if exists ( select * > from dbo.sysobjects > where id = object_id(N'[testtbl]') > and OBJECTPROPERTY(id, N'IsUserTable') = 1) > drop table dbo.testtbl > > /* create new table */ > select top 10 > * > into dbo.testtbl > from dbo.companies > end > > grant execute on dbo.TestSec > to public > > > > "Perayu" <Per***@discussions.microsoft.com> wrote in message Thanks for the response.news:7A89BCB7-6DB4-4DF4-A21C-F7121265267B@microsoft.com... > Why don't you use Trancate table and Insert Into instead of Drop table and > select ... Into. > [snip] My example was greatly simplified, but demonstrated the problem. I am using select into because I will not know the columns at design time. I felt this would be the easiest way to do it. I may try to do an alter table and add each columns individually. I hope I can do that. Thanks again. >> I am using select into because I will not know the columns at design time. I felt this would be the easiest way to do it. << Why don't you know the columns at design time?? That is crazy!! Theschema is in place before any application code or procedures are written. "--CELKO--" <jcelko***@earthlink.net> wrote in message That's a good point.news:1121466291.141941.97200@g14g2000cwa.googlegroups.com... >>> I am using select into because I will not know the columns at design >>> time. I felt this would be the easiest way to do it. << > > Why don't you know the columns at design time?? That is crazy!! The > schema is in place before any application code or procedures are > written. The reason why I don't know the fields at design time is because when I initially was hired build the app, the customer had a problem with a previous app. Every time they wanted to add a field, they needed to hire a programmer ... obviously. They really didn't like that and felt as though they were getting screwed. So, when I build the app, I build a feature in where they could add fields on the fly. Basically, I designed a standard data model with all the pre-existing fields, then I added a name-value pair type of thing so the user can add fields just like adding them to thier main table, and maintain them without needing me. It works great and there has never been a problem in 5 years. So, now, the client wants any added "Custom Fields" (that's what we called them), automatically added to a data table export. And that's the long winded explaination of why I will not necessarily know the fields at run time. I thought I would save you the details since it was irrelevant to my problem. Thanks for your feedback. John > So, now, the client wants any added "Custom Fields" (that's what we called If it's just for export then tell them to buy a proper ETL tool. It> them), automatically added to a data table export. will likely be cheaper and more reliable than trying to do this dynamically in TSQL. -- David Portas SQL Server MVP -- Despite some derogatory comments among these posts, your solution actually
appears to be a good one and is very reminiscent of Oracle's approach in their Oracle Applications suite, although they provide user-definable columns that map to existing generic-named columns in the base table. Had you considered using dynamic SQL in your SP? You're in full control of the format/content of the result set and don't need the DROP/ALTER capabilities. You only need for the result-set consumer to be aware of the file schema each time you export, but you've probably covered that... And, as your base schema may in all likelihood have changed and execution is infrequent, you can ignore the recompilation factor. Spelling and grammar checked before posting ;-) "SAinCA" <SAi***@discussions.microsoft.com> wrote in message Thanks for the compliment. I am confident it is a good design, but it's news:9B8D00AD-276E-419A-90CA-846D863DE1F4@microsoft.com... > Despite some derogatory comments among these posts, your solution actually > appears to be a good one and is very reminiscent of Oracle's approach in [snip] still reassuring to hear at least one other developer doesn't think it's crap. ;-) As far as the app goes, I just split the functionality and put some into the external tool. It's not a clean solution, but it's the fastest & most economical. Thanks again, John The simplest solution would be to build the table from a scheduled stored proc
which can run under admin privledges. If this is a report, then I would simply push a value into a "queue" table and have the stored proc run continuously checking for work. When it finds a value in the queue table, it builds a dynamic SQL statement that drops and builds your table. The one problem you may run into by using purely T-SQL, is that you are limited in the size of the statement you send to the Exec or sp_executesql statement. If the column names are long, then I would write the dynamic sql such that it creates a table with the first column and cycles through dynamic columns calling Alter Table Add... against the temp table. Thomas Show quote "John" <Please.Re***@To.the.group.com> wrote in message news:eJ6OdWXiFHA.3064@TK2MSFTNGP15.phx.gbl... > > "Perayu" <Per***@discussions.microsoft.com> wrote in message > news:7A89BCB7-6DB4-4DF4-A21C-F7121265267B@microsoft.com... >> Why don't you use Trancate table and Insert Into instead of Drop table and >> select ... Into. >> > [snip] > > Thanks for the response. > > My example was greatly simplified, but demonstrated the problem. I am using > select into because I will not know the columns at design time. I felt this > would be the easiest way to do it. > > I may try to do an alter table and add each columns individually. I hope I > can do that. > > Thanks again. > > John wrote:
Show quote > Hi, Create Table / Drop Table is not an inherited right. Why not have the > > I have a DROP TABLE command in a proc. > > The proc fails on DROP TABLE, if I run it under any user other than > sa. Even with the execute permission. > > My understanding is, that procs are run with the permission of the > proc owner. So this should be fine. > > I've been through google groups, and it looks to me that if the proc > has a DROP TABLE statement, only DDLAdmins can run it ... is that > correct? > Is there any other way to do this? > > BTW-I've been through archived postings for the last hour, and I > really can't determine if my conclusion is accurate. Or how I can > accomplish the same thing without the DROP TABLE command. > > Thanks in advance., > John > > ---------------------- > create proc dbo.TestSec > as > begin > /* drop table if it already exists */ > if exists ( select * > from dbo.sysobjects > where id = object_id(N'[testtbl]') > and OBJECTPROPERTY(id, N'IsUserTable') = 1) > drop table dbo.testtbl > > /* create new table */ > select top 10 > * > into dbo.testtbl > from dbo.companies > end > > grant execute on dbo.TestSec > to public table always created and delete the rows when needed or use a temp table. What would happen in your case if two users executed the procedure at the same time? "David Gugick" <david.gugick-nospam@quest.com> wrote in message Thanks for your response.news:uHXeLQXiFHA.1204@TK2MSFTNGP12.phx.gbl... [snip] > Create Table / Drop Table is not an inherited right. Why not have the > table always created and delete the rows when needed or use a temp table. > What would happen in your case if two users executed the procedure at the > same time? [snip] I am not using a previously created table because I won't know the fields at design time. As for 2 users running this at the same time ... that's a good question. But it's not a possibility .. this isn't a web-app or anything, it's a client server app in an office of only a few people, with one person doing all editing. This procedure will only be run every 6 months. I am going to try to put an ALTER TABLE in the proc to add the columns individually. I hope that works. Thanks again, John John wrote:
Show quote > "David Gugick" <david.gugick-nospam@quest.com> wrote in message I doubt that will fix the problem. You will have to grant that > news:uHXeLQXiFHA.1204@TK2MSFTNGP12.phx.gbl... > [snip] >> Create Table / Drop Table is not an inherited right. Why not have the >> table always created and delete the rows when needed or use a temp >> table. What would happen in your case if two users executed the >> procedure at the same time? > [snip] > > Thanks for your response. > > I am not using a previously created table because I won't know the > fields at design time. > > As for 2 users running this at the same time ... that's a good > question. But it's not a possibility .. this isn't a web-app or > anything, it's a client server app in an office of only a few people, > with one person doing all editing. This procedure will only be run > every 6 months. > I am going to try to put an ALTER TABLE in the proc to add the columns > individually. I hope that works. > > Thanks again, > John particular user create table rights in the database. If the user is the only one that needs access to this dynamically created table, then it can be created with the user as the owner rather than dbo. John wrote:
> I am not using a previously created table because I won't know the fields at Sounds like a perfectly valid reason to use a temporary table to me.> design time. > Is there a reason that you're not using a temp table? Stu
Show quote
"Stu" <stuart.ainswo***@gmail.com> wrote in message Good question.news:1121462476.046652.304380@g49g2000cwa.googlegroups.com... > > > John wrote: > >> I am not using a previously created table because I won't know the fields >> at >> design time. >> > > Sounds like a perfectly valid reason to use a temporary table to me. > Is there a reason that you're not using a temp table? > I am not using a temp table because there will be an external app, which links to the table and runs with it from there. And my understanding is that temp tables are dropped when the user session ends. This is basically, a pre-built report. Regards, John You want to create a table that a user will be inputting data to, but you
don't know the structure before-hand? Just wondering how you plain on storing/retrieving unstructured data from SQL and making it meaningful. And if it is truly unstructured data, then why use SQL? If it is structured then why don't you know the structure beforehand? Do you really want the person doing the editing designing their own schemas on the fly as they enter data? Show quote "John" <Please.Re***@To.the.group.com> wrote in message news:uA2lnZXiFHA.3716@TK2MSFTNGP14.phx.gbl... > "David Gugick" <david.gugick-nospam@quest.com> wrote in message > news:uHXeLQXiFHA.1204@TK2MSFTNGP12.phx.gbl... > [snip] >> Create Table / Drop Table is not an inherited right. Why not have the >> table always created and delete the rows when needed or use a temp table. >> What would happen in your case if two users executed the procedure at the >> same time? > [snip] > > Thanks for your response. > > I am not using a previously created table because I won't know the fields > at design time. > > As for 2 users running this at the same time ... that's a good question. > But it's not a possibility .. this isn't a web-app or anything, it's a > client server app in an office of only a few people, with one person doing > all editing. This procedure will only be run every 6 months. > > I am going to try to put an ALTER TABLE in the proc to add the columns > individually. I hope that works. > > Thanks again, > John > > > "Michael C#" <ho***@boutdat.com> wrote in message No, this is basically a prebuilt report. It's not suppose to be written to.news:eDUGbRYiFHA.3436@tk2msftngp13.phx.gbl... > You want to create a table that a user will be inputting data to, but you > don't know the structure before-hand? > Just wondering how you plain on storing/retrieving unstructured data from I don't want the user having any input on the schema, otherwise I could just > SQL and making it meaningful. And if it is truly unstructured data, then > why use SQL? If it is structured then why don't you know the structure > beforehand? Do you really want the person doing the editing designing > their own schemas on the fly as they enter data? give them DBO authority right? Problem solved ... in a sloppy incompetent kind of way. ;-) My understanding is, that the commands inside the proc will run with the rights of the proc owner. That's what I was counting on to be able to manage that table in a secure manner .. I just wasn't counting on the DROP TABLE statement being authorized against the users rights. Also, I don't feel that having the DROP TABLE statement in a proc designed by the proc owner (assuming they have rights) is like giving the user free riegn over your schema. Also, my choice of using T-SQL for this, was solely for speed, I thought I could have this done without problems in 2 hours. Thanks for the feedback. Respectfully, John Using temp tables is probably not an answer. They are usually a sign
that the programmer is mimicing a mag tape sysrtem and procedural code in which the results of each proceudrla step are saved on a scratch tape and passed long to the next sequential step. John, you obviously do not know what you are doing. A newsgroup is not a good place to learn how to write a database. Try to find a local person to help you and make sure they have 5-10 years experience so you are not getting another newbie. Or temp tables could be used as part of a completely valid operation
(cleaning data, or maintaining a legacy system, or part of the process of transforming data from a legacy system to a better design). While I agree that temp tables are a second-class solution to database design, they can be useful in the design (and maintenance) phases. >> While I agree that temp tables are a second-class solution to database design, they can be useful in the design (and maintenance) phases. << I understand using them for ELT (not ETL). But almost never as part ofa production RDBMS. >> I have a DROP TABLE command in a proc. << Why?? A stored procedure should never, never alter the schema on thefly!! This is basic software engineering priniciple. What were you thinking??  |
|||||||||||||||||||||||
Other interesting topics