.NET FOLLOWER

    In addition to the article SharePoint: SqlMembershipProvider – Get All Users In Role, here is one more method to extend the SqlMembershipProvider with. It’s found out that the SqlMembershipProvider doesn’t provide a method to lock user. By default a user can be automatically locked after several frequent and failed attempts to login. To unlock such users the SqlMembershipProvider supplies with the UnlockUser method. But what if administrator wants to temporarily lock user for some reason? Unfortunately, there is no such method out-of-box.

So, let’s try to implement our own LockUser method. Two obvious steps for that are as follows: to create a Stored Procedure in database; to extend a class derived from the SqlMembershipProvider with the proper method.

LockUser Stored Procedure

The stored procedure is very simple as we need just to update one field in the aspnet_Membership table for appropriate user. Below is the script to create such procedure. Run the script on MembershipProvider database, in my case it’s aspnetdb.

USE [aspnetdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      .Net Follower
-- Description:	Locks User
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_Membership_LockUser]
	@ApplicationName                         nvarchar(256),
    @UserName                                nvarchar(256)
AS
BEGIN
	DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF ( @UserId IS NULL )
        RETURN 1

    UPDATE dbo.aspnet_Membership 
    SET IsLockedOut = 1 WHERE @UserId = UserId

    RETURN 0
END

Custom Membership Provider

Now we can add the LockUser method to the custom Membership Provider called SqlMembershipProviderEx and shown in the article. The SqlMembershipProviderEx with the LockUser is listed below. Note that the methods mentioned in the previous article are skipped.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Reflection;
using System.Web.Security;
using Microsoft.SharePoint;

namespace dotNetFollower
{
    public class SqlMembershipProviderEx : SqlMembershipProvider
    {       
	    ...
		
        public bool LockUser(string username)
        {
            bool flag = false;
            CheckParameter(ref username, true, true, true, 0x100, "username");

            DoInSqlConnectionContext(delegate(SqlConnection connection)
            {
                //this.CheckSchemaVersion(connection.Connection);
                SqlCommand command = new SqlCommand("dbo.aspnet_Membership_LockUser", connection)
                {
                    CommandTimeout = CommandTimeout,
                    CommandType    = CommandType.StoredProcedure
                };
                command.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                command.Parameters.Add(CreateInputParam("@UserName", SqlDbType.NVarChar, username));
                SqlParameter parameter = new SqlParameter("@ReturnValue", SqlDbType.Int)
                {
                    Direction = ParameterDirection.ReturnValue
                };
                command.Parameters.Add(parameter);

                command.ExecuteNonQuery();
                flag = ((parameter.Value != null) ? ((int)parameter.Value) : -1) == 0;
            });

            return flag;
        }

        protected internal static void CheckParameter(ref string param, bool checkForNull, bool checkIfEmpty, bool checkForCommas, int maxSize, string paramName)
        {
            if (param == null)
            {
                if (checkForNull)
                    throw new ArgumentNullException(paramName);
            }
            else
            {
                param = param.Trim();
                if (checkIfEmpty && (param.Length < 1))
                    throw new ArgumentException(string.Format("The parameter '{0}' must not be empty.", new object[] { paramName }), paramName);

                if ((maxSize > 0) && (param.Length > maxSize))
                    throw new ArgumentException(string.Format("The parameter '{0}' is too long: it must not exceed {1} chars in length.", new object[] { paramName, maxSize.ToString(CultureInfo.InvariantCulture) }), paramName);

                if (checkForCommas && param.Contains(","))
                    throw new ArgumentException(string.Format("The parameter '{0}' must not contain commas.", new object[] { paramName }), paramName);
            }
        }

        ...
        
    }    
}

The latest version of the SqlMembershipProviderEx along with all used additional classes are available to download here.

Related posts:

• SharePoint: SqlMembershipProvider – Get All Users In Role