How to Track Deletion of any Database
-
Hi
I tried putting a trigger on Master..Sysdatabases
But it doesnt allow me to do so.
Is there any way to know when a particular database is deleted with the name of the Computer(Host_name())
Can i restrict anyone from deleting Database.
(All the users in my office use 'sa' login )
Thanks
awaiting reply
Mayuresh
MayurArt -
This was removed by the editor as SPAM
-
I can't answer your query directly, but I would ask why the users need SA privs?
I would determine the exact security level that the users need to perform their job (profiler could help with this) and reduce their security privs accordingly. If they are simply accessing the tables in a database, could you reduce them to db_datareader, db_datawriter and grant execute to any stored procedures. You would not have to worry about them deleting the database then. Or even dropping tables (although they could delete everything in a table...).
Perhaps creating a role with all necessary privs in it, grant the role to the users and then remove their SA rights. Do this with a "friendly" user first to ensure it works.
regards
Neil.
-
As Neil said - grant users the permissions to do their jobs, not sysadmin memberhips.
However, you can prevent casual database deletions with a trigger:
CREATE TRIGGER STOP_DB_DROP
ON ALL SERVER
FOR DROP_DATABASE
AS
PRINT 'You are not allowed to DROP databases'
ROLLBACK;
If you take this route they can still delete the database by disabling this trigger. Remember that with permissions users start with nothing, and you grant permissions bit by bit until they're able to work and can't compromise the data or the system. Never grant everything and try to work backwards.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply