Search code examples
sqlsql-server-2008crud

prevent updates, deletes and truncates on database table

I am in the process of creating a sql server 2008 database table for auditing users actions.

Is it possible to create a database table which can only inserted in to - no truncates, deletes or updates allowed on the data in the table. One option I know of is to use a different user with limited rights, but this isnt option for me. So looking at other options?

Solution

  • You need to create a TRIGGER that fires on UPDATE and DELETE and throws an error:

    CREATE TRIGGER user_action_update on UserActions FOR UPDATE, DELETE AS
    BEGIN
        RAISERROR ('Cannot modify or delete user actions', 16, 1)
        ROLLBACK TRAN
        RETURN
    END
GO

    Source: http://msdn.microsoft.com/en-us/magazine/cc164047.aspx