User Logins Report

[dhulbert]

Is it possible to run reports on who has created, modified and deleted, Uers, Schemas, Procedures Views etc.

I'm trying to get a handle on who's doing what on the server.

Cheers.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Jamfool]

(Depending on version of sql2k5+.) There is an inbuilt report called 'Schema Change History' which runs the following:

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

 

[dhulbert]

Thats' great, it gets most of th ethings I need,

Any idea how to see who has created or dropped users?

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Jamfool]

I am not sure there is anything that will allow you to do this out of the box. I have set up a server wide ddl trigger to capture developer changes ;p

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
USE [xxxx] --replace XXXX for insert also ;p

CREATE TABLE [dbo].[DDL_Server_Log](
	[PostTime] [datetime] NULL,
	[UserName] [nvarchar](100) NULL,
	[EventName] [nvarchar](100) NULL,
	[StatementTSQL] [nvarchar](2000) NULL,
	[XmlEventData] [xml] NULL
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [DDL_Server_Log_Trigger] 
ON ALL SERVER
FOR 
--DDL_LOGIN_EVENTS, DDL_GDR_SERVER_EVENTS, DDL_AUTHORIZATION_SERVER_EVENTS, DDL_ENDPOINT_EVENTS
CREATE_LOGIN,
ALTER_LOGIN,
DROP_LOGIN,
GRANT_SERVER,
DENY_SERVER,
REVOKE_SERVER,
CREATE_ENDPOINT,
ALTER_ENDPOINT,
DROP_ENDPOINT,
CREATE_DATABASE,
DROP_DATABASE,
ALTER_AUTHORIZATION_SERVER
AS
SET ARITHABORT ON
SET NOCOUNT ON  
DECLARE @data XML
SET @data = EVENTDATA()

BEGIN TRY
	INSERT [xxxx].[dbo].[DDL_Server_Log] 
	   (PostTime, UserName, EventName, StatementTSQL, XmlEventData) 
	   VALUES 
	   (
	   GETDATE(), 
	   @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
	   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
	   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
	   @data
	   );
END TRY
BEGIN CATCH
	PRINT 'DDL_Server_Log_Trigger- '
		   + char(13) + char(10) + 'ErrorNumber:' + COALESCE(CAST(ERROR_NUMBER() AS VARCHAR(4000)),'')
		   + char(13) + char(10) + 'ErrorMessage:' + COALESCE(CAST(ERROR_MESSAGE() AS VARCHAR(4000)),'')
END CATCH



GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [DDL_Server_Log_Trigger] ON ALL SERVER
GO