Mike Dopp

I have developed multiple E-Commerce sites (Mostly using a home grown Paypal /Verisign gateway). However I am always forgetting the format of some of these wrong so here is the test credit card numbers:

• American Express: 3111-1111-1111-1117
• Visa: 4111-1111-1111-1111 or 4222-2222-2222-2222
• MasterCard: 5111-1111-1111-1118 
• Discover: 6111-1111-1111-1116

Your CCV number and Name should not matter. Stay below $500 dollars as well make sure your date is not today but in the future.

Example:

Visa:4222-2222-2222-2222

CCV:123

Name:Homer J. Simpson

Expiration Date: 0110

Amount: $486.65

Hope this helps. Enjoy.

I have been coding using Microsoft technologies for a long while. Recently I was given the chance to do a little bit of web mastering. Due to my system admin background I was most definitely game for this.

Learning IIS 6.0 was not a huge leap as I had done a lot of development using IIS5.1(Windows XP Professional); however I had not as yet used Host headers in IIS (IIS 5.1 only allows for one site. I will post about that later.). This was a learning experience (Don’t even get me started on the issue of multiple SSL sites or web gardens).

I was doing some trail blazing while coming up with a build document to track my progress and to double check all my configurations. I highly recommend this process due to it has saved my bacon multiple times.

Uploaded all the sites and got the host headers configured correctly and moved the SSL (Secure Socket Layer)certifications over to the virgin IIS server when I noticed that not all my sites were happily working. Rendering was slow and some were just crashing. Crap time to take out the build document and rehearse my steps.

Getting through all the steps and all seemed fine. I could not find a reason my sites were acting all sorts of funky and the server processes were pegged out (running at 100%). I stressed and then went to my friend Google for some answers. I found some interesting little applications to monitor and test your sites.

IISReporter Cool little app to see how many people are logged in as well as a log viewer.

IISTrafficMonitor Another great application for watching real time traffic as well as a log viewer *may add extra cpu cycles*

IIS Web Application Stress Tool Just like the name implies it stresses your web applications

LoadRunner Add web traffic load to see what you Server(IIS) can handle. Before it all comes crashing down.

Web Application Stress Tool Another great Stress tool.

The rest are Microsoft utilities for your disposal.

IIS Diagnostics Toolkit 

IIS 6.0 Resource Kit Tools

Windows Server 2003 Resource Kit Tools

Creating Stress Tests for Web Applications

WCat Web Capacity Analysis Tool

These tools are guaranteed to help you in testing your server as well as your applications.

As you all know I have been working hard writing, eating and bleeding commerce server 2007 for the past year.

One thing I could never get past was why I could not do a site cache refresh from the tools provided with commerce server 2007. The tools that are provided are what you would call management tools (mostly well written web services.). Within these awesome tools is a cool little button called refresh. It’s sole purpose is to refresh the catalog,user base and or discounts without killing the database or web site. Killing I mean using IISReset to well reset or reboot the IIS server.

Well when clicking on the refresh button I would get an error mostly consisting of a 401.1 error with a little bit of information.

With frustration due to this error I went on a long quest using multiple search engines and lots of home time studying reasons this one function was failing to no avail. The forums at MSDN told me I was crazy and that it would work. Even got into a bit of a forum fight (you know those types of fights geeks have about which star wars series is better. 1970’s or 199x-200x (nothing physical just funny)). I had given up thrown in the towel other than using a url to do my site cache refreshing (only with service pack 1 installed mind you).

Due to the whole quest thing I found a lot of commerce server developers with blogs and subscribed to them.

A great blogger  Ravi Kanth  posted this on his blog on September 8th 2008:

“Cannot refresh commerce server cache when using host headers

Commerce server cache refresh through URL will not work if you are using host headers in your web site. You may receive IIS 401.1 error when you try to access the site.

The problem is not due to Commerce server but this issue occurs if you install Microsoft Windows XP Service Pack 2 (SP2) or Microsoft Windows Server 2003 Service Pack 1 (SP1). Windows XP SP2 and Windows Server 2003 SP1 include a loopback check security feature that is designed to help prevent reflection attacks on your computer. Therefore, authentication fails if the FQDN or the custom host header that you use does not match the local computer name.Click below link and do the changes suggested by this article.
http://support.microsoft.com/?id=896861 “

He goes further to describe why and how to do a work around.

Please Commerce Server 2007 team – Max Akbar (link love to Max when he fixes this) Please fix this.

Too Many cooks in the kitchen. When is too many developers one project a bad idea?

So I had the chance to meet with some friends the other day. Of course they work as developers like me. They work for one of those companies with allot of money to throw at a web site (a == 1). I found it interesting having 20 developers writing code for a web site. They called it Agile programming or Extreme Programming (Developer Humping). The reason I  called it this was as they (my friends) described agile as two developers sit in a cubicle(but of course no office unless your management right?) one developer coded while the other sat behind him correcting his mistakes.

So what bothered me most was having another developer watching over your shoulder as you coded. Like what the hell. How is that environment inviting to any developer? So here is my way of disgracing this habit as well as promoting it I guess.

Disgracing:

1. Cost is way more than your ROI (Return of Investment) on any project (yes I will argue this.)

2. It is said this type (Developer Humping) of development is good for the customer. what? how does that translate?

