November 18, 2009 at 8:32 pm
I've a table in my database. Many people use that table for insertion and updation.
How do I protect that table from getting dropped ?
November 18, 2009 at 8:55 pm
The main way is to make sure you have applied security using the least privileges method. Basically only give users the rights they need. So none of your users should be in the dbo or db_owner database roles or in the sysadmin server role. If you only give users CRUD right (Create/insert, read, update, delete) to the tables they need to do those functions then no one but you and the DBA should have the rights to drop any objects.
Another method, the one I prefer, is to use Stored Procedures for ALL data access. Now the users only have execute rights on the stored procedures and cannot do anything to the database other than execute those stored procedures to which they have been given execute rights.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 18, 2009 at 9:07 pm
Hi,
Jack Corbett approach is correct, even though to avoid accidentally drops by developers, and some time by the DBA, you create the dummy view WITH SCHEMABINDING and use your table, so that accidentally drop also stopped.
Like
CREATE VIEW dummy_name WITH SCHEMABINDING
As
/*Mentioned, what purpose this view created*/
SELECT * FROM dbo.mytable
November 18, 2009 at 9:13 pm
Jack is exactly right that is the best way.
You can also use DDL triggers to catch and stop it, but you really need to control access.
CEWII
November 22, 2009 at 8:14 am
Why would a developer have the ability to drop a table in a production database? As others have mentioned, proper grants of authority are the appropriate way to control access to data and tables. In general, developers should never, under any circumstances, have any access ot production other than perhaps read. DBA's should never drop tables at will. All actions in a database should be scripted, then thoroughly tested before the DBA runs the script in production. DBA access with any significant authority should be with an ID other than the normal LAN ID. That helps make sure that there aren't any mistakes. DBA access to production databases should be with an ID that is not the same as that used for Acceptance and Development environments. It is a minor pain for the DBA to login to production databases with a separate ID, but in the long run it pays off with a lower potential for disaster.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply