SHARE
TWEET

Untitled

bisonn Nov 20th, 2019 184 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [stat]
  2.  
  3. CREATE TABLE [dbo].[ddl](
  4.     [id] [int] IDENTITY(1,1) NOT NULL,
  5.     [EventType] [varchar](1000) NULL,
  6.     [PostTime] [datetime] NULL,
  7.     [SPID] [int] NULL,
  8.     [ServerName] [varchar](1000) NULL,
  9.     [LoginName] [varchar](1000) NULL,
  10.     [UserName] [varchar](1000) NULL,
  11.     [Host] [varchar](1000) NULL,
  12.     [Program] [varchar](1000) NULL,
  13.     [DatabaseName] [varchar](1000) NULL,
  14.     [SchemaName] [varchar](1000) NULL,
  15.     [ObjectName] [varchar](1000) NULL,
  16.     [ObjectType] [varchar](1000) NULL,
  17.     [x] [xml] NULL,
  18.     [CommandText] [nvarchar](max) NULL,
  19.  CONSTRAINT [PK_ddl] PRIMARY KEY CLUSTERED
  20. (
  21.     [id] ASC
  22. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  23. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  24. GO
  25.  
  26. USE [master]
  27. GO
  28.  
  29. CREATE trigger [ddl_stat] on all server for
  30.     CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
  31.     CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
  32.     CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
  33.     CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
  34.     CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
  35.     CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
  36.     CREATE_USER, ALTER_USER, DROP_USER,
  37.     CREATE_ROLE, ALTER_ROLE, DROP_ROLE, ADD_ROLE_MEMBER, DROP_ROLE_MEMBER,
  38.     RENAME
  39. as begin
  40.  
  41.     if suser_name() = 'KUPIVIP\sqlagt' return
  42.  
  43.     set nocount on
  44.     declare @x xml = eventdata()
  45.  
  46.     if is_member('sv_dev')=1 and @x.value('(/EVENT_INSTANCE/SchemaName)[1]',  'varchar(1000)') <> 'sv' begin
  47.         print 'Вам нельзя менять объекты базы'
  48.         rollback
  49.     end
  50.  
  51.     insert stat..ddl(EventType,PostTime,SPID,ServerName,LoginName,UserName,Host,Program,DatabaseName,SchemaName,ObjectName,ObjectType,CommandText,x)
  52.     select
  53.     @x.value('(/EVENT_INSTANCE/EventType)[1]',   'varchar(1000)'),
  54.     getdate(),
  55.     @@spid,
  56.     @@servername,
  57.     suser_name(),
  58.     user_name(),
  59.     host_name(),
  60.     app_name(),
  61.     @x.value('(/EVENT_INSTANCE/DatabaseName)[1]',  'varchar(1000)'),
  62.     @x.value('(/EVENT_INSTANCE/SchemaName)[1]',  'varchar(1000)'),
  63.     @x.value('(/EVENT_INSTANCE/ObjectName)[1]',  'varchar(1000)'),
  64.     @x.value('(/EVENT_INSTANCE/ObjectType)[1]',  'varchar(1000)'),
  65.     @x.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',  'nvarchar(max)'),
  66.     @x
  67. end
  68. GO
  69.  
  70. ENABLE TRIGGER [ddl_stat] ON ALL SERVER
  71. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top