Preventing accidental 'drop table' by 'sa'?

[kazuootani]

Hello,

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?

Regards,
Kazuo Otani

[former_member89972]

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()

go

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.

Avinash