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