SQL Server Performance

how to protect the table and watching for it

Discussion in 'SQL Server 2005 General DBA Questions' started by oyousef, Dec 6, 2009.

  1. oyousef New Member

    We have a table in the SQL database name Alies This table note of each period of the disappearance of all its contents We do not know what is the reason??! For your information the table is not deleted, but I think it is the work of Truncate Table As is well known that this is his job to delete records in the table Is there a way to protect this table and monitor changes that occur upon ???!! And prevent any person from the work to Truncate this table
    Could you please help me writing the command for restricting the table to be truncated for all users
  2. oyousef New Member

    Briefly the problem that the data in a table alias that someone deleted by using a command Truncate

    All I want to block the use this command or knowledge of this work is

    Has been the experience of profiler

    And has not been defined, use the Truncate

    ***Truncate Table will not fire a DDL trigger.

    Can you help me in learning how to work FOREIGN KEY for the table alias

    I thank you for helping me
  3. arunyadav Member

    Welcome to the Forums!
    [From MSDN] TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
    You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEYconstraint; instead, use DELETE statement without a WHERE clause.Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
    TRUNCATE TABLE may not be used on tables participating in an indexed view.
    I don't think there is any other way but the ones mentioned above.
  4. oyousef New Member

    Thank you very much for the assistance
  5. satya Moderator

    Welcome to the forums.
    Are you sure that any other job or process is triggering the deletion of these rows from that table?
    Also how many users do have SYSADMIN privileges on the system?

Share This Page