Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2005
    Posts
    45

    Unanswered: Owner of table creation in SP

    Hi everyone and Happy Holidays!

    I've got a problem with table creation in stored procedures (SQL Server 2000). We've got an application where the user login only has rights to execute stored procedures. The problem is that a stored proc is dynamically creating a table and so the owner of that table is being assigned to whatever login the application is using instead of dbo. It's causing numerous issues. Is there any way that this can be avoided or changed without granting the user sa privileges?

    Thanks in advance,
    Cat

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I never write application code that creates permanent database objects on the fly. it's bad news more often than not.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2005
    Posts
    45
    Hi Thras,

    They're not actually permanent, but they need to stick around for longer than the stored proc that's creating them.

    Cat

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    How about this then. Create the table as dbo.<tablename> with the needed columns, a guid as the PK, and a DatePopulated column as an indexed column. When the user needs to insert data, grab a guid and getdate() and put it all in the table. Save the guid for later use.

    If the user needs to retrieve the data, use the held guid to access it.

    Have a scheduled job that runs periodically (daily, hourly, whatever) that deletes from the table after the desired retention period has expired.

    No more tables created by users, schema gets backed up with your backups, everyone is happy!

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ????????

    What happens when the same sproc executes at the same time?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Each spid gets a different guid ... unique dataset per loser ... err I mean user.

    ALso have seen that technique used for delayed paging ... sweep data every 20 minutes or so ro remove stale data.

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    no, not your idea to have 1 table...what happens when the sproc is creating a table and it is executed at the same time?

    ka boom

    why not a temp table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I saw a design once where the developer appended the tables he created on the fly with the user name and if there was a table already there he would add a incrementing number after the user name. it was amazingly bad and problematic and junked up the database and the execution plans something fierce because the table were not always removed. I offered another solution. He rejected it because it was too much work. he was my boss. My tenure there was short.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Thrasymachus
    I saw a design once where the developer appended the tables he created on the fly with the user name and if there was a table already there he would add a incrementing number after the user name. it was amazingly bad and problematic and junked up the database and the execution plans something fierce because the table were not always removed. I offered another solution. He rejected it because it was too much work. he was my boss. My tenure there was short.

    Was the boss short?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Brett Kaiser
    no, not your idea to have 1 table...what happens when the sproc is creating a table and it is executed at the same time?

    ka boom

    why not a temp table?
    Sorry Brett ... real live dba creates one and only one table before any proc runs. Proc inserts into the table, and retrieves data as needed. Scheduled job clears the table of stale data.

    Proc does not create table, that way no loser tables to clean up!

    Quote Originally Posted by Brett Kaiser
    Was the boss short?
    With pointy hair!

    -- This is all just a Figment of my Imagination --

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Brett Kaiser
    Was the boss short?
    no. he was rather tall.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Jun 2005
    Posts
    45
    The table name has an identifier imbedded into it, it works like Tom's suggestion but the data is distributed into separate tables with a look-up for the TableID. Please note, this was not my design so please don't shoot me!

    There is a nightly process to go through and drop the tables (which is one place where I'm running into the difficulty of having them created by the user login).

    Cat

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    got some examples?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2005
    Posts
    45
    It's all very ugly because any stored procs that access these tables have to use dynamic SQL statements like...

    Code:
    declare @cmd varchar(1000)
    set @cmd = 'select * from tDynBrokerage' + convert(varchar(10), @tabid)
    exec @cmd
    where @tabid is passed in from the application. I don't like it but I'm stuck with it for the time being. That's what I get for going on maternity leave! So... back to the original question. Any thoughts on that?

    Thanks,
    Cat

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why can't these be global temps again?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •