Contents

1 1 - DDL 觜蠍 碁Μ蟇
2 2 - 一危磯伎 譴 碁Μ蟇
3 3 - 覯 譴 碁Μ蟇
4 谿瑚


1 1 - DDL 觜蠍 碁Μ蟇 #

--http://databaser.net
--谿瑚: http://www.sqlservercentral.com/articles/SQLServerCentral.com/sqlserver2005logontriggers/2366/
USE AdeventureWorks
GO
 
--觜 觚襦貉 燕
ALTER DATABASE AdeventureWorks SET ENABLE_BROKER
GO
 
--DDL碁Μ蟇一  一危郁 願幻 企
CREATE TABLE dbo.T_DDLLog
(
	LoggingID int IDENTITY(1,1)
,	EventTime DATETIME
,	EventType NVARCHAR(255)
,	LoginName NVARCHAR(255)
,	HostName NVARCHAR(255)
,	NTUserName NVARCHAR(255)
,	NTDomainName NVARCHAR(255)
,	Success INT
,	FullLog XML
)
GO

--Try~Catch
CREATE TABLE dbo.T_SP_ErrorLog(
	ErrorNumber int NULL
,	ErrorSeverity int NULL
,	ErrorState int NULL
,	ErrorProcedure nvarchar(126) NULL
,	ErrorLine int NULL
,	ErrorMessage nvarchar(2048) NULL
,	ErrorDatetime datetime NULL
,	Parameters nvarchar(max) NULL
) 
 
CREATE CLUSTERED INDEX CIX_EventTime
ON dbo.T_DDLLog(EventTime DESC);
 
CREATE INDEX NIX_LoggingID
ON dbo.T_DDLLog(LoggingID);
GO
 
--觜るゼ  れ(, 觜)
--DROP QUEUE LoggingQueue
CREATE QUEUE LoggingQueue
GO
 
--DROP SERVICE LoggingService
CREATE SERVICE LoggingService
ON QUEUE LoggingQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
 
--企欧 誤 
--DROP EVENT NOTIFICATION Logging_Event_Notification ON SERVER
--ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/wmi9/html/e2916cd7-a3ed-41e6-97b4-2ee060754cbe.htm
CREATE EVENT NOTIFICATION Logging_Event_Notification
ON SERVER
FOR DDL_EVENTS
--,	TRC_CLR
--,	TRC_DEPRECATION
--,	TRC_ERRORS_AND_WARNINGS
--,	TRC_FULL_TEXT
--,	TRC_LOCKS
--,	TRC_OBJECTS
--,	TRC_OLEDB
--,	TRC_PERFORMANCE
--,	TRC_QUERY_NOTIFICATIONS
--,	TRC_SECURITY_AUDIT
--,	AUDIT_LOGIN
--,	AUDIT_LOGOUT
--,	AUDIT_LOGIN_FAILED
--,	AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
--,	TRC_SERVER
--,	TRC_STORED_PROCEDURES
--,	TRC_TSQL
--,	TRC_USER_CONFIGURABLE
TO SERVICE 'LoggingService', 'current database'
GO
--select top 100 * from sys.dm_exec_connections襯 伎覃 企殊伎誤IP(Client IP    蟆企.)
 
--觜 觚襦貉れ  燕 襦
CREATE PROCEDURE Up_DDL_Log_Create
AS
SET XACT_ABORT ON 
SET NOCOUNT ON 
 
DECLARE     @message_body XML,
            @message_type_name NVARCHAR(256),
            @dialog UNIQUEIDENTIFIER ;
 
--Endless loop
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;
 
    -- Receive the next available message
	WAITFOR 
	(
		RECEIVE TOP(1)
			@message_type_name=message_type_name
		,	@message_body=message_body
		,	@dialog = conversation_handle
		FROM LoggingQueue
	), TIMEOUT 2000
 
	--Rollback and exit if no messages were found
	IF (@@ROWCOUNT = 0)
	BEGIN
		ROLLBACK TRANSACTION ;
		BREAK ;
	END;
 
   --End conversation of end dialog message
    IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    BEGIN
		PRINT 'End Dialog received for dialog # ' + cast(@dialog AS nvarchar(40)) ;
		END CONVERSATION @dialog ;
    END ELSE IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
	BEGIN
		PRINT 'End Dialog received for dialog # ' + cast(@dialog AS nvarchar(40)) ;
		END CONVERSATION @dialog ;
	END;
	ELSE
    BEGIN
		BEGIN TRY
			INSERT INTO dbo.T_DDLLog 
			(
				EventTime
			,	EventType
			,	LoginName
			,	HostName
			,	NTUserName
			,	NTDomainName
			,	Success
			,	FullLog	
			)
			SELECT
				CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME)
			,	CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100))
			,	CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER)
			,	@message_body
		END TRY
 
		BEGIN CATCH
			IF		(XACT_STATE())	= -1 ROLLBACK; 
			ELSE IF (XACT_STATE())	=  1 COMMIT;
 
			INSERT dbo.T_SP_ErrorLog
			(
				ErrorNumber
			,	ErrorSeverity
			,	ErrorState
			,	ErrorProcedure
			,	ErrorLine
			,	ErrorMessage
			,	ErrorDatetime
			,	Parameters
			)
			SELECT
				ERROR_NUMBER()
			,	ERROR_SEVERITY()
			,	ERROR_STATE()
			,	ERROR_PROCEDURE()
			,	ERROR_LINE()
			,	ERROR_MESSAGE()
			,	GETDATE()
			,	CAST(@message_body AS nvarchar(max));	-- nvarchar(max) (朱誤磯ゼ k.)
 
			RETURN ERROR_NUMBER();
		END CATCH;
	END;
