January 16, 2003 at 1:37 am
You could create a COM-object which logs in as dbo and then truncates the table. Then call the COM from a stored procedure (see sp_OACreate, sp_OAMethod...).
This way you can keep dbo as owner of the table and you only give away the possibility to truncate one (or more) table(s). The downside is that you have to install a COM-object on the server and that it feels like going to the moon and back just to buy a newspaper.
I've tried this once in a scenario where a wanted "Autonomous transactions" (supported by Oracle but not SqlServer) and it worked quite well.
January 16, 2003 at 5:53 am
I have used Global Temporary Tables instead of using a table in production DB and then go for TRUNCATE TABLE.
Scope of global temp table can span on multiple objects and can be used using double hash sign. (create table ##mytemptable)
January 16, 2003 at 5:56 am
Thats not a bad idea, a variation of the proxy approach of putting the request in a table so that sql agent can do the work. Potentially less secure since the object would have to have embedded password, could still obfuscate it. If you choose either approach you should exercise care that they can't do more than you intend - like truncating any old table!
Andy
January 16, 2003 at 6:37 am
The problem that you have with using a Global Temp table (##) is that you get back into the permission problem that you are trying to solve in the first place. By using a standard temp table (#), you allow the truncate to take place, because your user created the table, and is therefore the owner. With a global Temp, that is only true for the creator of the table.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply