How to add users and roles in SQL for the SQLMembershipProvider?

Question

We are migrating to the production environment, and I want to write a sript the the DBA can create a user with the roles immediately after running the script I've generated with aspnet_regsql. In the Development environment, I have been adding users and roles with the Membership Provider's API in the Global.asax.cs. But I want to avoid this hard-coded approach. Now my T-SQL lack of exprience is showing. I wrote the following script, which works if I don't run it all at once.

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

I have found that I can get the INSERTs to work if I end each INSERT with a semicolon and then add GO, but then I need to redeclare and reassign each variable.

How would a real SQL developer do this?

Answer 1

Rather than writing INSERT statements by hand, use the stored procedures that are part of the SqlMembershipProvider implementation provider and are included when installing application services using the aspnet_reg.exe tool.

In particular, use:

• aspnet_Roles_CreateRole to create a new role
• aspnet_Membership_CreateUser to create a user and supply his membership data (password, security question and answer, and so forth)
• aspnet_UsersInRoles_AddUsersToRoles to add an existing user to an existing role

The aspnet_Membership_CreateUser is the only tricky one out of the lot. Presuming you are storing your passwords not in plain-text, you need to pass in either the hashed or encrypted version into the sproc via the @Password parameter. I suggest using Reflector to examine the code in the SqlMembershipProvider class's CreateUser method. There you will see how .NET handles this logic underneath the covers.

As an alternative to scripting this, consider writing a command line program that, perhaps, reads a text file and creates the specified roles and users and user-role associations. This command line program would use the Membership API directly and would, therefore, handle all of the low level details. You could then execute this command line program as part of your build or deployment process.

Happy Programming!