• 
• 

 

JOBS AUDITORIA SQL SERVER PACK 1

T-SQL com a criação dos principais jobs para auditoria.

Você tem que logico trocar ** SERVIDOR CONCENTRADOR ** pelo servidor que você elegeu para o concentrador das tabelas de auditoria:

Este script ira configura para rodar somente uma vez ao dia caso você necessite alterar, rode o script altere depois exporte o script conforme informando em outro artigo neste maravilhoso site.

 

Jobs Auditoria :

 backup

bancos por servidor

discos

linked server

usuarios bancos

 

 

 

USE [msdb]

 

GO

 

 

 

/****** Object:  Job [Auditoria_backup]    Script Date: 04/16/2015 17:51:01 ******/

 

BEGIN TRANSACTION

 

DECLARE @ReturnCode INT

 

SELECT @ReturnCode = 0

 

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/16/2015 17:51:01 ******/

 

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

 

BEGIN

 

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

 

 

END

 

 

 

DECLARE @jobId BINARY(16)

 

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Auditoria_backup', 

 

@enabled=1, 

 

@notify_level_eventlog=0, 

 

@notify_level_email=0, 

 

@notify_level_netsend=0, 

 

@notify_level_page=0, 

 

@delete_level=0, 

 

@description=N'No description available.', 

 

@category_name=N'[Uncategorized (Local)]', 

 

@owner_login_name=N'<< SEU USUARIO >>, @job_id = @jobId OUTPUT

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

/****** Object:  Step [backup]    Script Date: 04/16/2015 17:51:01 ******/

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'backup', 

 

@step_id=1, 

 

@cmdexec_success_code=0, 

 

@on_success_action=1, 

 

@on_success_step_id=0, 

 

@on_fail_action=3, 

 

@on_fail_step_id=0, 

 

@retry_attempts=0, 

 

@retry_interval=0, 

 

@os_run_priority=0, @subsystem=N'TSQL', 

 

@command=N'INSERT INTO [** SERVIDOR CONCENTRADOR **].[Control_Data_Base].[dbo].[check_backup]

 

([server],[base],[last_backup],[date_check],[situation])

 

 

 

SELECT 

 

@@SERVERNAME as Servidor

 

,sdb.Name AS DatabaseName

 

,MAX(bus.backup_finish_date) AS Ultimo_bak

 

, getdate() as Data_atual

 

,(CASE WHEN  datediff(day, MAX(bus.backup_finish_date), getdate()) >1  

 

  then ''ALERTA FALHA'' 

 

   else ''ok''    END

 

  ) as columnname

 

-- TROCAR O NOME ''SERVIDOR AUDITADO'' PARA O SERVIDOR DESEJADO             

 

 

 

FROM sys.sysdatabases sdb

 

 

 

LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

 

 

 

GROUP BY sdb.Name 

 

 

 

;', 

 

@database_name=N'master', 

 

@flags=0

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'auditoria', 

 

@enabled=1, 

 

@freq_type=4, 

 

@freq_interval=1, 

 

@freq_subday_type=1, 

 

@freq_subday_interval=0, 

 

@freq_relative_interval=0, 

 

@freq_recurrence_factor=0, 

 

@active_start_date=20150128, 

 

@active_end_date=99991231, 

 

@active_start_time=70000, 

 

@active_end_time=235959, 

 

@schedule_uid=N'49a46d7c-9053-4786-92c3-0b63b760b183'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

COMMIT TRANSACTION

 

GOTO EndSave

 

QuitWithRollback:

 

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

 

EndSave:

 

 

 

GO

 

 

 

/****** Object:  Job [Auditoria_Bancos_por_Servidores]    Script Date: 04/16/2015 17:51:01 ******/

 

BEGIN TRANSACTION

 

DECLARE @ReturnCode INT

 

SELECT @ReturnCode = 0

 

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/16/2015 17:51:01 ******/

 

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

 

BEGIN

 

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

 

 

END

 

 

 

DECLARE @jobId BINARY(16)

 

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Auditoria_Bancos_por_Servidores', 

 

@enabled=1, 

 

@notify_level_eventlog=0, 

 

@notify_level_email=0, 

 

@notify_level_netsend=0, 

 

@notify_level_page=0, 

 

@delete_level=0, 

 

@description=N'-- Bancos Por Servidores

 

 

 

use master

 

 

 

INSERT INTO [** SERVIDOR CONCENTRADOR **].[Control_Data_Base].[dbo].[bancos_servidores]

 

 

 

insert into bancos_servidores 

 

      ( [hostname]

 

      ,[dbname]

 

      ,[filetype]

 

      ,[location]

 

       ,[date_check]

 

       )

 

  

 

  

 

  SELECT

 

 @@SERVERNAME,

 

    db.name AS DBName,

 

 

 

    type_desc AS FileType,

 

 

 

    Physical_Name AS Location,

 

    

 

   Getdate()  

 

     

 

     

 

