By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,759 Members | 959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,759 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"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
Jul 20 '05 #2

P: n/a

"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

Jul 20 '05 #3

P: n/a
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

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.