如果連接到SQL server 的應(yīng)用程序只需要訪問SQL server 實例內(nèi)部的對象和資源，那這是非常理想的。但是，通常一個應(yīng)用程序需要訪問外部系統(tǒng)的資源，例如文件、網(wǎng)絡(luò)、環(huán)境變量或注冊表。舉例來說，應(yīng)用程序可能需要運行xp_cmdshell 擴(kuò)展存儲過程來調(diào)用一個Windows shell命令，并執(zhí)行一個shell命令來獲取一個目錄下的文件列表。或者，這個應(yīng)用程序安排一個SQL server Agent工作來執(zhí)行維護(hù)任務(wù)。這個工作有一個Active Scripting工作步驟或一個Web Service任務(wù)來調(diào)用一個Web Service，以便驗證地理位置和郵編信息。

默認(rèn)情況下，在SQL server 2000中，只有sysadmin固定服務(wù)器角色的成員才可以執(zhí)行xp_cmdshell擴(kuò)展存儲過程和Active Scripting工作步驟。當(dāng)xp_cmdshell擴(kuò)展存儲過程被sysadmin固定服務(wù)器角色的一個成員執(zhí)行時，shell命令的Windows進(jìn)程在SQL Server服務(wù)帳戶的安全上下文中運行。當(dāng)sysadmin角色的一個成員的一個工作運行時，它的Active Scripting工作步驟運行在SQL server Agent服務(wù)帳戶的安全之下。但是，在大多數(shù)公司里，數(shù)據(jù)庫管理員角色和應(yīng)用程序開發(fā)人員角色通常是分開的。基于安全考慮，應(yīng)用程序開發(fā)人員不被允許具有sysadmin權(quán)限。為了使應(yīng)用程序開發(fā)人員可以訪問外部資源而不必給他們過多的權(quán)限，SQL Server提供了代理帳戶的解決方案。

擴(kuò)展存儲過程xp_sqlagent_proxy_account設(shè)置SQL server Agent和xp_cmdshell在執(zhí)行工作或命令時對于不是sysadmin固定服務(wù)器角色成員的用戶所使用的代理帳戶信息。例如，下面的命令設(shè)置代理帳戶為一個域帳戶PowerDomainPowerUser，然后使得非sysadmin登錄進(jìn)來在域帳戶的安全上下文中執(zhí)行Active Scripting工作步驟和xp_cmdshell。

;;;USE master GO

-- Create a test login called testuser

EXEC sp_addlogin 'testuser', 'testuser'

-- Add a windows domain account PoweDomainPowerUser as the proxy account.

EXECUTE xp_sqlagent_proxy_account N'SET' , N'PowerDomain' , N'PowerUser' , N'P@ssw0rd'

-- Enable non-sysadmin logins to run active Scripting job steps and execute xp_cmdshell.

EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0

-- Grant database access to the sql server login account that you want to provide access.

EXEC sp_grantdbaccess 'testuser'

-- Grant execute permission on xp_cmdshell to the sql server login account.

GRANT exec ON xp_cmdshell TO [testuser]

GO請注意，在SQL server 2000中只能指定一個代理帳戶。這個帳戶是用來執(zhí)行xp_cmdshell和Active Scripting工作步驟的。

在SQL server 2005和2008中，為了允許一個非sysadmin登錄進(jìn)來從而執(zhí)行xp_cmdshell，你需要創(chuàng)建一個特定的系統(tǒng)憑證##xp_cmdshell_proxy_account##，這是通過運行外部存儲過程sp_xp_cmdshell_proxy_account并指定一個Windows帳戶來實現(xiàn)的。這個帳戶將被非sysadmin角色的成員用戶用來運行xp_cmdshell。

USE master GO

-- Create a test login called testuser

CREATE LOGIN testuser WITH PASSWORD='P3h4jek@x'

-- Create a proxy credential for xp_cmdshell.

EXEC sp_xp_cmdshell_proxy_account 'PowerDomainPowerUser', 'P@ssw0rd'

-- Grant database access to the sql server login account that you want to provide access.

EXEC sp_grantdbaccess 'testuser'

-- Grant execute permission on xp_cmdshell to the sql server login account.

GRANT exec ON sys.xp_cmdshell TO [testuser]

GO

為了確認(rèn)##xp_cmdshell_proxy_account##憑證確實被創(chuàng)建了，你可以選擇sys.credentials視圖。

你還可以對SQL server 2005 and 2008中的SQL server Agent工作的代理進(jìn)行更多和更好的控制。你可以指定不止一個的代理帳戶。你還可以指定你想應(yīng)用一個代理帳戶到哪個子系統(tǒng)(工作步驟類型)上去。

在創(chuàng)建一個代理帳戶之前，你需要定義一個Windows憑證。在憑證創(chuàng)建之后，你可以創(chuàng)建一個代理帳戶并分配這個憑證給它。然后你授權(quán)這個代理訪問給一個或多個子系統(tǒng)。如果你使用sp_grant_proxy_to_subsystem存儲過程來授予訪問權(quán)限給多個子系統(tǒng)，那么你將需要多次執(zhí)行這個存儲過程。在這之后，你可以授權(quán)這個權(quán)限給多個SQL Server登錄、msdb角色、和/或服務(wù)器角色來使用這個代理帳戶。

例如，我們想為執(zhí)行SSIS包的工作步驟創(chuàng)建一個叫做SSISProxy的代理。這個代理將使用域帳戶PowerDomainPowerUser的憑證。我們想允許testUser使用這個代理帳戶登錄進(jìn)來執(zhí)行它所擁有的SQL server Agent工作中的SSIS包。

;;;-- Create a credential containing the domain account PowerDomainPowerUser and its password CREATE CREDENTIAL PowerUser WITH IDENTITY = N'PowerDomainPowerUser', SECRET = N'P@ssw0rd'

GO

USE [msdb]

GO

-- Create a new proxy called SSISProxy and assign the PowerUser credentail to it

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSISProxy',@credential_name=N'PowerUser',@enabled=1

-- Grant SSISProxy access to the 'SSIS package execution' subsystem

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'SSISProxy', @subsystem_id=11

-- Grant the login testUser the permissions to use SSISProxy

EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'testUser', @proxy_name=N'SSISProxy'

GO在登錄進(jìn)去之后，testUser授權(quán)這個訪問給代理帳戶SSISProxy，在testUser所擁有的一個工作中，如果一個工作步驟是要執(zhí)行一個SSIS包，那么testUser可以選擇代理SSISProxy并在這個代理帳戶下運行這個步驟。

sql server 2005有11個子系統(tǒng)，羅列如下：

1 Value Description

2 microsoft ActiveX Script

3 Operating system (CmdExec)

4 Replication Snapshot Agent

5 Replication Log Reader Agent

6 Replication Distribution Agent

7 Replication Merge Agent

8 Replication Queue Reader Agent

9 Analysis Services Command

10 Analysis Services Query

11 SSIS包執(zhí)行

sql server 2008為PowerShell的集成又添加了一個子系統(tǒng)。

12 PowerShell Script

總結(jié)

sql Server中的代理帳戶為登錄到SQL Server中執(zhí)行Windows shell命令和SQL server Agent工作提供了一個解決方法而不必提供過多的權(quán)限。這篇文章描述了在SQL Server2000、2005和2008中怎樣建立代理，并比較了它們之間的不同。