如何在SQLMembershipProvider中添加用户和角色？

Question

我们正在迁移到生产环境，并且我想编写一个脚本，使DBA能够在运行由aspnet_regsql生成的脚本后立即创建具有角色的用户。在开发环境中，我一直在使用Membership Provider的API在Global.asax.cs中添加用户和角色。但是我想避免这种硬编码方法。现在我的T-SQL经验不足。我编写了以下脚本，如果我不一次运行它，则可以正常工作。

Use MyApps_Prod;
GO

DECLARE @user_identity CHAR(40);
DECLARE @scalar_userid AS NVARCHAR(255);
DECLARE @scalar_roleid AS NVARCHAR(255);
DECLARE @app_id AS NVARCHAR(255);
SET @user_identity = N'AMERICAS\First.Last';

SET @app_id = (SELECT DISTINCT ApplicationId 
            FROM [dbo].[aspnet_Applications] 
            WHERE loweredapplicationname = 'MyApplication');
SELECT * FROM [dbo].[aspnet_Users] WHERE UserName = @user_identity

IF NOT EXISTS (SELECT * FROM [dbo].[aspnet_Users] WHERE UserName = @user_identity ) 
BEGIN
    INSERT INTO [dbo].aspnet_Users
             ( [ApplicationId], [UserName], [LoweredUserName], [LastActivityDate] )
    VALUES
        ( @app_id, @user_identity, LOWER(@user_identity), GETDATE());
END;

DECLARE @role_name CHAR(40);
SET @role_name = N'Communicator';
IF NOT EXISTS (SELECT * FROM [dbo].[aspnet_Roles] WHERE RoleName = @role_name ) 
BEGIN
    INSERT INTO [dbo].[aspnet_Roles]
        ( [ApplicationId], [RoleName], [LoweredRoleName])
    VALUES
        (@app_id, @role_name, LOWER(@role_name))
END;


SET @scalar_userid = (SELECT DISTINCT UserID FROM [dbo].aspnet_Users WHERE UserName = @user_identity);
SET @scalar_roleid = (SELECT DISTINCT RoleID FROM [dbo].aspnet_Roles WHERE RoleName = @role_name);

INSERT INTO [dbo].aspnet_UsersInRoles (UserID, RoleID)
    VALUES (
        @scalar_userid ,
        @scalar_roleid
    );


SET @role_name = N'AccessAdministrator';
IF NOT EXISTS (SELECT * FROM [dbo].[aspnet_Roles] WHERE RoleName = @role_name ) 
BEGIN
    INSERT INTO [dbo].[aspnet_Roles]
        ( [ApplicationId], [RoleName], [LoweredRoleName])
    VALUES
        (@app_id, @role_name, LOWER(@role_name))
END;


SET @scalar_roleid = (SELECT DISTINCT RoleID FROM [dbo].aspnet_Roles WHERE RoleName = @role_name);

INSERT INTO [dbo].aspnet_UsersInRoles (UserID, RoleID)
    VALUES (
        @scalar_userid ,
        @scalar_roleid
    );
GO

我发现只要在每个INSERT语句结尾添加分号并添加GO，就可以使INSERT正常工作，但这样需要重新声明和赋值每个变量。

那么一个真正的SQL开发人员会如何处理呢？

- Blanthor

---

如果其他方法都失败了，就在global.asax中保留v1版本并在v2中删除它。 - Greg

Answer 1

不要手动编写INSERT语句，可以使用存储过程，这些存储过程是SqlMembershipProvider实现提供程序的一部分，并在使用aspnet_reg.exe工具安装应用程序服务时包含在内。

具体来说，可以使用以下存储过程：

• aspnet_Roles_CreateRole 创建新角色
• aspnet_Membership_CreateUser 创建用户并提供其成员身份数据（密码、安全问题和答案等）
• aspnet_UsersInRoles_AddUsersToRoles 将现有用户添加到现有角色中

aspnet_Membership_CreateUser 是其中唯一棘手的一个。假设您不是将密码以明文形式存储，那么需要通过@Password参数将哈希或加密版本传递给存储过程。建议使用Reflector检查SqlMembershipProvider类的CreateUser方法中的代码。在那里，您将看到.NET如何在幕后处理此逻辑。

作为替代脚本的选择，考虑编写一个命令行程序，该程序可能会读取文本文件，并创建指定的角色、用户和用户角色关联。该命令行程序将直接使用Membership API并处理所有低级细节。然后，您可以将此命令行程序作为构建或部署过程的一部分执行。

祝编程愉快！

- Scott Mitchell

Answer 2

你是否尝试在运行硬编码实现时同时运行SQL Profiler？这样可以显示要运行的确切顺序。

- Babak Naffas

---

如果可以的话，您应该至少使用数据库中包含的存储过程。分析器应该能够向您展示如何使用它们。 - Greg

Answer 3

“一个真正的SQL开发人员会怎么做呢？”

以下是一些开始。虽然T-SQL不直接支持数组，但我通常会循环遍历表变量，将它们视为数组，以压缩/重用代码。在您的示例中，这并没有太大的好处。但是，如果有10个角色名称，那就会有所帮助。

例如：

DECLARE @roles TABLE (rolename CHAR(40));
INSERT @roles
 SELECT 'Communicator'
 UNION ALL
 SELECT 'AccessAdministrator';
DECLARE @rolename CHAR(40);

--loop through the @roles table variable like it's an array
WHILE (SELECT COUNT(*) FROM @roles) > 0
  BEGIN
    SELECT TOP 1 @rolename = rolename FROM @roles;
    --Do something with the current rolename
    SELECT @rolename;
    DELETE @roles WHERE rolename = @rolename;
  END

- brian

Answer 4

通过脚本创建它并不是一个好主意。而且，如果您正在使用API，我不确定当您说必须手动创建时是否已经尝试迁移现有数据。

但如果这是您的选择，那么我建议您使用Reflector工具查看那些成员身份/角色方法的代码。仅运行存储过程是不够的，因为在这些API方法执行存储过程之前，可能涉及加密/哈希、选择正确的应用程序名称和其他几个检查，根据您在web.config中的设置。

因此，请确保您考虑所有这些因素，因为您将能够插入用户详细信息，但当您想要使用它时，您将使用API，这可能会破坏其他东西。

- gbs