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

我们正在迁移到生产环境,我想编写一个sript,DBA可以在运行我用aspnet_regsql生成的脚本后立即创建一个具有角色的用户.在开发环境中,我一直在Global.asax.cs中使用Membership Provider的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,我可以让INSERT工作,但是我需要重新声明并重新分配每个变量.

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