SQL Server下设置代理：提升数据库安全性

什么是SQL Server代理？

SQL Server代理是一种可编程的任务调度系统，可用于执行管理任务和自动化业务流程，同时也是SQL Server安全性的一部分。在SQL Server中，代理程序允许用户开发和调度自动化的任务，如备份数据库、清理日志、执行指定的存储过程等。SQL Server代理程序可以执行一个或多个作业、管理多个计划程序，并且每个计划程序可以根据时钟表、事件或根据其他条件来启动。

为什么需要设置SQL Server代理？

SQL Server代理最常见的用途是在等待和执行命令之间节省大量的开销。它提供了一个“中介”层，自动执行计划任务而无需人工干预，大大节省了手动管理的时间和精力。此外，SQL Server代理还可以为您提供一些额外的安全性保护，通过设置代理程序的权限和用户，限制对数据库的访问权限。

如何设置SQL Server代理？

步骤一：创建一个代理账户

首先，需要创建SQL Server代理所需的Windows账户。通常情况下，这个账户可以用Windows本地账户或域账户创建。当然，要根据实际情况创建。然后，需要将这个账户作为一个SQL Server代理账户添加到SQL Server中。下面是添加代理账户的方法：

USE [msdb];
GO
CREATE LOGIN [Domain\user] FROM WINDOWS; --将Windows账户加入
GO 
EXEC sp_addsrvrolemember @loginame = N'Domain\user', @rolename = N'sysadmin'; --将Windows账户添加到sysadmin角色
GO
USE [msdb] 
GO 
EXEC msdb.dbo.sp_adduser @login_name='Domain\user', @usename='Domain\user', @rolename='SQLAgentOperatorRole'; --将Windows账户添加到SqlAgent用户组
EXEC msdb.dbo.sp_adduser @login_name='Domain\user', @usename='Domain\user', @rolename='SQLAgentReaderRole';
EXEC msdb.dbo.sp_adduser @login_name='Domain\user', @usename='Domain\user', @rolename='SQLAgentUserRole';
GO

上述代码中，您需要将“Domain\user”替换成您创建的Windows账户名称。代码将Windows账户添加到sysadmin角色，这是因为该角色拥有对数据库名称、数据库中所有对象和SQL Server实例的完整控制权，这样可以避免代理程序无法访问某些操作。然后，将Windows账户添加到SqlAgentOperatorRole、SqlAgentReaderRole和SqlAgentUserRole角色，具体作用如下：

SqlAgentOperatorRole：允许用户管理代理和作业，这意味着用户可以修改创建作业、删除作业、修改步骤等信息。

SqlAgentReaderRole：允许用户查看代理、作业、操作和操作区域等信息。

SqlAgentUserRole：允许用户操作代理和作业。

步骤二：启用SQL Server代理

在SQL Server Management Studio中，单击SQL Server代理，然后在上下文菜单中选择“启动”选项。此外，还可以在SQL Server配置管理器中启用SQL Server代理。在SQL Server代理上右键单击，然后单击“属性”，在“常规”选项卡中单击“启动类型”下拉列表中的“自动”选项。

步骤三：创建SQL Server代理作业

创建新作业时，需要指定作业的名称、描述和执行器。执行器是要运行的一个或多个Transact-SQL脚本，可以在SQL Server Management Studio的作业步骤中指定。

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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'MyTestJob', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'This is a test job.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'your_login_name_here', 
        @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1', 
        @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'SELECT name FROM sys.databases', 
        @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'MyTestJob_Schedule', 
        @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=20170811, 
        @active_end_date=99991231, 
        @active_start_time=20000, 
        @active_end_time=235959
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:

上述代码中，您需要将“your_login_name_here”替换成您想要作为代理账户运行作业的登录名。然后，将T-SQL脚本添加到@command参数中，以指定要在作业中执行的T-SQL代码。最后，使用sp_add_jobschedule存储过程将作业计划添加到SQL Server代理中。

步骤四：配置SQL Server代理的参数

在SQL Server代理的“属性”对话框中，可以配置如下选项：

代理状态：允许您启用或禁用SQL Server代理。

执行历史记录：允许您将作业或操作历史记录存储在SQL Server中，以供查询使用。

警报系统：允许您在操作失败、收到特定事件或条件时向管理员发送警报。

多服务器管理：允许您以集中化的方式管理多个远程SQL Server实例上的代理和作业。

总结

在SQL Server中设置代理可以带来许多好处，节省大量的人工干预时间和精力，提高任务执行效率。此外，代理还可以增强SQL Server的安全性，通过管理代理程序的权限和用户，限制对数据库的访问权限。我们可以根据需要创建不同的代理账户，启用SQL Server代理，并创建代理作业，以及配置代理的其他参数，使代理能够更好地为我们服务。