Skip to Content

Preventing accidental 'drop table' by 'sa'?


SAP ASE 16.0 SP02

One of my customer accidentally dropped user tables in master including 'spt_values' and suspended ASE operation for a few hours.
They are considering preventive measure against accidental object removal.

I am trying granular permissions but not successful yet.
Any idea?

Kazuo Otani

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Apr 06, 2017 at 02:37 PM

    To err is human !

    With "sa" privileges there does not seem to be any protection for accidental removal of any objects.

    (As "sa" every time I use "kill" for a SPID on a user's request, I double check with him/her about other attributes for that connection)

    For destructive commands : Best practice of begin transaction, SQL commands, followed by commit or rollback helps.

    Many a time I have been saved by my favorite following SQL snippet

    select @@servername, db_name(), getdate()


    telling me what server and database I am connected to.

    If you use sqsh, then configure it for 'nix style prompt to tell/remind about server and database name for the connection.

    But with all the precautions, there is always a potential for a genuine mistake by "sa". So you should be ready to recover master or any other database from the latest backup copy.


    Add comment
    10|10000 characters needed characters exceeded