导读:1建站知识下面就是一个解决上述问题的方案，我们通过创建一个表DatabaseLog和DDL触发器来解决问题，首先在msdb数据库里面新建网站优化seo培训网站建设教程。

前言： 有时候，一个数据库有多个帐号，包括数据库管理员，开发人员，运维支撑人员等，可能有很多帐号都有比较大的权限，例如DDL操作权限(创建，修改，删除存储过程，创建，修改，删除表等），账户多了，管理起来就会相当麻烦，容易产生混乱，如果数据库管理员不监控数据库架构变更的话，就不知道谁对数据库架构做了啥改动（此处改动仅仅只DDL操作），尤其有时候，有些开发人员可能不按规章制度办事，绕过或忘了通知发布人员或DBA，直接去生产机做一些DDL操作，那么我们就需要对数据库架构某些更改的事件进行监控，如果能够监控并留下证据，这样既可以让DBA或相关管理人员知晓这些变更，有效管理数据库，也可以避免出现问题，出现扯皮现象，最后DBA成了背黑锅的。 下面就是一个解决上述问题的方案，我们通过创建一个表DatabaseLog和DDL触发器来解决问题，首先在msdb数据库里面新建一个表DatabaseLog，用来保存DDL触发器获取的信息。其中DDL触发器主要通过EVENTDATA()函数返回有关服务器或数据库事件的信息。

复制代码 代码如下:

USE msdb;GOCREATE TABLE [dbo].[DatabaseLog](    [DatabaseLogID]   [int]    IDENTITY(1,1) NOT NULL,    [PostTime]        [datetime] NOT NULL,    [DatabaseUser]    [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    [LoginName]       [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [ClientHost]      [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [Event]           [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    [Schema]          [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,  seo网站排名优化软件;  [Object]          [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [TSQL]            [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    [XmlEvent]        [xml] NOT NULL,CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED(    [DatabaseLogID] ASC  )WITH (PAD_INDEX= OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Primary key for DatabaseLog records.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'DatabaseLogID'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The date and time the DDL change occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Post网站seo优化培训Time'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The user who implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'DatabaseUser'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The login which implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'LoginName'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The client machine on which implemented t网站优化seo培训he DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'ClientHost'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The type of DDL statement that was executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Event'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The schema to which the changed object belongs.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Schema'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The object that was changed by the DDL statment.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Object'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The exact Transact-SQL statement that was executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'TSQL'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The raw XML data generated by database trigger.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'XmlEvent'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Audit table tracking all DDL changes made to the database. Data is captured by the database trigger ddlDatabaseTriggerLog.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog'GOEXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Primary key (nonclustered) constraint' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'CONSTRAINT',@level2name=N'PK_DatabaseLog_DatabaseLogID'GO

关键词标签: 操作 SQL 存储过程