Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Логирование в базе данных или аудит в Standart версии  [new]
NewBie77
Member

Откуда:
Сообщений: 184
 		Доброго времени суток. Задача такая :
1.Логировать кто залогинился в базу данных (с фильтром для исключения некоторых юзеров)
2.Кто пытался залогиниться и не смог
3.Кто что изменил в процедурах,функциях и в тригерах
4.Кто добавил новое поле в таблицу или модифицировал его
5.Кто создал новую процедуру,функцию,тригер,вюшку,таблицу

Я знаю что это все можно сделать правильно настроив аудит но к сожалению мне версия не позволяет (Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Standard Edition (64-bit)) , обновить версию на данном этапе тоже не можем и приходиться все делать руками.

Нашел решения :
1. думаю над этим
2.Читать из error log-a
3.Смотреть дату модификации в sys.objects и если она изменлась то по имени процедуры вытаскивать definition c sys.sql_modules (но узер кто изменил к сожалению не буду знать). Также попробовал Reports -> Schema Change History с помощью профаилера взял селект где я буду знать юзера кто модифицировал процедуру ,но она будет загружать базу и это не утсраивает
+
exec sp_executesql @stmt=N'begin try
declare @enable int;
select top 1 @enable = convert(int,value_in_use) from sys.configurations where name = ''default trace enabled''
if @enable = 1
begin
declare @d1 datetime;
declare @diff int;
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int ;
declare @temp_trace table (
obj_name nvarchar(256)
, obj_id int
, database_name nvarchar(256)
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256)
, login_name nvarchar(256)
, user_name nvarchar(256)
, application_name nvarchar(256)
, ddl_operation nvarchar(40)
);

select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = PATINDEX(''%\%'', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'';

insert into @temp_trace
select ObjectName
, ObjectID
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, NTUserName
, ApplicationName
, ''temp''
from ::fn_trace_gettable( @base_tracefilename, default )
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = db_id() ;

update @temp_trace set ddl_operation = ''CREATE'' where event_class = 46;
update @temp_trace set ddl_operation = ''DROP'' where event_class = 47;
update @temp_trace set ddl_operation = ''ALTER'' where event_class = 164;

select @d1 = min(start_time) from @temp_trace
set @diff= datediff(hh,@d1,getdate())
set @diff=@diff/24;

select @diff as difference
, @d1 as date
, object_type as obj_type_desc
, (dense_rank() over (order by obj_name,object_type ) )%2 as l1
, (dense_rank() over (order by obj_name,object_type,start_time ))%2 as l2
, *
from @temp_trace where object_type not in (21587) -- don''t bother with auto-statistics as it generates too much noise
order by start_time desc;
end else
begin
Select top 0 1 as difference, 1 as date, 1 as obj_type_desc, 1 as l1, 1 as l2, 1 as obj_name, 1 as obj_id, 1 as database_name, 1 as start_time, 1 as event_class, 1 as event_subclass, 1 as object_type, 1 as server_name, 1 as login_name, 1 as user_name, 1 as application_name, 1 as ddl_operation
end
end try
begin catch
select -100 as difference
, ERROR_NUMBER() as date
, ERROR_SEVERITY() as obj_type_desc
, 1 as l1, 1 as l2
, ERROR_STATE() as obj_name
, 1 as obj_id
, ERROR_MESSAGE() as database_name
, 1 as start_time, 1 as event_class, 1 as event_subclass, 1 as object_type, 1 as server_name, 1 as login_name, 1 as user_name, 1 as application_name, 1 as ddl_operation
end catch',@params=N''

4. думаю над этим
5. думаю над этим

Прошу любой помощи для решения данной задачи. Заранее спасибо!
1 май 15, 10:21    [17590576]     Ответить | Цитировать Сообщить модератору
 Re: Логирование в базе данных или аудит в Standart версии  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37143
 		1. Логинятся на сервер, а не в базу.
2. Лог, аудит.
3-5. DDL Trigger.
1 май 15, 11:01    [17590662]     Ответить | Цитировать Сообщить модератору
 Re: Логирование в базе данных или аудит в Standart версии  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
 		а) default trace
б) event notifications
1 май 15, 11:21    [17590737]     Ответить | Цитировать Сообщить модератору
 Re: Логирование в базе данных или аудит в Standart версии  [new]
NewBie77
Member

Откуда:
Сообщений: 184
 		спасибо за отзывы

Гавриленко Сергей Алексеевич , прочитаю про DDL Trigger

invm , default trace не будет сильно загружать базу ?
1 май 15, 11:25    [17590754]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить