sql-server – Microsoft SQL Server – 谁创建了存储过程？

解决方法

我们的管理数据库中有一个表,可以获取所有活动.它使用DDL触发器,是2005年的新手.这些脚本在您的管理数据库中创建一个表(对我来说是SQL_DBA),在模型数据库上创建一个触发器,在现有数据库上创建触发器.我还在最后创建了一个sp_msforeachDB语句来禁用所有这些语句.

一个警告 – 您的数据库需要处于90的兼容模式(在每个数据库的选项中),否则您可能会开始出错. EXECUTE AS中的帐户也需要访问您的管理表.

USE [SQL_DBA]
GO
/****** Object:  Table [dbo].[DDL_Login_Log]    Script Date: 03/03/2009 17:28:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DDL_Login_Log](
    [DDL_Id] [int] IDENTITY(1,1) NOT NULL,[PostTime] [datetime] NOT NULL,[DB_User] [nvarchar](100) NULL,[DBName] [nvarchar](100) NULL,[Event] [nvarchar](100) NULL,[TSQL] [nvarchar](2000) NULL,[Object] [nvarchar](1000) NULL,CONSTRAINT [PK_DDL_Login_Log] PRIMARY KEY CLUSTERED 
(
    [DDL_Id] ASC,[PostTime] ASC
)WITH (PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE  = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--This creates the trigger on the model database so all new DBs get it
USE [model]
GO
/****** Object:  DdlTrigger [ddl_DB_User]    Script Date: 03/03/2009 17:26:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddl_DB_User] 
ON DATABASE
FOR DDL_DATABASE_SECURITY_EVENTS
AS 

DECLARE @data XML
declare @user nvarchar(100)

SET @data = EVENTDATA()
select @user = convert(nvarchar(100),SYSTEM_USER)

execute as login='domainsqlagent'
INSERT sql_dba.dbo.DDL_Login_Log 
   (PostTime,DB_User,DBName,Event,TSQL,Object) 
   VALUES 
   (@data.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(100)'),@user,db_name(),@data.value('(/EVENT_INSTANCE/EventType)[1]',@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(1000)')
)

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--CREATE TRIGGER IN ALL NON SYSTEM DATABASES

DECLARE @dataname varchar(255),@dataname_header varchar(255),@command VARCHAR(MAX),@usecommand VARCHAR(100)
SET @command = '';
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sys.databases 
WHERE name not in ('master','pubs','tempdb','model','msdb')
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status = 0)
BEGIN

PRINT '----------BEGIN---------'

PRINT 'DATANAME variable: ' + @dataname;

EXEC ('USE ' + @dataname);

PRINT 'CURRENT db: ' + db_name();

SELECT @command = 'CREATE TRIGGER DBA_Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)
SET @data = EVENTDATA()
SET @cmd = @data.value(''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'',''NVARCHAR(1000)'')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'''','''')))
SET @posttime = @data.value(''(/EVENT_INSTANCE/PostTime)[1]'',''DATETIME'')
SET @spid = @data.value(''(/EVENT_INSTANCE/SPID)[1]'',''nvarchar(6)'')
SET @loginname = @data.value(''(/EVENT_INSTANCE/LoginName)[1]'',''NVARCHAR(100)'')
SET @hostname = HOST_NAME()
INSERT INTO [DBA_AUDIT].dbo.AuditLog(Command,PostTime,HostName,LoginName)
 VALUES(@cmd,@posttime,@hostname,@loginname);'

 EXEC (@command);
 FETCH NEXT FROM datanames_cursor INTO @dataname;
PRINT '----------END---------'
END
CLOSE datanames_cursor
DEALLOCATE datanames_cursor

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

----Disable all triggers when things go haywire
sp_msforeachdb @command1='use [?]; IF  EXISTS (SELECT * FROM sys.triggers WHERE name = N''ddl_DB_User'' AND parent_class=0)disable TRIGGER [ddl_DB_User] ON DATABASE'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!