FROM

 

 

 

    sys.master_files mf

 

 

 

INNER JOIN 

 

 

 

    sys.databases db ON db.database_id = mf.databa', 

 

@category_name=N'[Uncategorized (Local)]', 

 

@owner_login_name=N'<<SEU USUARIO>>', @job_id = @jobId OUTPUT

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

/****** Object:  Step [Bancos_por_Servidores]    Script Date: 04/16/2015 17:51:01 ******/

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Bancos_por_Servidores', 

 

@step_id=1, 

 

@cmdexec_success_code=0, 

 

@on_success_action=1, 

 

@on_success_step_id=0, 

 

@on_fail_action=2, 

 

@on_fail_step_id=0, 

 

@retry_attempts=0, 

 

@retry_interval=0, 

 

@os_run_priority=0, @subsystem=N'TSQL', 

 

@command=N'use master

 

 

 

 

 

INSERT INTO [** SERVIDOR CONCENTRADOR **].[Control_Data_Base].[dbo].[bancos_servidores]

 

      ( [hostname]

 

      ,[dbname]

 

      ,[filetype]

 

      ,[location]

 

       ,[date_check]

 

       ,[Gigas]

 

       )

 

       

 

       

 

SELECT   @@SERVERNAME, db.name,    type_desc ,    Physical_Name ,Getdate()  ,((size*8) / 1024) /1024 FROM    sys.master_files mf

 

 

 

INNER JOIN     sys.databases db ON db.database_id = mf.database_id  

 

', 

 

@database_name=N'master', 

 

@flags=0

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Bancos_por_Servidores', 

 

@enabled=1, 

 

@freq_type=4, 

 

@freq_interval=1, 

 

@freq_subday_type=1, 

 

@freq_subday_interval=0, 

 

@freq_relative_interval=0, 

 

@freq_recurrence_factor=0, 

 

@active_start_date=20150317, 

 

@active_end_date=99991231, 

 

@active_start_time=50000, 

 

@active_end_time=235959, 

 

@schedule_uid=N'6e162db2-a946-4c23-b351-87b8cb6b23c6'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

COMMIT TRANSACTION

 

GOTO EndSave

 

QuitWithRollback:

 

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

 

EndSave:

 

 

 

GO

 

 

 

/****** Object:  Job [Auditoria_discos]    Script Date: 04/16/2015 17:51:01 ******/

 

BEGIN TRANSACTION

 

DECLARE @ReturnCode INT

 

SELECT @ReturnCode = 0

 

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/16/2015 17:51:01 ******/

 

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

 

BEGIN

 

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

 

 

END

 

 

 

DECLARE @jobId BINARY(16)

 

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Auditoria_discos', 

 

@enabled=1, 

 

@notify_level_eventlog=0, 

 

@notify_level_email=0, 

 

@notify_level_netsend=0, 

 

@notify_level_page=0, 

 

@delete_level=0, 

 

@description=N'No description available.', 

 

@category_name=N'[Uncategorized (Local)]', 

 

@owner_login_name=N'<< SEU USUARIO >>', @job_id = @jobId OUTPUT

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

/****** Object:  Step [Auditoria_discos]    Script Date: 04/16/2015 17:51:01 ******/

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Auditoria_discos', 

 

@step_id=1, 

 

@cmdexec_success_code=0, 

 

@on_success_action=1, 

 

@on_success_step_id=0, 

 

@on_fail_action=2, 

 

@on_fail_step_id=0, 

 

@retry_attempts=0, 

 

@retry_interval=0, 

 

@os_run_priority=0, @subsystem=N'TSQL', 

 

@command=N'DECLARE @hr int 

 

DECLARE @fso int 

 

DECLARE @drive char(1) 

 

DECLARE @odrive int 

 

DECLARE @TotalSize varchar(20) 

 

DECLARE @MB bigint ; SET @MB = 1048576

 

 

 

CREATE TABLE #drives (drive char(1) PRIMARY KEY, 

 

                      FreeSpace int NULL, 

 

                      TotalSize int NULL)

 

INSERT #drives(drive,FreeSpace) 

 

EXEC master.dbo.xp_fixeddrives

 

EXEC @hr=sp_OACreate ''Scripting.FileSystemObject'',@fso OUT 

 

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

 

 

 

DECLARE dcur CURSOR LOCAL FAST_FORWARD 

 

 

 

FOR SELECT drive from #drives 

 

 

 

ORDER by drive

 

 

 

OPEN dcur

 

 

 

FETCH NEXT FROM dcur INTO @drive

 

 

 

WHILE @@FETCH_STATUS=0 

 

 

 

