database change detect

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alli Dem - 26 Jun 2008 11:00 GMT

Hi,

is there any way to detect if user stops sql, changes mdf and ldf file with
a modified one and starts again.

Thanks

Reply to this Message

Andrea Montanari - 26 Jun 2008 15:17 GMT

hi,

> Hi,
>
> is there any way to detect if user stops sql, changes mdf and ldf
> file with a modified one and starts again.

actually not... modifications (both in the metadata and in the data as well)
is a "natural" state of life for data and databases...
and the "case" of your scenario is even wors as SQL Server is not involved
at all, shutting down the service ad overwriting the database's files is
performed at NTFS level (so adeguate permissions in this area is required)
but, again, SQL Server is not involved.. it is later involved when
restarting the service and starting the replaced database.. if something
goes wrong in the NTFS operation, SQL Server could mark the database as
suspect, but this is another story..
on the other hand, SQL Server 2005 introduces DDL triggers,
http://msdn.microsoft.com/en-us/library/ms175941.aspx, but, again, the NTFS
operation is out of control here as well..

Signature

Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://www.hotelsole.com
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply

Reply to this Message

Todd C - 23 Jul 2008 20:26 GMT

Andrea is right. SQL Server connot detect a replaced file on its own any more
than a truck driver can detect that someone empited his trailer and refilled
it with something else while he was in the diner eating a cheeseburger!

The best way to protect against this scenario is to put the database files
in a location that is not readily available to the general user, but IS
available to the account that runs SQL.

HTH

Signature

Todd C

> hi,
> > Hi,
[quoted text clipped - 14 lines]
> http://msdn.microsoft.com/en-us/library/ms175941.aspx, but, again, the NTFS
> operation is out of control here as well..

Reply to this Message