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!

Answer 2

Have you tried running your hard-coded implementation with SQL Profiler running? That should show you the exact order to run things.

Answer 3

"How would a real SQL developer do this?"

Here's a start. While T-SQL does not directly support arrays, I'll typically loop through table variables, treating them as arrays, to condense/reuse code. In your example, it's not a huge benefit. But, if there were 10 rolenames, it would be.

Example:

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

That isn't a good idea creating it via script. Also I am not sure when you say you have to create manually if you are using API. Did you try migrating your existing data?

But if that is the choice then I would suggest you to use Reflector Tool to see the code of those membership /Roles methods. Just running the stored proc isn't sufficient because there might be encryption/hashing, picking right applicationName and several other checks involved as per you setting in the web.config before the stored proc is executed by those API methods.

So make sure you consider all those because you will be be able to insert user details but when you want to use it, you will be use API and that might break something else.