SQL Server Performance Forum – Threads Archive
Drop Temp Tables – Permission error
On one of the systems I support, there are a bunch of temp tables being created. I’d like to write a stored procedure to drop those temp tables nightly since some often get left behind for whatever reason. The problem I run into is that I receive a permissions error whenever I try to use the "drop table" command. The tables are not created with a normal dbo but instead have domain users as the owners. So you’d see tables like DomainNameUsername.TempTableName. This seems to cause problems and I get an error that says "Cannot drop the table ‘DomainNameUsername.TempTableName’, because it does not exist or you do not have permission." First thing I tried was to put everything in brackets like [DomainNameUsername.TempTableName] but that didn’t work either. so I tried it without the DomainNameUsername part and still got the error. Also tried just the username.temptablename and got the error. Has anyone run into this kind of thing before? I’m not really sure what to do.true temporary tables (ie, the ones created by CREATE TABLE #MyTemptable) are dropped automatically as soon as the connection closes. Are yours just normal tables, being used for a temporary purpose ?
Even though your table name is Temp your table is not a temporary table. Use # as suffix to table name. it will be automatticaly droped after the connection closes —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com
The application creates the tables. They are not actually true temporary tables, but they are used in a similar fashion. They are supposed to be dropped at the end of processing but that doesn’t always happen. Unfortunately using true temporary tables as an alternative is not an option in this case.
Do your tables appear in the output from this query, and if so what are the owners listed as ? select TABLE_SCHEMA, TABLE_NAME from information_schema.tables
Yes the tables appear in that output. Unlike the rest of the other tables, the owner is not dbo. The owner is the Windows Login of the table creator for those "temporary" tables.
It’s not a permission error, a syntax error. You must specify full qualified name of the table. You can use Enterprise Manager to generate right drop script. You should see temp tables you want to drop in Enterprise Manager. In Enterprise Manager, right click on the table. Click All Tasks -> Generate Sql Script… Generate Sql Scripts window must be opened. In Formatting Tab, clear Generate the CREATE… Generate the DROP… has already been checked. In General Tab, click Preview button. That’s the right drop script.
]]>