Drop Table permissions...

I'm thinking this can't be done, but wanted to try it anyways...
Is there any way a user can grant permission for another user to drop one of
his/her tables? Something like this:
GRANT DROP TABLE ON MYTABLE TO ALL;
We have a table which can be created by a user then left alone for a while
(data import procedure) which needs to be dropped by someone else (on the
next import, the ID numbers need to be set back to one). Thanks in advance!Using NT Authentication, set the permissions to a login based on a user
group and put both people in the group.
Bob Castleman
DBA Poseur
"Lost!" <Lost@.discussions.microsoft.com> wrote in message
news:272B4F05-D539-4F86-966B-EA74B23D7C20@.microsoft.com...
> I'm thinking this can't be done, but wanted to try it anyways...
> Is there any way a user can grant permission for another user to drop one
> of
> his/her tables? Something like this:
> GRANT DROP TABLE ON MYTABLE TO ALL;
> We have a table which can be created by a user then left alone for a while
> (data import procedure) which needs to be dropped by someone else (on the
> next import, the ID numbers need to be set back to one). Thanks in
> advance!|||Why not have each user do this at the END of his/her processing?
Also be aware that truncating the table resets Identity Values, as well as
deleting all data, without the added overhead of recording the delete in the
transaction Log. If your schema will allow the use of Truncate Table, that
might be a better option.
"Lost!" wrote:
> I'm thinking this can't be done, but wanted to try it anyways...
> Is there any way a user can grant permission for another user to drop one
of
> his/her tables? Something like this:
> GRANT DROP TABLE ON MYTABLE TO ALL;
> We have a table which can be created by a user then left alone for a while
> (data import procedure) which needs to be dropped by someone else (on the
> next import, the ID numbers need to be set back to one). Thanks in advance![/color
]|||But then they couldn't recreate the table. Why not let the user create the
table in their own namespace? Or you could have a permanent table, delete
the data from it, insert the new data and subtract the minimum value from
the new ids.
create table dbo.test
(
testId int identity,
value char(1)
)
go
insert into test (value)
values ('a')
insert into test (value)
values ('b')
insert into test (value)
values ('c')
delete dbo.test
insert into test (value)
values ('a')
insert into test (value)
values ('b')
insert into test (value)
values ('c')
declare @.offset int
set @.offset = (select min(testId) - 1 from test)
select testId - @.offset as testId, value
from test
value
-- --
1 a
2 b
3 c
Just add a job to cleanup the value every once in a while :) Tacky, but it
should work for you.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Lost!" <Lost@.discussions.microsoft.com> wrote in message
news:272B4F05-D539-4F86-966B-EA74B23D7C20@.microsoft.com...
> I'm thinking this can't be done, but wanted to try it anyways...
> Is there any way a user can grant permission for another user to drop one
> of
> his/her tables? Something like this:
> GRANT DROP TABLE ON MYTABLE TO ALL;
> We have a table which can be created by a user then left alone for a while
> (data import procedure) which needs to be dropped by someone else (on the
> next import, the ID numbers need to be set back to one). Thanks in
> advance!sql