修改SQL Server 2005執行環境

執行環境是SQL Server中設定使用者許可權的認證方式，例如，當您登入到SQL Server的時候，登入賬戶就被賦予了一定的許可權，其中可能包括登入的功能、訪問資料庫以及在資料庫中執行某些操作的功能。

SQL Server 2005包含了EXECUTE AS語句，通過使用EXECUTE AS語句，您可以為批處理和過程轉換執行環境，這樣，呼叫該批處理或過程的使用者就可以使用不同的許可權來操作了。

所有權鏈

在我正式講解SQL Server 2005中執行環境的問題之前，先來簡單地說說所有權鏈的工作原理。

當使用者執行一個儲存過程的時候（假定該使用者擁有執行該儲存過程的許可權），SQL Server將該儲存過程的所有者與這個儲存過程所涉及到的物件的所有者進行對比，如果他們的所有者相同，那麼就不必對這些引用物件的許可權進行評估了。

所以，如果使用者Tim獲得了儲存過程usp_ProcedureChain的許可權，而usp_ProcedureChain儲存過程的所有者是dbo，那麼，如果dbo還同時擁有usp_ProcedureChain所呼叫的其他儲存過程，那麼Tim在執行這個儲存過程的時候就不會出現錯誤。

執行環境的轉換

在SQL Server 2000中，您可以使用SETUSER命令來模擬SQL使用者的執行環境，但問題在於，只有系統管理員或者資料庫的所有者才能使用這個命令，而且Windows賬戶也不能使用該命令。

在SQL Server 2005中，EXECUTE AS語句可以替代SETUSER來改變儲存過程、觸發器、批處理或者函式的執行環境。如果執行環境變成了另外一個使用者，那麼SQL Server將檢查該使用者的許可權。如果您需要在建立或修改一個儲存過程或函式的時候指定EXECUTE AS語句，您需要具備IMPERSONATE的許可權，以及建立該物件的許可權。

例項

正如我剛才所介紹的一樣，改變儲存過程的執行環境非常有用，接下來我將通過例項來講解如何實現這一功能。在這個例子中，您會看到如何使用EXECUTE AS將沒有確切許可權的使用者模擬為所有者對錶格進行插入操作。

在第一行語句中，我使用了REVERT命令，這樣，您就可以完整地返回到例子中，而不必擔心需要清除任何物件。

REVERT

GO

在列表A中的第七行，我使用了清除語句，這樣可以檢查我在隨後的例子中要使用的物件是否已經存在，如果已經存在，就將其清除。

IF OBJECT_ID('usp_InsertMyTable','P')>0

      DROP PROCEDURE usp_InsertMyTable

GO

IF OBJECT_ID('TableOwnerSchema.MyTable','U')>0

DROP TABLE TableOwnerSchema.MyTable

GO

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'TableOwnerSchema')

DROP SCHEMA [TableOwnerSchema]

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'BaseUser')

DROP USER BaseUser

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'BaseUser')

DROP LOGIN BaseUser

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'TableOwner')

DROP USER TableOwner

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TableOwner')

DROP LOGIN TableOwner

列表A

以下的指令碼語句建立了兩個登入名和資料庫的使用者賬戶，注意，CHECK_EXPIRATION和CHECK_POLICY語句，這兩條語句是SQL Server 2005中新出現的。這些語句告訴SQL Server不要對這個使用者賬戶強制執行密碼截止期限策略，同時也不要進行任何型別的密碼策略檢查，對於強制安全策略而言，這些是非常有效的方法。

CREATE LOGIN [BaseUser] WITH PASSWORD=N'baseuser',

DEFAULT_DATABASE=[TRS],

CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

CREATE USER [BaseUser] FOR LOGIN [BaseUser]

GO

CREATE LOGIN [TableOwner] WITH PASSWORD=N'tableowner',

DEFAULT_DATABASE=[TRS],

CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

