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,
CatI never write application code that creates permanent database objects on the fly. it's bad news more often than not.|||Hi Thras,
They're not actually permanent, but they need to stick around for longer than the stored proc that's creating them.
Cat|||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!|||????
What happens when the same sproc executes at the same time?|||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.|||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?|||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.|||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?|||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!
Was the boss short?
With pointy hair!|||Was the boss short?
no. he was rather tall.|||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! :shocked:
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|||got some examples?|||It's all very ugly because any stored procs that access these tables have to use dynamic SQL statements like...
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|||Why can't these be global temps again?
