USE [stat]

CREATE TABLE [dbo].[ddl](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [EventType] [varchar](1000) NULL,
    [PostTime] [datetime] NULL,
    [SPID] [int] NULL,
    [ServerName] [varchar](1000) NULL,
    [LoginName] [varchar](1000) NULL,
    [UserName] [varchar](1000) NULL,
    [Host] [varchar](1000) NULL,
    [Program] [varchar](1000) NULL,
    [DatabaseName] [varchar](1000) NULL,
    [SchemaName] [varchar](1000) NULL,
    [ObjectName] [varchar](1000) NULL,
    [ObjectType] [varchar](1000) NULL,
    [x] [xml] NULL,
    [CommandText] [nvarchar](max) NULL,
 CONSTRAINT [PK_ddl] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

USE [master]
GO

CREATE trigger [ddl_stat] on all server for
    CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
    CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
    CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
    CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
    CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
    CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
    CREATE_USER, ALTER_USER, DROP_USER,
    CREATE_ROLE, ALTER_ROLE, DROP_ROLE, ADD_ROLE_MEMBER, DROP_ROLE_MEMBER,
    RENAME
as begin

    if suser_name() = 'KUPIVIP\sqlagt' return

    set nocount on
    declare @x xml = eventdata()

    if is_member('sv_dev')=1 and @x.value('(/EVENT_INSTANCE/SchemaName)[1]',  'varchar(1000)') <> 'sv' begin
        print 'Вам нельзя менять объекты базы'
        rollback
    end

    insert stat..ddl(EventType,PostTime,SPID,ServerName,LoginName,UserName,Host,Program,DatabaseName,SchemaName,ObjectName,ObjectType,CommandText,x)
    select
    @x.value('(/EVENT_INSTANCE/EventType)[1]',   'varchar(1000)'),
    getdate(),
    @@spid,
    @@servername,
    suser_name(),
    user_name(),
    host_name(),
    app_name(),
    @x.value('(/EVENT_INSTANCE/DatabaseName)[1]',  'varchar(1000)'),
    @x.value('(/EVENT_INSTANCE/SchemaName)[1]',  'varchar(1000)'),
    @x.value('(/EVENT_INSTANCE/ObjectName)[1]',  'varchar(1000)'),
    @x.value('(/EVENT_INSTANCE/ObjectType)[1]',  'varchar(1000)'),
    @x.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',  'nvarchar(max)'),
    @x
end
GO

ENABLE TRIGGER [ddl_stat] ON ALL SERVER
GO