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 3 3399
"M A Srinivas" <ma***@vsnl.com > wrote in message
news:f7******** *************** ***@posting.goo gle.com... 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" <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
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*****@greenm s.com> wrote in message news:<1i******* **************@ twister.nyroc.r r.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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: MailSurfer |
last post by:
Hi all,
OS. linux 2.4.22
MYsql : 4.0.18-standard
I created a database and populate 2 big InnoDB tables, the total size
is > 3G. After all testing, I droped the database .
But, the harddisk space is not release to the filesystem, Why, How
could I recover that ?
|
by: Blnukem |
last post by:
Hi All
I'm new to javascript and was wondering if someone can help me with
this I want to disable my second and third drop-box if the first one
is selected to "Closed" I think I'm close but just cant get it to work
here is my code:
<html>
<head>
|
by: tgru |
last post by:
I am trying to drop a database, but keep getting the following error.
"cannot drop database 'blah' because it is currently being used for
replication".
This db is not currently being replicated, but once was. It is the
subscriber side of an old replication pair.
Can anyone tell what I have to do to make this go away?
|
by: aixunix |
last post by:
Hi Experts,
I am working on DB2 UDB on AIX and I get following OSERR message in
db2diag.log when I drop database.
Well, the database can be dropped successfully, but the error message
is troublesome as system owner will not accept any error message at
db2diag.log.
2005-11-18-18.18.38.182670+480 E2336A414 LEVEL: Error (OS)
|
by: Risen |
last post by:
Hi,all,
I want to execute SQL command " DROP DATABASE mydb" and "Restore DATABASE
....." in vb.net 2003. But it always shows error. If any body can tell me how
to execute sql command as above? Thanks a lot.
Best regard.
Risen
| |
by: Jeff Beem |
last post by:
In ASP.net 2.0 (it may be possible in previous versions also, I don't know)
you can drag a database table from the server explorer to the webform and it
will simply run. At least, that's what happens when my database server is
SQL Server 2000. However, if my database server is SQL 2005 it doesn't run
at all and throws an exception stating that the <tablename> is an invalid
object.
In code I connect to the databases the same way, what's...
|
by: kestrel |
last post by:
im making a drop down list, and i want to disable only certain options
how can i do this?
|
by: Michael C |
last post by:
Hi All,
I'm trying to drop an sqlserver database from c# but can't because it is
claiming it is in use. As I don't have a connection to it it must be
connection pooling that is causing the problem. I can test this by disabling
connection pooling and the database can be dropped. Is there any way to
clear out the connection pool? I can't disable connection pooling as my app
uses it. Some sample code is below
Thanks
|
by: pedalpete |
last post by:
I think I somehow REALLY screwed up my database. I get no return making queries, REPAIR said it was all ok, I mysqldump was hanging, etc. etc, lots of nasty stuff.
So, I decided to drop the database and rebuild it from my back-up.
but even the drop is hanging with the command
Drop Database zifi
I was searching for a more powerful way to get rid of it, but can't find anything.
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |