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

Question

我们正在迁移到生产环境，我想编写一个脚本，让 DBA 在运行我使用 aspnet_regsql 生成的脚本后可以立即创建具有角色的用户。在开发环境中，我一直在 Global.asax.cs 中使用成员资格提供商的 API 添加用户和角色。但我想避免这种硬编码方法。现在我的 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，我就可以让 INSERTs 工作，但随后我需要重新声明并重新分配每个变量。

真正的 SQL 开发人员会如何做到这一点？

Answer 1

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

特别是，使用：

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

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

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

快乐编程！

Answer 2

您是否尝试过在运行 SQL Profiler 的情况下运行硬编码实现？这应该会告诉你运行的确切顺序。

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

Answer 4

通过脚本创建它不是一个好主意。另外我不确定你什么时候说如果你使用 API 则必须手动创建。您是否尝试过迁移现有数据？

但如果这是选择，那么我建议您使用 Reflector Tool 来查看这些membership /Roles 方法的代码。仅运行存储过程是不够的，因为在这些 API 方法执行存储过程之前，可能会根据您在 web.config 中的设置进行加密/散列、选择正确的 applicationName 以及其他一些检查。

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