Skip to Content

Preventing accidental 'drop table' by 'sa'?

Apr 06, 2017 at 08:14 AM


avatar image


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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Avinash Kothare 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.


10 |10000 characters needed characters left characters exceeded