COMMIT TRANSACTION
END
GO
 
-- 覲蟆
--DROP QUEUE LoggingQueue
ALTER QUEUE LoggingQueue
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = dbo.Up_DDL_Log_Create ,
MAX_QUEUE_READERS = 2, EXECUTE AS SELF ), STATUS = ON
GO
 
--ALTER QUEUE dbo.LoggingQueue WITH STATUS = ON 
--ALTER QUEUE dbo.LoggingQueue WITH STATUS = OFF
 
/*
--Test
drop table test
CREATE TABLE test(id int)
GO
 
SELECT 
	EventTime
,	FullLog.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(255)') LoginName
,	FullLog.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(255)') EventType
,	FullLog.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') CommandText
FROM dbo.T_DDLLog
WHERE LoggingID = 2
*/
 
CREATE PROC dbo.Up_DDL_Log_Read
	@BeginDT datetime
,	@EndDT datetime
,	@LoggingID int = 0
,	@EventType nvarchar(255) = ''
,	@LoginName nvarchar(255) = ''
,	@HostName nvarchar(255) = ''
,	@NTUserName nvarchar(255) = ''
,	@NTDomainName nvarchar(1000) = ''
,	@SQLViewYN bit = 0
AS
SELECT 
	LoggingID
,	EventTime
,	EventType
,	LoginName
,	HostName
,	NTUserName
,	NTDomainName
,	Success
,	FullLog
--,	CASE WHEN @SQLViewYN = 0 THEN '' ELSE FullLog.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') END SQLSource
FROM dbo.T_DDLLog
WHERE 1=1
AND EventTime BETWEEN @BeginDT AND @EndDT
GO
 
/*
 
SELECT * FROM dbo.T_DDLLog
where EventType like '%LOGIN%'
 
select top 100 * from LoggingQueue
 
 
truncate table T_DDLLog
SELECT top 100 * FROM LoggingQueue
*/

2 2 - 一危磯伎 譴 碁Μ蟇 #

use TestDB
go

-- 碁Μ蟇 蟯 一危磯ゼ ロ 企
CREATE TABLE DDL_Log (
	ID      int IDENTITY(1, 1)
		PRIMARY KEY
,	TrData	xml
)
GO

-- ろ語朱 DROP, ALTER襯   企
CREATE TABLE TEST01 ( id int )
GO

-- DDL 碁Μ蟇
CREATE TRIGGER tr_Test1
	ON DATABASE 
	FOR DROP_TABLE, ALTER_TABLE 
AS
	DECLARE @trData XML
	   -- EVENTDATA()  語 蟆郁骸襯 XML襦 ロ蟇磯, XQuery襦 蟆
	SET @trData = EVENTDATA()

	   -- DDL 碁Μ蟇磯 ROLLBACK 豌襴螳 螳ロ
	ROLLBACK

	   -- Log 蠍磯
	SET NOCOUNT ON
	INSERT INTO DDL_Log (TrData) VALUES (@trData)
GO

/*
   DDL 碁Μ蟇 語 ろ
*/

-- 1) DROP TABLE 企欧碁 讌 
DROP TABLE TEST01
SELECT * FROM DDL_Log
GO

-- 2) ALTER TABLE 企欧碁 TRY ... CATCH 伎  覺
BEGIN TRY
	ALTER TABLE TEST01 ADD b int
END TRY
BEGIN CATCH
	SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
SELECT * FROM DDL_Log
* SQL Server 2008 蟲 覦 朱 (襷: 蟾壱)

3 3 - 覯 譴 碁Μ蟇 #

-- SQL 襦蠏語 煙 讌 覈詩襦  碁Μ蟇 .

-- CREATE LOGIN   DDL 碁Μ蟇
CREATE TRIGGER tr_Test2
ON ALL SERVER
FOR CREATE_LOGIN
AS
SET NOCOUNT ON

   -- XQuery  value() 覃襯 , 轟  螳 蟆螻 觜蟲
IF (EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(100)') 
			= 'SQL Login')
BEGIN
ROLLBACK
PRINT 'SQL 譴  燕  給!'
END
ELSE
BEGIN
PRINT 'Windows   螳ロ!'
END
GO

-- SQL 譴   ろ => ろ
CREATE LOGIN SqlUser WITH PASSWORD = 'p@ssw0rd'
GO

-- Windows 譴   ろ
CREATE LOGIN [SQL2005PC\Guest] FROM WINDOWS
GO
DROP LOGIN [SQL2005PC\Guest]
GO
* SQL Server 2008 蟲 覦 朱 (襷: 蟾壱)

4 谿瑚 #

  • 襦蠏語 碁Μ蟇 覈 蟇語る, [http](http://www.mssqltips.com/sqlservertip/1631/connecting-to-sql-server-with-a-bad-logon-trigger/)襯 谿瑚