SharePoint: SqlMembershipProvider – Lock User
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.