BEGIN

 

 

 

        EXEC @hr = sp_OAMethod @fso,''GetDrive'', @odrive OUT, @drive 

 

 

 

        IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso 

 

 

 

        EXEC @hr = sp_OAGetProperty @odrive,''TotalSize'', @TotalSize OUT 

 

 

 

        IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive 

 

 

 

        UPDATE #drives 

 

 

 

        SET TotalSize=@TotalSize/@MB 

 

 

 

        WHERE drive=@drive 

 

 

 

        FETCH NEXT FROM dcur INTO @drive

 

END

 

 

 

CLOSE dcur 

 

DEALLOCATE dcur

 

EXEC @hr=sp_OADestroy @fso 

 

 

 

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

 

 

 

INSERT INTO [** SERVIDOR CONCENTRADOR **].[Control_Data_Base].[dbo].[espaco_discos]

 

            ([server],[drive],[freespace],[totalsize],[percentfree],[date_check])

 

 

 

SELECT @@SERVERNAME as servido,

 

       drive, 

 

       FreeSpace as ''Livre(MB)'', 

 

       TotalSize as ''Total(MB)'', 

 

       CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as ''Livre(%)'' ,

 

       GETDATE()

 

FROM #drives 

 

ORDER BY drive

 

DROP TABLE #drives

 

RETURN 

 

GO

 

-- Confesso que parte do código peguei na internet, só não lembro aonde.', 

 

@database_name=N'master', 

 

@flags=0

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Auditoria_discos', 

 

@enabled=1, 

 

@freq_type=4, 

 

@freq_interval=1, 

 

@freq_subday_type=1, 

 

@freq_subday_interval=0, 

 

@freq_relative_interval=0, 

 

@freq_recurrence_factor=0, 

 

@active_start_date=20150416, 

 

@active_end_date=99991231, 

 

@active_start_time=71500, 

 

@active_end_time=235959, 

 

@schedule_uid=N'11b64e2a-cd4a-42b1-8959-e3db1723c7d6'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

COMMIT TRANSACTION

 

GOTO EndSave

 

QuitWithRollback:

 

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

 

EndSave:

 

 

 

GO

 

 

 

/****** Object:  Job [Auditoria_Linked_Servers]    Script Date: 04/16/2015 17:51:01 ******/

 

BEGIN TRANSACTION

 

DECLARE @ReturnCode INT

 

SELECT @ReturnCode = 0

 

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/16/2015 17:51:01 ******/

 

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

 

BEGIN

 

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

 

 

END

 

 

 

DECLARE @jobId BINARY(16)

 

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Auditoria_Linked_Servers', 

 

@enabled=1, 

 

@notify_level_eventlog=0, 

 

@notify_level_email=0, 

 

@notify_level_netsend=0, 

 

@notify_level_page=0, 

 

@delete_level=0, 

 

@description=N'Auditoria_Linked_Servers', 

 

@category_name=N'[Uncategorized (Local)]', 

 

@owner_login_name=N'<<SEU USUARIO >>', @job_id = @jobId OUTPUT

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

/****** Object:  Step [Auditoria_Linked_Servers]    Script Date: 04/16/2015 17:51:01 ******/

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Auditoria_Linked_Servers', 

 

@step_id=1, 

 

@cmdexec_success_code=0, 

 

@on_success_action=1, 

 

@on_success_step_id=0, 

 

@on_fail_action=2, 

 

@on_fail_step_id=0, 

 

@retry_attempts=0, 

 

@retry_interval=0, 

 

@os_run_priority=0, @subsystem=N'TSQL', 

 

@command=N'INSERT INTO [** SERVIDOR CONCENTRADOR **].[Control_Data_Base].[dbo].[Linked_Servers]

 

      ( [hostname]

 

      ,[name]

 

      ,[product]

 

      ,[provider]

 

      ,[data_source]

 

       ,[remote_name]

 

       ,[date_check]

 

       )

 

select @@SERVERNAME,s.name, s.product,s.provider,s.data_source, l.remote_name,Getdate()

 

from sys.servers s

 

    join sys.linked_logins l

 

        on s.server_id = l.server_id

 

    left join sys.server_principals p

 

        on l.local_principal_id = p.principal_id

 

where s.is_linked = 1', 

 

@database_name=N'master', 

 

@flags=0

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Auditoria_Linked_Servers', 

 

@enabled=1, 

 

@freq_type=8, 

 

@freq_interval=1, 

 

@freq_subday_type=1, 

 

@freq_subday_interval=0, 

 

@freq_relative_interval=0, 

 

@freq_recurrence_factor=1, 

 

@active_start_date=20150320, 

 

@active_end_date=99991231, 

 

@active_start_time=70000, 

 

@active_end_time=235959, 

 

@schedule_uid=N'4523f5c9-3db8-4c9d-8651-3b9342df38af'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

