title: How to add users and roles in SQL for the SQLMembershipProvider?
body: <p>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 <code>aspnet_regsql</code>.  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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have found that I can get the <code>INSERTs</code> to work if I end each <code>INSERT</code> with a semicolon and then add <code>GO</code>, but then I need to redeclare and reassign each variable.</p>
  
  <p>How would a real SQL developer do this?</p>