database change detect   

Hi,

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

Thanks

date: Thu, 26 Jun 2008 13:00:59 +0300   author:   Alli Dem

Re: database change detect   

hi,
Alli Dem wrote:
> 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

date: Thu, 26 Jun 2008 16:17:31 +0200   author:   Andrea Montanari

Re: database change detect   

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

"Andrea Montanari" wrote:

> hi,
> Alli Dem wrote:
> > 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 
> 
> 
>

date: Wed, 23 Jul 2008 12:26:01 -0700   author:   Todd C