CREATE USER TableOwner FOR LOGIN TableOwner

GO

在SQL Server 2005中，模式不再是和資料庫使用者相同的事情了，對於所包含的物件而言，它處於完全不同的名稱空間。使用者和模式的分離是SQL Server 2005中的一大進步，這樣做使物件的所有權可以分離，而且比SQL Server 2000更易於管理，以下的語句建立了我們將要使用的資料庫模式：

CREATE SCHEMA [TableOwnerSchema] AUTHORIZATION [TableOwner]

GO

Now I enable logins so they can be used:

ALTER LOGIN [TableOwner] ENABLE

ALTER LOGIN [BaseUser] ENABLE

GO

GRANT CREATE TABLE TO TableOwner

GO

首先，我使用了EXECUTE AS命令，我將當前的執行環境設定為TableOwner，在執行了這個命令之後，所有的許可權評估將以TableOwner執行，而以前的系統管理員許可權將不再適用。

EXECUTE AS USER = 'TableOwner'

GO

執行這個語句就能夠表明現在的執行環境是TableOwner：

SELECT SESSION_USER

GO

這個指令碼將在TableOwnerSchema的模式中建立一個名為MyTable的表格，因為我已經賦予了該使用者CREATE TABLE 的許可權，所以TableOwner可以執行這條語句。

CREATE TABLE TableOwnerSchema.MyTable

(

      Field1 INT

)

GO

當我執行REVERT語句的時候，可以在執行環境鏈中回退一步，在SQL Server 2005中，執行環境是可以巢狀的，所以如果您在同一個資料庫連線中有很多使用者在執行，您可能需要多次執行該語句以返回到原始的登入環境。

REVERT

GO

SELECT SESSION_USER

GO

現在我要對新的表格進行快速選擇以確認它的存在：

SELECT * FROM TableOwnerSchema.MyTable

GO

以下的指令碼建立了一個過程可以插入新的TableOwnerSchema.MyTable表格，注意我在過程定義中使用了WITH EXECUTE AS 'TableOwner'語句，這意味著該過程被執行的時候，它將在TableOwner的執行環境中被執行。

CREATE PROCEDURE usp_InsertMyTable

WITH EXECUTE AS 'TableOwner'

AS
BEGIN
            INSERT INTO TableOwnerSchema.MyTable(Field1)VALUES(8)

END

GO

我還可以將執行許可權賦予一個使用者賬戶，在這種情況下，我使用以前建立的名為BaseUser的使用者。

GRANT EXEC ON usp_InsertMyTable TO BaseUser
GO

接下來，我將執行環境轉換為BaseUser並嘗試執行儲存過程：

EXECUTE AS USER = 'BaseUser'

GO

EXEC usp_InsertMyTable

GO

現在我可以向TableSchema.MyTable表格中新增記錄了，因為在這個過程中TableOwner允許我這樣做，而BaseOwner並沒有明確的許可權可以向該表格新增記錄，所以該使用者的任何嘗試都會導致錯誤的發生。為了演示這個問題，可以執行以下的指令碼，該指令碼改變了我們剛才的過程，改為執行在呼叫者的執行環境中。

REVERT

GO

ALTER PROCEDURE usp_InsertMyTable

AS
BEGIN
            INSERT INTO TableOwnerSchema.MyTable(Field1)VALUES(8)

END

GO

EXECUTE AS USER = 'BaseUser'

GO

EXEC usp_InsertMyTable

GO

REVERT

開發者和資料庫管理員會發現在執行儲存過程的時候轉換許可權非常有用，尤其是您處理TRUNCATE TABLE語句的時候，這個方法能幫上大忙，因為TRUNCATE TABLE並沒有可以指定的許可權。您可以將許可權賦予將要進行擷取表格操作的使用者，然後在操作結束的時候再將原有的許可權設定恢復就可以了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-558079/，如需轉載，請註明出處，否則將追究法律責任。