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

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"M A Srinivas" <masri@.vsnl.com> wrote in message
news:f7e90f78.0308160008.176fc1bc@.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|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f3e0eb0$1_4@.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|||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\)" <mooregr@.greenms.com> wrote in message news:<1iq%a.106647$wk4.105134@.twister.nyroc.rr.com>...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:3f3e0eb0$1_4@.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