question

bbunn avatar image
bbunn asked

Can I prevent users from deleting tables they've created?

Sandbox DB with multiple developers and I'm being asked to prevent all table drops. I've denied DROP to all users (as SA) but if I log in as myself, I can still drop a table I created. Is this because I'm the DBOwner?

securitytablesdropsecurity-modelsecurity model
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

You can't deny DROP explicitly, you'd have to deny ALTER, which can cause other issues as it will prevent you making any changes to those tables.

One way around this is to create a database trigger on DROP that does a rollback.

10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.