Sql Server Job To Script Logins And Passwords
USE [msdb]
GO
 
/****** Object:  Job [Backup Logins]  ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT  @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]  ******/
IF NOT EXISTS ( SELECT  name
                FROM    msdb.dbo.syscategories
                WHERE   name = N'Database Maintenance'
                        AND category_class = 1 ) 
    BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB',
            @type = N'LOCAL', @name = N'Database Maintenance'
        IF ( @@ERROR <> 0
             OR @ReturnCode <> 0
           ) 
            GOTO QuitWithRollback
 
    END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'Backup Logins',
    @enabled = 1, @notify_level_eventlog = 2, @notify_level_email = 2,
    @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0,
    @description = N'This job backs up the logins to a text file.',
    @category_name = N'Database Maintenance', @owner_login_name = N'sa',
    @notify_email_operator_name = N'Database Administrators',
    @job_id = @jobId OUTPUT
IF ( @@ERROR <> 0
     OR @ReturnCode <> 0
   ) 
    GOTO QuitWithRollback
/****** Object:  Step [Backup Logins]     ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,
    @step_name = N'Backup Logins', @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 = 1,
    @os_run_priority = 0, @subsystem = N'TSQL', @command = N'USE MASTER
go
SET NOCOUNT ON
select ''-- Note:''
union
select ''-- Remove the [SQLSTATE 01000] messages before running this script.''
union
select ''-- Run from here onwards.''
select ''''
exec sp_help_revlogin', @database_name = N'master',
    @output_file_name = N'E:\LoginsBackup.txt', @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'Daily at 18:00', @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 = 20060706, @active_end_date = 99991231,
    @active_start_time = 180000, @active_end_time = 235959,
    @schedule_uid = N'c17f652a-5ce8-4b80-90df-b4d387b084aa'
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
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License