修改SQL Server 2005执行环境实例演示

此文主要是介绍正确对SQL Server 2005执行环境进行修改是实际操作的实例演示，正如我上一篇文章里所介绍的一样，改变存储过程的SQL Server 2005数据库的执行环境非常有用，接下来我将通过实例来讲解如何实现这一功能。

在这个例子中，您会看到如何使用EXECUTE AS将没有确切权限的使用者模拟为所有者对表格进行插入操作。

在第一行语句中，我使用了REVERT命令，这样，您就可以完整地返回到例子中，而不必担心需要清除任何对象。

REVERT   
GO

在下面的代码的第七行，我使用了清除语句，这样可以检查我在随后的例子中要使用的对象是否已经存在，如果已经存在，就将其清除。

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

以下的脚本语句创建了两个登录名和数据库的用户账户，注意，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命令，我将当前的SQL Server 2005执行环境设定为TableOwner，在运行了这个命令之后，所有的权限评估将以TableOwner运行，而以前的系统管理员权限将不再适用。

EXECUTE AS USER = 'TableOwner'   
GO

运行这个语句就能够表明现在的SQL Server 2005执行环境是TableOwner：

SELECT SESSION_USER   
GO

这个脚本将在TableOwnerSchema的模式中创建一个名为MyTable的表格，因为我已经赋予了该用户CREATE TABLE 的权限，所以TableOwner可以执行这条语句。

CREATE TABLE TableOwnerSchema.MyTable   
(   
Field1 INT   
)   
GO

当我运行REVERT语句的时候，可以在SQL Server 2005执行环境链中回退一步，在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

接下来，我将SQL Server 2005执行环境转换为BaseUser并尝试运行存储过程：

EXECUTE AS USER = 'BaseUser'   
GO   
EXEC usp_InsertMyTable   
GO

现在我可以向TableSchema.MyTable表格中添加记录了，因为在这个过程中TableOwner允许我这样做，而BaseOwner并没有明确的权限可以向该表格添加记录，所以该用户的任何尝试都会导致错误的发生。为了演示这个问题，可以运行以下的脚本，该脚本改变了我们刚才的过程，改为运行在调用者的SQL Server 2005执行环境中。

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