database change detect

[Alli Dem]

Hi,

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

Thanks

[Andrea Montanari]

hi,

Post by Alli Dem
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..

[Todd C]

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
--
Todd C

Post by Andrea Montanari
hi,

Post by Alli Dem
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..
--
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