Is It possible to disable drop database for sa or sysadmin also ?

[M A Srinivas]

Hello,

I would like to know is it possible to disable drop database for sa
or sysadmin. If saor sysadmin needs to drop the database , he/she may
have to change status in one of the system tables (sysdatabases ?) and
then only database can be dropped . This is to avoid dropping the
database by mistake by sa.

In books online under drop database
System databases (msdb, master, model, tempdb) cannot be dropped

I would like to know how this is implemented for system databases ?

Thanks

M A Srinivas

[Simon Hayes]

"M A Srinivas" <ma***@vsnl.com> wrote in message
news:f7**************************@posting.google.c om...

Hello,

I would like to know is it possible to disable drop database for sa
or sysadmin. If saor sysadmin needs to drop the database , he/she may
have to change status in one of the system tables (sysdatabases ?) and
then only database can be dropped . This is to avoid dropping the
database by mistake by sa.

In books online under drop database
System databases (msdb, master, model, tempdb) cannot be dropped

I would like to know how this is implemented for system databases ?

Thanks

M A Srinivas

Basically, no, it's not possible. A sysadmin can do anything in SQL Server,
so the best approach is to limit sysadmin membership to DBAs only. As with
any system administrator, you have to trust them to do the job properly,
based on their training, experience etc.

I don't know the mechanism that prevents system databases being dropped (I'd
guess that there may be a check in the MSSQL engine that won't drop dbid <=
4 or something similar), but there is no flag or other mechanism to prevent
someone with DROP DATABASE authority dropping user databases.

Simon

[Greg D. Moore \(Strider\)]

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3f**********@news.bluewin.ch...

Basically, no, it's not possible. A sysadmin can do anything in SQL Server, so the best approach is to limit sysadmin membership to DBAs only. As with
any system administrator, you have to trust them to do the job properly,
based on their training, experience etc.

I don't know the mechanism that prevents system databases being dropped (I'd guess that there may be a check in the MSSQL engine that won't drop dbid <= 4 or something similar), but there is no flag or other mechanism to prevent someone with DROP DATABASE authority dropping user databases.

I'd also question the utility of it.

If you're afraid of an sa purposely trying to do damage, a drop database is
the least of your worries (note normally you can't do it in any case if the
DB is in use).

If you're afraid they might do it by accident, I'd have to ask what are they
doing that the likelihood is all that high?

Simon

[M A Srinivas]

I know that database can not be dropped while in use.
Sometimes while dropping a database through EM, sa may select the
wrong database to drop by mistake.
Restoring of database from back-up takes time (depends on the size)

M A Srinivas
"Greg D. Moore \(Strider\)" <mo*****@greenms.com> wrote in message news:<1i*********************@twister.nyroc.rr.com >...

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3f**********@news.bluewin.ch...

Basically, no, it's not possible. A sysadmin can do anything in SQL

Server,

so the best approach is to limit sysadmin membership to DBAs only. As with
any system administrator, you have to trust them to do the job properly,
based on their training, experience etc.

I don't know the mechanism that prevents system databases being dropped

(I'd

guess that there may be a check in the MSSQL engine that won't drop dbid

<=

4 or something similar), but there is no flag or other mechanism to

prevent

someone with DROP DATABASE authority dropping user databases.

I'd also question the utility of it.

If you're afraid of an sa purposely trying to do damage, a drop database is
the least of your worries (note normally you can't do it in any case if the
DB is in use).

If you're afraid they might do it by accident, I'd have to ask what are they
doing that the likelihood is all that high?

Simon