COMMIT TRANSACTION

 

GOTO EndSave

 

QuitWithRollback:

 

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

 

EndSave:

 

 

 

GO

 

 

 

/****** Object:  Job [Auditoria_Usuarios_Bancos]    Script Date: 04/16/2015 17:51:01 ******/

 

BEGIN TRANSACTION

 

DECLARE @ReturnCode INT

 

SELECT @ReturnCode = 0

 

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/16/2015 17:51:01 ******/

 

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

 

BEGIN

 

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

 

 

END

 

 

 

DECLARE @jobId BINARY(16)

 

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Auditoria_Usuarios_Bancos', 

 

@enabled=1, 

 

@notify_level_eventlog=0, 

 

@notify_level_email=0, 

 

@notify_level_netsend=0, 

 

@notify_level_page=0, 

 

@delete_level=0, 

 

@description=N'No description available.', 

 

@category_name=N'[Uncategorized (Local)]', 

 

@owner_login_name=N'<<SEU USUARIO>>', @job_id = @jobId OUTPUT

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

/****** Object:  Step [Auditoria_Usuarios_Bancos]    Script Date: 04/16/2015 17:51:01 ******/

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Auditoria_Usuarios_Bancos', 

 

@step_id=1, 

 

@cmdexec_success_code=0, 

 

@on_success_action=1, 

 

@on_success_step_id=0, 

 

@on_fail_action=2, 

 

@on_fail_step_id=0, 

 

@retry_attempts=0, 

 

@retry_interval=0, 

 

@os_run_priority=0, @subsystem=N'TSQL', 

 

@command=N'IF EXISTS (SELECT * FROM TEMPDB.dbo.sysobjects WHERE NAME IN (''##Users'')) 

 

BEGIN

 

  DROP TABLE ##Users

 

END

 

GO

 

IF EXISTS (SELECT * FROM TEMPDB.dbo.sysobjects WHERE NAME IN (N''##ACESSO'')) 

 

BEGIN

 

  DROP TABLE ##ACESSO

 

END

 

GO

 

CREATE TABLE ##Users (

 

[sid] varbinary(100) NULL,

 

[Login Name] varchar(100) NULL

 

)

 

 

 

 

 

CREATE TABLE ##ACESSO ([T_user_id] VARCHAR(MAX), [T_server_login] VARCHAR(MAX), [T_database_role] VARCHAR(MAX), [T_database] VARCHAR(MAX))

 

declare @cmd1 nvarchar(500)

 

declare @cmd2 nvarchar(500)

 

set @cmd1 = ''

 

INSERT INTO ##Users ([sid],[Login Name]) SELECT sid, loginname FROM master.dbo.syslogins

 

INSERT INTO ##ACESSO 

 

 SELECT su.[name] ,  

 

 u.[Login Name]  , 

 

  sug.name   , ''''?''''

 

  FROM [?].[dbo].[sysusers] su 

 

  LEFT OUTER JOIN ##Users u 

 

  ON su.sid = u.sid 

 

  LEFT OUTER JOIN ([?].[dbo].[sysmembers] sm  

 

  INNER JOIN [?].[dbo].[sysusers] sug 

 

  ON sm.groupuid = sug.uid) 

 

  ON su.uid = sm.memberuid  

 

  WHERE su.hasdbaccess = 1 

 

  AND su.[name] != ''''dbo''''

 

''

 

exec sp_MSforeachdb @command1=@cmd1

 

 

 

 

 

INSERT INTO  [** SERVIDOR CONCENTRADOR **].[Control_Data_Base].[dbo].[Security_Users]

 

      ( [hostname]

 

       ,[user_id]

 

      ,[server_login]

 

      ,[database_role]

 

      ,[database]

 

      ,[date_check]

 

       )

 

SELECT @@SERVERNAME as hostname ,[T_user_id] , [T_server_login]  , [T_database_role]  , [T_database] , Getdate() as date_check FROM ##ACESSO  

 

GROUP BY  [T_user_id] , [T_server_login]  , [T_database_role]  , [T_database]   ORDER BY [T_database]', 

 

@database_name=N'master', 

 

@flags=0

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Auditoria_Usuarios_Bancos', 

 

@enabled=1, 

 

@freq_type=4, 

 

@freq_interval=1, 

 

@freq_subday_type=1, 

 

@freq_subday_interval=0, 

 

@freq_relative_interval=0, 

 

@freq_recurrence_factor=0, 

 

@active_start_date=20150327, 

 

@active_end_date=99991231, 

 

@active_start_time=60000, 

 

@active_end_time=235959, 

 

@schedule_uid=N'5982f06a-7c06-4469-997b-29cc2881305e'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

COMMIT TRANSACTION

 

GOTO EndSave

 

QuitWithRollback:

 

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

 

EndSave:

 

 

 

GO