Geek Answers Handbook

How to add users and roles in SQL for SQLMembershipProvider?

We move on to the production environment, and I want to write a script that the DBA can create a user with roles immediately after running the script generated with aspnet_regsql. In the development environment, I added users and roles with the Membership Provider API in Global.asax.cs. But I want to avoid this hard-coded approach. Now my T-SQL exprience flaw shows. I wrote the following script that works if I do not 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 found that I can make it INSERTswork if I end each INSERTsemicolon and then add GO, but then I need to redefine and reassign each variable.

How could a real SQL developer do?

+5
4

INSERT , SqlMembershipProvider aspnet_reg.exe.

, :

  • aspnet_Roles_CreateRole,
  • aspnet_Membership_CreateUser, (, ..).
  • aspnet_UsersInRoles_AddUsersToRoles, .

aspnet_Membership_CreateUser . , , , sproc @Password. Reflector SqlMembershipProvider CreateUser. , .NET .

, , , , . API- . .

!

+10

SQL Profiler? .

+2

" 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 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
0

script. , , , API. ?

, Reflector Tool, /. , /, , ​​ web.config , API.

, , , , API, - .

0

More articles:


All Articles