3. Somehow Projects get done faster? (wow I could argue that all day.) This goes back to the too many cooks in the kitchen mentality.

Promoting:

1. Both developers learn quick and bounce ideas off of each other.

2. Micro management of developers is much easier.

So in theory if your company has the money to spend tons of money on 2 developers per cubie(cubicle for those not in cubicles) and developing for 1 web site good on you. Truly in my humble opinion Agile development should be used for large “Software” companies with budgets for multiple projects.

This is what our friends at Wikipedia had to say:

Extreme Programming (or XP) is a software engineering methodology (and a form of agile software development)^[1][2][3] prescribing a set of daily stakeholder practices that embody and encourage particular XP values (below). Proponents believe that exercising these practices—traditional software engineering practices taken to so-called "extreme" levels—leads to a development process that is more responsive to customer needs ("agile") than traditional methods, while creating software of better quality.

So why do I seem against this type of development? I guess I am not all that much impressed with it. I love team development. I truly encourage it. I am not of the mind “put a developer in a closet with pizza and a coke and leave him/her be”. I however like my space and love to collaborate with my fellow developers on multiple projects.

Currently I work on multiple web sites at the same time. I like to call it the shotgun development method. I work with 2 other excellent web developers. One is front end (the famous joe) his code is second to none. Then I work with new guy Bryan (still waiting on a blog bryan) who is the C# guru. We all have our pieces of the projects we work on. This is a great work environment. Typically called the Waterfall method.

No developer humping here just straight work hard and enjoy the team work involved.

Let me know how you feel.

Thought I best post this before I forget.

Here is the SQL Script:

/**********************************************************************/ /* InstallMembership.SQL */ /* */ /* Installs the tables, triggers and stored procedures necessary for */ /* supporting the aspnet feature of ASP.Net */ /* */ /* InstallCommon.sql must be run before running this file. */ /* ** Copyright Microsoft, Inc. 2002 ** All Rights Reserved. */ /**********************************************************************/ PRINT '-------------------------------------------' PRINT 'Starting execution of InstallMembership.SQL' PRINT '-------------------------------------------' GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON -- We don't want (NULL = NULL) == TRUE GO SET ANSI_PADDING ON GO SET ANSI_NULL_DFLT_ON ON GO /*************************************************************/ /*************************************************************/ /*************************************************************/ /*************************************************************/ /*************************************************************/ DECLARE @dbname nvarchar(128) SET @dbname = N'aspnetdb' IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = @dbname OR name = @dbname))) BEGIN RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname) END GO USE [aspnetdb] GO IF (NOT EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Applications') AND (type = 'U'))) BEGIN RAISERROR('The table ''aspnet_Applications'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1) END IF (NOT EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Users') AND (type = 'U'))) BEGIN RAISERROR('The table ''aspnet_Users'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1) END IF (NOT EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Applications_CreateApplication') AND (type = 'P'))) BEGIN RAISERROR('The stored procedure ''aspnet_Applications_CreateApplication'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1) END IF (NOT EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Users_CreateUser') AND (type = 'P'))) BEGIN RAISERROR('The stored procedure ''aspnet_Users_CreateUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1) END IF (NOT EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Users_DeleteUser') AND (type = 'P'))) BEGIN RAISERROR('The stored procedure ''aspnet_Users_DeleteUser'' cannot be found. Please use aspnet_regsql.exe for installing ASP.NET application services.', 18, 1) END /*************************************************************/ /*************************************************************/ IF (NOT EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership') AND (type = 'U'))) BEGIN PRINT 'Creating the aspnet_Membership table...' CREATE TABLE dbo.aspnet_Membership ( ApplicationId uniqueidentifier NOT NULL FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId), UserId uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId), Password nvarchar(128) NOT NULL, PasswordFormat int NOT NULL DEFAULT 0, PasswordSalt nvarchar(128) NOT NULL, MobilePIN nvarchar(16), Email nvarchar(256), LoweredEmail nvarchar(256), PasswordQuestion nvarchar(256), PasswordAnswer nvarchar(128), IsApproved bit NOT NULL, IsLockedOut bit NOT NULL, CreateDate datetime NOT NULL, LastLoginDate datetime NOT NULL, LastPasswordChangedDate datetime NOT NULL, LastLockoutDate datetime NOT NULL, FailedPasswordAttemptCount int NOT NULL, FailedPasswordAttemptWindowStart datetime NOT NULL, FailedPasswordAnswerAttemptCount int NOT NULL, FailedPasswordAnswerAttemptWindowStart datetime NOT NULL, Comment ntext ) CREATE CLUSTERED INDEX aspnet_Membership_index ON aspnet_Membership(ApplicationId, LoweredEmail) END GO /*************************************************************/ /*************************************************************/ /*************************************************************/ DECLARE @ver int DECLARE @version nchar(100) DECLARE @dot int DECLARE @hyphen int DECLARE @SqlToExec nchar(400) SELECT @ver = 8 SELECT @version = @@Version SELECT @hyphen = CHARINDEX(N' - ', @version) IF (NOT(@hyphen IS NULL) AND @hyphen > 0) BEGIN SELECT @hyphen = @hyphen + 3 SELECT @dot = CHARINDEX(N'.', @version, @hyphen) IF (NOT(@dot IS NULL) AND @dot > @hyphen) BEGIN SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen) SELECT @ver = CONVERT(int, @version) END END /*************************************************************/ IF (@ver >= 8) EXEC sp_tableoption N'aspnet_Membership', 'text in row', 3000 /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_CreateUser') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_CreateUser GO CREATE PROCEDURE dbo.aspnet_Membership_CreateUser @ApplicationName nvarchar(256), @UserName nvarchar(256), @Password nvarchar(128), @PasswordSalt nvarchar(128), @Email nvarchar(256), @PasswordQuestion nvarchar(256), @PasswordAnswer nvarchar(128), @IsApproved bit, @CurrentTimeUtc datetime, @CreateDate datetime = NULL, @UniqueEmail int = 0, @PasswordFormat int = 0, @UserId uniqueidentifier OUTPUT AS BEGIN DECLARE @ApplicationId uniqueidentifier SELECT @ApplicationId = NULL DECLARE @NewUserId uniqueidentifier SELECT @NewUserId = NULL DECLARE @IsLockedOut bit SET @IsLockedOut = 0 DECLARE @LastLockoutDate datetime SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 ) DECLARE @FailedPasswordAttemptCount int SET @FailedPasswordAttemptCount = 0 DECLARE @FailedPasswordAttemptWindowStart datetime SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ) DECLARE @FailedPasswordAnswerAttemptCount int SET @FailedPasswordAnswerAttemptCount = 0 DECLARE @FailedPasswordAnswerAttemptWindowStart datetime SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ) DECLARE @NewUserCreated bit DECLARE @ReturnValue int SET @ReturnValue = 0 DECLARE @ErrorCode int SET @ErrorCode = 0 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSE SET @TranStarted = 0 EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END SET @CreateDate = @CurrentTimeUtc SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId IF ( @NewUserId IS NULL ) BEGIN SET @NewUserId = @UserId EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT SET @NewUserCreated = 1 END ELSE BEGIN SET @NewUserCreated = 0 IF( @NewUserId <> @UserId AND @UserId IS NOT NULL ) BEGIN SET @ErrorCode = 6 GOTO Cleanup END END IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END IF( @ReturnValue = -1 ) BEGIN SET @ErrorCode = 10 GOTO Cleanup END IF ( EXISTS ( SELECT UserId FROM dbo.aspnet_Membership WHERE @NewUserId = UserId ) ) BEGIN SET @ErrorCode = 6 GOTO Cleanup END SET @UserId = @NewUserId IF (@UniqueEmail = 1) BEGIN IF (EXISTS (SELECT * FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK ) WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email))) BEGIN SET @ErrorCode = 7 GOTO Cleanup END END IF (@NewUserCreated = 0) BEGIN UPDATE dbo.aspnet_Users SET LastActivityDate = @CreateDate WHERE @UserId = UserId IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END END INSERT INTO dbo.aspnet_Membership ( ApplicationId, UserId, Password, PasswordSalt, Email, LoweredEmail, PasswordQuestion, PasswordAnswer, PasswordFormat, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart ) VALUES ( @ApplicationId, @UserId, @Password, @PasswordSalt, @Email, LOWER(@Email), @PasswordQuestion, @PasswordAnswer, @PasswordFormat, @IsApproved, @IsLockedOut, @CreateDate, @CreateDate, @CreateDate, @LastLockoutDate, @FailedPasswordAttemptCount, @FailedPasswordAttemptWindowStart, @FailedPasswordAnswerAttemptCount, @FailedPasswordAnswerAttemptWindowStart ) IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN 0 Cleanup: IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCode END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_GetUserByName') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_GetUserByName GO CREATE PROCEDURE dbo.aspnet_Membership_GetUserByName @ApplicationName nvarchar(256), @UserName nvarchar(256), @CurrentTimeUtc datetime, @UpdateLastActivity bit = 0 AS BEGIN DECLARE @UserId uniqueidentifier IF (@UpdateLastActivity = 1) BEGIN -- select user ID from aspnet_users table SELECT TOP 1 @UserId = u.UserId FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId IF (@@ROWCOUNT = 0) -- Username not found RETURN -1 UPDATE dbo.aspnet_Users SET LastActivityDate = @CurrentTimeUtc WHERE @UserId = UserId SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut, m.LastLockoutDate FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE @UserId = u.UserId AND u.UserId = m.UserId END ELSE BEGIN SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut,m.LastLockoutDate FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId IF (@@ROWCOUNT = 0) -- Username not found RETURN -1 END RETURN 0 END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_GetUserByUserId') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_GetUserByUserId GO CREATE PROCEDURE dbo.aspnet_Membership_GetUserByUserId @UserId uniqueidentifier, @CurrentTimeUtc datetime, @UpdateLastActivity bit = 0 AS BEGIN IF ( @UpdateLastActivity = 1 ) BEGIN UPDATE dbo.aspnet_Users SET LastActivityDate = @CurrentTimeUtc FROM dbo.aspnet_Users WHERE @UserId = UserId IF ( @@ROWCOUNT = 0 ) -- User ID not found RETURN -1 END SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserName, m.IsLockedOut, m.LastLockoutDate FROM dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE @UserId = u.UserId AND u.UserId = m.UserId IF ( @@ROWCOUNT = 0 ) -- User ID not found RETURN -1 RETURN 0 END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_GetUserByEmail') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_GetUserByEmail GO CREATE PROCEDURE dbo.aspnet_Membership_GetUserByEmail @ApplicationName nvarchar(256), @Email nvarchar(256) AS BEGIN IF( @Email IS NULL ) SELECT u.UserName FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND u.UserId = m.UserId AND m.LoweredEmail IS NULL ELSE SELECT u.UserName FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND u.UserId = m.UserId AND LOWER(@Email) = m.LoweredEmail IF (@@rowcount = 0) RETURN(1) RETURN(0) END GO /*************************************************************/ /*************************************************************/ IF ( EXISTS( SELECT name FROM sysobjects WHERE ( name = N'aspnet_Membership_GetPasswordWithFormat' ) AND ( type = 'P' ) ) ) DROP PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat GO CREATE PROCEDURE dbo.aspnet_Membership_GetPasswordWithFormat @ApplicationName nvarchar(256), @UserName nvarchar(256), @UpdateLastLoginActivityDate bit, @CurrentTimeUtc datetime AS BEGIN DECLARE @IsLockedOut bit DECLARE @UserId uniqueidentifier DECLARE @Password nvarchar(128) DECLARE @PasswordSalt nvarchar(128) DECLARE @PasswordFormat int DECLARE @FailedPasswordAttemptCount int DECLARE @FailedPasswordAnswerAttemptCount int DECLARE @IsApproved bit DECLARE @LastActivityDate datetime DECLARE @LastLoginDate datetime SELECT @UserId = NULL SELECT @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat, @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount, @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved, @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND u.UserId = m.UserId AND LOWER(@UserName) = u.LoweredUserName IF (@UserId IS NULL) RETURN 1 IF (@IsLockedOut = 1) RETURN 99 SELECT @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount, @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1) BEGIN UPDATE dbo.aspnet_Membership SET LastLoginDate = @CurrentTimeUtc WHERE UserId = @UserId UPDATE dbo.aspnet_Users SET LastActivityDate = @CurrentTimeUtc WHERE @UserId = UserId END RETURN 0 END GO /*************************************************************/ /*************************************************************/ IF ( EXISTS( SELECT name FROM sysobjects WHERE ( name = N'aspnet_Membership_UpdateUserInfo' ) AND ( type = 'P' ) ) ) DROP PROCEDURE dbo.aspnet_Membership_UpdateUserInfo GO CREATE PROCEDURE dbo.aspnet_Membership_UpdateUserInfo @ApplicationName nvarchar(256), @UserName nvarchar(256), @IsPasswordCorrect bit, @UpdateLastLoginActivityDate bit, @MaxInvalidPasswordAttempts int, @PasswordAttemptWindow int, @CurrentTimeUtc datetime, @LastLoginDate datetime, @LastActivityDate datetime AS BEGIN DECLARE @UserId uniqueidentifier DECLARE @IsApproved bit DECLARE @IsLockedOut bit DECLARE @LastLockoutDate datetime DECLARE @FailedPasswordAttemptCount int DECLARE @FailedPasswordAttemptWindowStart datetime DECLARE @FailedPasswordAnswerAttemptCount int DECLARE @FailedPasswordAnswerAttemptWindowStart datetime DECLARE @ErrorCode int SET @ErrorCode = 0 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSE SET @TranStarted = 0 SELECT @UserId = u.UserId, @IsApproved = m.IsApproved, @IsLockedOut = m.IsLockedOut, @LastLockoutDate = m.LastLockoutDate, @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount, @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart, @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount, @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK ) WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND u.UserId = m.UserId AND LOWER(@UserName) = u.LoweredUserName IF ( @@rowcount = 0 ) BEGIN SET @ErrorCode = 1 GOTO Cleanup END IF( @IsLockedOut = 1 ) BEGIN GOTO Cleanup END IF( @IsPasswordCorrect = 0 ) BEGIN IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) ) BEGIN SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc SET @FailedPasswordAttemptCount = 1 END ELSE BEGIN SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1 END BEGIN IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts ) BEGIN SET @IsLockedOut = 1 SET @LastLockoutDate = @CurrentTimeUtc END END END ELSE BEGIN IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 ) BEGIN SET @FailedPasswordAttemptCount = 0 SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ) SET @FailedPasswordAnswerAttemptCount = 0 SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ) SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 ) END END IF( @UpdateLastLoginActivityDate = 1 ) BEGIN UPDATE dbo.aspnet_Users SET LastActivityDate = @LastActivityDate WHERE @UserId = UserId IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END UPDATE dbo.aspnet_Membership SET LastLoginDate = @LastLoginDate WHERE UserId = @UserId IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END END UPDATE dbo.aspnet_Membership SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate, FailedPasswordAttemptCount = @FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart WHERE @UserId = UserId IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN @ErrorCode Cleanup: IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCode END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_GetPassword') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_GetPassword GO CREATE PROCEDURE dbo.aspnet_Membership_GetPassword @ApplicationName nvarchar(256), @UserName nvarchar(256), @MaxInvalidPasswordAttempts int, @PasswordAttemptWindow int, @CurrentTimeUtc datetime, @PasswordAnswer nvarchar(128) = NULL AS BEGIN DECLARE @UserId uniqueidentifier DECLARE @PasswordFormat int DECLARE @Password nvarchar(128) DECLARE @passAns nvarchar(128) DECLARE @IsLockedOut bit DECLARE @LastLockoutDate datetime DECLARE @FailedPasswordAttemptCount int DECLARE @FailedPasswordAttemptWindowStart datetime DECLARE @FailedPasswordAnswerAttemptCount int DECLARE @FailedPasswordAnswerAttemptWindowStart datetime DECLARE @ErrorCode int SET @ErrorCode = 0 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSE SET @TranStarted = 0 SELECT @UserId = u.UserId, @Password = m.Password, @passAns = m.PasswordAnswer, @PasswordFormat = m.PasswordFormat, @IsLockedOut = m.IsLockedOut, @LastLockoutDate = m.LastLockoutDate, @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount, @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart, @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount, @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK ) WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND u.ApplicationId = a.ApplicationId AND u.UserId = m.UserId AND LOWER(@UserName) = u.LoweredUserName IF ( @@rowcount = 0 ) BEGIN SET @ErrorCode = 1 GOTO Cleanup END IF( @IsLockedOut = 1 ) BEGIN SET @ErrorCode = 99 GOTO Cleanup END IF ( NOT( @PasswordAnswer IS NULL ) ) BEGIN IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) ) BEGIN IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) ) BEGIN SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc SET @FailedPasswordAnswerAttemptCount = 1 END ELSE BEGIN SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1 SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc END BEGIN IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts ) BEGIN SET @IsLockedOut = 1 SET @LastLockoutDate = @CurrentTimeUtc END END SET @ErrorCode = 3 END ELSE BEGIN IF( @FailedPasswordAnswerAttemptCount > 0 ) BEGIN SET @FailedPasswordAnswerAttemptCount = 0 SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ) END END UPDATE dbo.aspnet_Membership SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate, FailedPasswordAttemptCount = @FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart WHERE @UserId = UserId IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END IF( @ErrorCode = 0 ) SELECT @Password, @PasswordFormat RETURN @ErrorCode Cleanup: IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCode END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_SetPassword') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_SetPassword GO CREATE PROCEDURE dbo.aspnet_Membership_SetPassword @ApplicationName nvarchar(256), @UserName nvarchar(256), @NewPassword nvarchar(128), @PasswordSalt nvarchar(128), @CurrentTimeUtc datetime, @PasswordFormat int = 0 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 Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt, LastPasswordChangedDate = @CurrentTimeUtc WHERE @UserId = UserId RETURN(0) END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_ResetPassword') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_ResetPassword GO CREATE PROCEDURE dbo.aspnet_Membership_ResetPassword @ApplicationName nvarchar(256), @UserName nvarchar(256), @NewPassword nvarchar(128), @MaxInvalidPasswordAttempts int, @PasswordAttemptWindow int, @PasswordSalt nvarchar(128), @CurrentTimeUtc datetime, @PasswordFormat int = 0, @PasswordAnswer nvarchar(128) = NULL AS BEGIN DECLARE @IsLockedOut bit DECLARE @LastLockoutDate datetime DECLARE @FailedPasswordAttemptCount int DECLARE @FailedPasswordAttemptWindowStart datetime DECLARE @FailedPasswordAnswerAttemptCount int DECLARE @FailedPasswordAnswerAttemptWindowStart datetime DECLARE @UserId uniqueidentifier SET @UserId = NULL DECLARE @ErrorCode int SET @ErrorCode = 0 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSE SET @TranStarted = 0 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 ) BEGIN SET @ErrorCode = 1 GOTO Cleanup END SELECT @IsLockedOut = IsLockedOut, @LastLockoutDate = LastLockoutDate, @FailedPasswordAttemptCount = FailedPasswordAttemptCount, @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart, @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount, @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart FROM dbo.aspnet_Membership WITH ( UPDLOCK ) WHERE @UserId = UserId IF( @IsLockedOut = 1 ) BEGIN SET @ErrorCode = 99 GOTO Cleanup END UPDATE dbo.aspnet_Membership SET Password = @NewPassword, LastPasswordChangedDate = @CurrentTimeUtc, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt WHERE @UserId = UserId AND ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) ) IF ( @@ROWCOUNT = 0 ) BEGIN IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) ) BEGIN SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc SET @FailedPasswordAnswerAttemptCount = 1 END ELSE BEGIN SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1 END BEGIN IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts ) BEGIN SET @IsLockedOut = 1 SET @LastLockoutDate = @CurrentTimeUtc END END SET @ErrorCode = 3 END ELSE BEGIN IF( @FailedPasswordAnswerAttemptCount > 0 ) BEGIN SET @FailedPasswordAnswerAttemptCount = 0 SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ) END END IF( NOT ( @PasswordAnswer IS NULL ) ) BEGIN UPDATE dbo.aspnet_Membership SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate, FailedPasswordAttemptCount = @FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart WHERE @UserId = UserId IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN @ErrorCode Cleanup: IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCode END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_UnlockUser') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_UnlockUser GO CREATE PROCEDURE dbo.aspnet_Membership_UnlockUser @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 = 0, FailedPasswordAttemptCount = 0, FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ), FailedPasswordAnswerAttemptCount = 0, FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ), LastLockoutDate = CONVERT( datetime, '17540101', 112 ) WHERE @UserId = UserId RETURN 0 END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_UpdateUser') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_UpdateUser GO CREATE PROCEDURE dbo.aspnet_Membership_UpdateUser @ApplicationName nvarchar(256), @UserName nvarchar(256), @Email nvarchar(256), @Comment ntext, @IsApproved bit, @LastLoginDate datetime, @LastActivityDate datetime, @UniqueEmail int, @CurrentTimeUtc datetime AS BEGIN DECLARE @UserId uniqueidentifier DECLARE @ApplicationId uniqueidentifier SELECT @UserId = NULL SELECT @UserId = u.UserId, @ApplicationId = a.ApplicationId 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) IF (@UniqueEmail = 1) BEGIN IF (EXISTS (SELECT * FROM dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK) WHERE ApplicationId = @ApplicationId AND @UserId <> UserId AND LoweredEmail = LOWER(@Email))) BEGIN RETURN(7) END END DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSE SET @TranStarted = 0 UPDATE dbo.aspnet_Users WITH (ROWLOCK) SET LastActivityDate = @LastActivityDate WHERE @UserId = UserId IF( @@ERROR <> 0 ) GOTO Cleanup UPDATE dbo.aspnet_Membership WITH (ROWLOCK) SET Email = @Email, LoweredEmail = LOWER(@Email), Comment = @Comment, IsApproved = @IsApproved, LastLoginDate = @LastLoginDate WHERE @UserId = UserId IF( @@ERROR <> 0 ) GOTO Cleanup IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN 0 Cleanup: IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN -1 END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_ChangePasswordQuestionAndAnswer') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer GO CREATE PROCEDURE dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer @ApplicationName nvarchar(256), @UserName nvarchar(256), @NewPasswordQuestion nvarchar(256), @NewPasswordAnswer nvarchar(128) AS BEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @UserId = u.UserId FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a WHERE LoweredUserName = LOWER(@UserName) AND u.ApplicationId = a.ApplicationId AND LOWER(@ApplicationName) = a.LoweredApplicationName AND u.UserId = m.UserId IF (@UserId IS NULL) BEGIN RETURN(1) END UPDATE dbo.aspnet_Membership SET PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer WHERE UserId=@UserId RETURN(0) END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_GetAllUsers') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_GetAllUsers GO CREATE PROCEDURE dbo.aspnet_Membership_GetAllUsers @ApplicationName nvarchar(256), @PageIndex int, @PageSize int AS BEGIN DECLARE @ApplicationId uniqueidentifier SELECT @ApplicationId = NULL SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName IF (@ApplicationId IS NULL) RETURN 0 -- Set the page bounds DECLARE @PageLowerBound int DECLARE @PageUpperBound int DECLARE @TotalRecords int SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageSize - 1 + @PageLowerBound -- Create a temp table TO store the select results CREATE TABLE #PageIndexForUsers ( IndexId int IDENTITY (0, 1) NOT NULL, UserId uniqueidentifier ) -- Insert into our temp table INSERT INTO #PageIndexForUsers (UserId) SELECT u.UserId FROM dbo.aspnet_Membership m, dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId ORDER BY u.UserName SELECT @TotalRecords = @@ROWCOUNT SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut, m.LastLockoutDate FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound ORDER BY u.UserName RETURN @TotalRecords END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_GetNumberOfUsersOnline') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline GO CREATE PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline @ApplicationName nvarchar(256), @MinutesSinceLastInActive int, @CurrentTimeUtc datetime AS BEGIN DECLARE @DateActive datetime SELECT @DateActive = DATEADD(minute, -(@MinutesSinceLastInActive), @CurrentTimeUtc) DECLARE @NumOnline int SELECT @NumOnline = COUNT(*) FROM dbo.aspnet_Users u(NOLOCK), dbo.aspnet_Applications a(NOLOCK), dbo.aspnet_Membership m(NOLOCK) WHERE u.ApplicationId = a.ApplicationId AND LastActivityDate > @DateActive AND a.LoweredApplicationName = LOWER(@ApplicationName) AND u.UserId = m.UserId RETURN(@NumOnline) END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_FindUsersByName') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_FindUsersByName GO CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByName @ApplicationName nvarchar(256), @UserNameToMatch nvarchar(256), @PageIndex int, @PageSize int AS BEGIN DECLARE @ApplicationId uniqueidentifier SELECT @ApplicationId = NULL SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName IF (@ApplicationId IS NULL) RETURN 0 -- Set the page bounds DECLARE @PageLowerBound int DECLARE @PageUpperBound int DECLARE @TotalRecords int SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageSize - 1 + @PageLowerBound -- Create a temp table TO store the select results CREATE TABLE #PageIndexForUsers ( IndexId int IDENTITY (0, 1) NOT NULL, UserId uniqueidentifier ) -- Insert into our temp table INSERT INTO #PageIndexForUsers (UserId) SELECT u.UserId FROM dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch) ORDER BY u.UserName SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut, m.LastLockoutDate FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound ORDER BY u.UserName SELECT @TotalRecords = COUNT(*) FROM #PageIndexForUsers RETURN @TotalRecords END GO /*************************************************************/ /*************************************************************/ IF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Membership_FindUsersByEmail') AND (type = 'P'))) DROP PROCEDURE dbo.aspnet_Membership_FindUsersByEmail GO CREATE PROCEDURE dbo.aspnet_Membership_FindUsersByEmail @ApplicationName nvarchar(256), @EmailToMatch nvarchar(256), @PageIndex int, @PageSize int AS BEGIN DECLARE @ApplicationId uniqueidentifier SELECT @ApplicationId = NULL SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName IF (@ApplicationId IS NULL) RETURN 0 -- Set the page bounds DECLARE @PageLowerBound int DECLARE @PageUpperBound int DECLARE @TotalRecords int SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageSize - 1 + @PageLowerBound -- Create a temp table TO store the select results CREATE TABLE #PageIndexForUsers ( IndexId int IDENTITY (0, 1) NOT NULL, UserId uniqueidentifier ) -- Insert into our temp table IF( @EmailToMatch IS NULL ) INSERT INTO #PageIndexForUsers (UserId) SELECT u.UserId FROM dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL ORDER BY m.LoweredEmail ELSE INSERT INTO #PageIndexForUsers (UserId) SELECT u.UserId FROM dbo.aspnet_Users u, dbo.aspnet_Membership m WHERE u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch) ORDER BY m.LoweredEmail SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved, m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate, u.UserId, m.IsLockedOut, m.LastLockoutDate FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound ORDER BY m.LoweredEmail SELECT @TotalRecords = COUNT(*) FROM #PageIndexForUsers RETURN @TotalRecords END GO /*************************************************************/ /*************************************************************/ IF (NOT EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))) BEGIN PRINT 'Creating the vw_aspnet_MembershipUsers view...' EXEC(' CREATE VIEW [dbo].[vw_aspnet_MembershipUsers] AS SELECT [dbo].[aspnet_Membership].[UserId], [dbo].[aspnet_Membership].[PasswordFormat], [dbo].[aspnet_Membership].[MobilePIN], [dbo].[aspnet_Membership].[Email], [dbo].[aspnet_Membership].[LoweredEmail], [dbo].[aspnet_Membership].[PasswordQuestion], [dbo].[aspnet_Membership].[PasswordAnswer], [dbo].[aspnet_Membership].[IsApproved], [dbo].[aspnet_Membership].[IsLockedOut], [dbo].[aspnet_Membership].[CreateDate], [dbo].[aspnet_Membership].[LastLoginDate], [dbo].[aspnet_Membership].[LastPasswordChangedDate], [dbo].[aspnet_Membership].[LastLockoutDate], [dbo].[aspnet_Membership].[FailedPasswordAttemptCount], [dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart], [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount], [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart], [dbo].[aspnet_Membership].[Comment], [dbo].[aspnet_Users].[ApplicationId], [dbo].[aspnet_Users].[UserName], [dbo].[aspnet_Users].[MobileAlias], [dbo].[aspnet_Users].[IsAnonymous], [dbo].[aspnet_Users].[LastActivityDate] FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users] ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId] ') END GO /*************************************************************/ /*************************************************************/ -- --Create Membership schema version -- DECLARE @command nvarchar(4000) SET @command = 'GRANT EXECUTE ON [dbo].aspnet_RegisterSchemaVersion TO ' + QUOTENAME(user) EXECUTE (@command) GO EXEC [dbo].aspnet_RegisterSchemaVersion N'Membership', N'1', 1, 1 GO /*************************************************************/ /*************************************************************/ -- --Create Membership roles -- IF ( NOT EXISTS ( SELECT name FROM sysusers WHERE issqlrole = 1 AND name = N'aspnet_Membership_FullAccess' ) ) EXEC sp_addrole N'aspnet_Membership_FullAccess' IF ( NOT EXISTS ( SELECT name FROM sysusers WHERE issqlrole = 1 AND name = N'aspnet_Membership_BasicAccess' ) ) EXEC sp_addrole N'aspnet_Membership_BasicAccess' IF ( NOT EXISTS ( SELECT name FROM sysusers WHERE issqlrole = 1 AND name = N'aspnet_Membership_ReportingAccess' ) ) EXEC sp_addrole N'aspnet_Membership_ReportingAccess' GO EXEC sp_addrolemember N'aspnet_Membership_BasicAccess', N'aspnet_Membership_FullAccess' EXEC sp_addrolemember N'aspnet_Membership_ReportingAccess', N'aspnet_Membership_FullAccess' GO -- --Stored Procedure rights for BasicAcess -- GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByUserId TO aspnet_Membership_BasicAccess GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByName TO aspnet_Membership_BasicAccess GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByEmail TO aspnet_Membership_BasicAccess GRANT EXECUTE ON dbo.aspnet_Membership_GetPassword TO aspnet_Membership_BasicAccess GRANT EXECUTE ON dbo.aspnet_Membership_GetPasswordWithFormat TO aspnet_Membership_BasicAccess GRANT EXECUTE ON dbo.aspnet_Membership_UpdateUserInfo TO aspnet_Membership_BasicAccess GRANT EXECUTE ON dbo.aspnet_Membership_GetNumberOfUsersOnline TO aspnet_Membership_BasicAccess GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Membership_BasicAccess GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Membership_BasicAccess GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Membership_BasicAccess -- --Stored Procedure rights for ReportingAccess -- GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByUserId TO aspnet_Membership_ReportingAccess GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByName TO aspnet_Membership_ReportingAccess GRANT EXECUTE ON dbo.aspnet_Membership_GetUserByEmail TO aspnet_Membership_ReportingAccess GRANT EXECUTE ON dbo.aspnet_Membership_GetAllUsers TO aspnet_Membership_ReportingAccess GRANT EXECUTE ON dbo.aspnet_Membership_GetNumberOfUsersOnline TO aspnet_Membership_ReportingAccess GRANT EXECUTE ON dbo.aspnet_Membership_FindUsersByName TO aspnet_Membership_ReportingAccess GRANT EXECUTE ON dbo.aspnet_Membership_FindUsersByEmail TO aspnet_Membership_ReportingAccess GRANT EXECUTE ON dbo.aspnet_CheckSchemaVersion TO aspnet_Membership_ReportingAccess GRANT EXECUTE ON dbo.aspnet_RegisterSchemaVersion TO aspnet_Membership_ReportingAccess GRANT EXECUTE ON dbo.aspnet_UnRegisterSchemaVersion TO aspnet_Membership_ReportingAccess -- --Additional stored procedure rights for FullAccess -- GRANT EXECUTE ON dbo.aspnet_Users_DeleteUser TO aspnet_Membership_FullAccess GRANT EXECUTE ON dbo.aspnet_Membership_CreateUser TO aspnet_Membership_FullAccess GRANT EXECUTE ON dbo.aspnet_Membership_SetPassword TO aspnet_Membership_FullAccess GRANT EXECUTE ON dbo.aspnet_Membership_ResetPassword TO aspnet_Membership_FullAccess GRANT EXECUTE ON dbo.aspnet_Membership_UpdateUser TO aspnet_Membership_FullAccess GRANT EXECUTE ON dbo.aspnet_Membership_ChangePasswordQuestionAndAnswer TO aspnet_Membership_FullAccess GRANT EXECUTE ON dbo.aspnet_Membership_UnlockUser TO aspnet_Membership_FullAccess -- --View rights -- GRANT SELECT ON dbo.vw_aspnet_Applications TO aspnet_Membership_ReportingAccess GRANT SELECT ON dbo.vw_aspnet_Users TO aspnet_Membership_ReportingAccess GRANT SELECT ON dbo.vw_aspnet_MembershipUsers TO aspnet_Membership_ReportingAccess /*************************************************************/ /*************************************************************/ /*************************************************************/ /*************************************************************/ DECLARE @command nvarchar(4000) SET @command = 'REVOKE EXECUTE ON [dbo].aspnet_RegisterSchemaVersion FROM ' + QUOTENAME(user) EXECUTE (@command) GO PRINT '--------------------------------------------' PRINT 'Completed execution of InstallMembership.SQL' PRINT '--------------------------------------------'

You can also find this script: C:\Windows\Microsoft.NET\Framework\v2.0.50727 as InstallMembership.SQL

Thank you Microsoft

Recently I ran into a SSL cert issue. I run a small e-commerce site and was doing some testing on it. Got to the buy now feature (when the site switches from non- SSL to SSL or http to https) I received a your SSL cert has expired. What? No I remember I purchased it forever ago but it shouldn’t be up now why did I have no warning?

Well with the help and guidance of the crew @ AwesomeIdeas I found a way to look up my SSL Certificate life.

copy & paste script below into a file called "CertExpirationCheck.vbs" and run the script from command line

When in the command prompt use the following parameter:

C:\> cscript certexpirationcheck.vbs [SubjectName]

C:\> cscript certexpirationcheck.vbs mikedopp.com

'**************************************************
'* CertExpirationCheck.vbs
'* Enumerate certificates with day left for expiry 
'**************************************************

Option Explicit
Dim SubjectName
If WScript.Arguments.Count > 0 Then
    SubjectName = LCase(WScript.Arguments(0))
Else
    CommandUsage
End If

Dim Store, Certificates, Certificate
Const CAPICOM_LOCAL_MACHINE_STORE = 1
Const CAPICOM_CERTIFICATE_FIND_SUBJECT_NAME = 1        
Const CAPICOM_STORE_OPEN_READ_ONLY = 0

Set Store = CreateObject("CAPICOM.Store")
Store.Open CAPICOM_LOCAL_MACHINE_STORE, "MY" ,CAPICOM_STORE_OPEN_READ_ONLY
Set Certificates = Store.Certificates.Find(CAPICOM_CERTIFICATE_FIND_SUBJECT_NAME, SubjectName, 0)

If Certificates.Count >0 Then
   For Each Certificate in Certificates
    'Certificate.display()    'If you want to see the Cert in UI
    WScript.Echo "*** Subject " & Certificate.SubjectName & " ***"
    WScript.Echo "Issued by " & Certificate.IssuerName 
    WScript.Echo "Valid from " & Certificate.ValidFromDate & " to " & Certificate.ValidToDate 
    WScript.Echo "Days to expiry " & DateDiff("d",now(),Certificate.ValidToDate)
    WScript.Echo 
   Next
 Else
  WScript.Echo "No certificates with SubjectName => '" & SubjectName & "'"
End If

Set Certificates = Nothing
Set Store = Nothing

Sub CommandUsage
  MsgBox "Usage: CertExpirationCheck.vbs  [SubjectName] ", vbInformation,"CertExpirationCheck"
  WScript.Quit(1)
End Sub