ddl trigger on database for connect 1

[northrd]

Hello ALL
I am trying to stop the unauthorised attachment of a database. Yes, I have asked this before and yes ... I am using encryption.
But I have become intrigued with tee DDL triggers. In principle I want to detach on attach, as I have scripted below


Create Trigger [All_Events]
On Database
For DDL_DATABASE_LEVEL_EVENTS
AS

Declare @Test as nvarchar(10);


if exists (select * from master.INFORMATION_SCHEMA.columns where table_name = 'Authentication' and column_name = 'Authentication')
begin
select @Test=Authentication from master.dbo.authentication
if @Test <> 'Bollocks'
begin
sp_detach_db 'test'
end
end

else
begin
sp_detach_db 'test'
end

But it just doesn't work.
Any ideas?

 

[SQLWilts]

Hi,
Are there any messages in the error log? Is anything using the DB when you are trying to detach it?

I havent tried this myself yet - I'll give it a go and let you know how I fare...

 

[SQLWilts]

OK, so far I have this aleration:

Create Trigger [All_Events]
On Database
For DDL_DATABASE_LEVEL_EVENTS 
AS

    Declare @Test as nvarchar(10);

        
    if exists (select * from master.INFORMATION_SCHEMA.columns where table_name = 'Authentication' and column_name = 'Authentication')
        begin
        select @Test=Authentication from master.dbo.authentication
        if isnull(@Test, 'OK') <> 'Bollocks'
            begin
            exec sp_detach_db 'test'
            end
        end
        
     else
        begin
        exec sp_detach_db 'test'
        end

this then creates the trigger, and it fires when someone tries to create a table (for example) in the database.
But, you will then get this error:

Msg 15002, Level 16, State 1, Procedure sp_detach_db, Line 41
The procedure 'sys.sp_detach_db' cannot be executed within a transaction.

I am looking for a way around this...

 

[Jamfool]

My version:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [DDL_Server_CreateDB_Trigger] 
ON ALL SERVER
FOR 
CREATE_DATABASE
AS
SET NOCOUNT ON  

BEGIN TRY
	PRINT 'Stop it! I am watching you.'
	ROLLBACK;
END TRY
BEGIN CATCH
	PRINT 'DDL_Server_CreateDB_Trigger- '
		   + char(13) + char(10) + 'ErrorNumber:' + COALESCE(CAST(ERROR_NUMBER() AS VARCHAR(4000)),'')
		   + char(13) + char(10) + 'ErrorMessage:' + COALESCE(CAST(ERROR_MESSAGE() AS VARCHAR(4000)),'')
END CATCH



GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [DDL_Server_CreateDB_Trigger] ON ALL SERVER
GO

 

[SQLWilts]

Hi Jamfool,
I can see where you're thinking, but (and this is without testing it...) if you put a rollback within a trigger doesn't the trigger throw an error about the transaction finishing before the trigger has or something?
Sorry to be vague, but something about it is nagging me in the back of my mind, and I don't have a lot of time to play with it...

 

[Jamfool]

It works fine for me.

http://msdn.microsoft.com/en-us/library/ms186406.aspx

ms example:

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
   PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
   ROLLBACK
;

 

[SQLWilts]

Then I stand corrected, and bow to your greater knowledge.

I know I will be squirrelling this code away for use later - we never stop learning, do we?