August 27, 2012 at 8:31 am
Hi,
Is there any way to grant DROP permissions to only one table in SQL 2000? I have a SQL login that I want to be able to drop one backup table as part of a script, but I don't want to grant DROP permissions to any other tables in the database. But I also don't want to grant owner permissions.
Thanks in advance for any help or advice.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 27, 2012 at 10:27 am
I don't think you can do so. Per http://msdn.microsoft.com/en-us/library/aa258841(v=SQL.80).aspx:
DROP TABLE permissions default to the table owner, and are not transferable. However, members of the sysadmin fixed server role or the db_owner and db_dlladmin fixed database roles can drop any object by specifying the owner in the DROP TABLE statement.
I have to ask, why would you want a script to be able to drop one table? Usually, I'd either expect the script to use a temporary table, or for the table structure to be maintained permanently.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2012 at 11:36 am
GSquared (8/27/2012)
I don't think you can do so. Per http://msdn.microsoft.com/en-us/library/aa258841(v=SQL.80).aspx:DROP TABLE permissions default to the table owner, and are not transferable. However, members of the sysadmin fixed server role or the db_owner and db_dlladmin fixed database roles can drop any object by specifying the owner in the DROP TABLE statement.
I have to ask, why would you want a script to be able to drop one table? Usually, I'd either expect the script to use a temporary table, or for the table structure to be maintained permanently.
Thanks for the confirmation.
I am working with existing code, so I agree with you that dropping and re-creating the table seems atypical and probably not advised. I will propose a version that just empties and refills the table.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply