SSL Host Headers? Does it really exist?

Yes however somewhat tricky.

  • You need to have a Wildcard or *.domain.com SSL certificate.
  • Your domains need to be all sub-domains of the wild card domain
  • You just can’t do it using the IIS inetmgr user interface.

http://technet2.microsoft.com/WindowsServer/en/library/1032c063-e372-4875-9a31-d6de8e1e87921033.mspx?mfr=true

In short, to add an SSL host header, run the following:
cscript.exe adsutil.vbs SET /w3svc/<site identifier>/SecureBindings “:443:<host header>

To get the site identifier you can run the following:
cscript.exe adsutil.vbs ENUM Path “/P”

 

Being a System Administrator as well as a Web Developer I run into issues with the Mail protocol SMTP.

Things hang or people want their email better formatted. Or simply test out you spam messages you are going to blast before you well blast them.

Of course you run into issues like is my ports open correctly, is the SMTP server setup to block relaying? Has my domain been blacklisted from spamming?

These kind of questions are important. While I can’t fix the blacklisting domain issue the rest is well my night job.

However during the day I develop lots and lots of web code and sometimes and most often these applications need a way to contact people via email, txt and so forth.

Being behind a great firewall and sending test email can be a long and drawn out process. I found a  great application to do the SMTP testing for you inside you own localhost(127.0.0.1) Yes that is my localhost ip if you would like to “hack me” go ahead Ill wait.(if you don’t already know that is the IP of your pc).

PaperCut

This little app allows you to send SMTP messages without sending them anywhere.

screenshot1

 

Paper Cut is my favorite application for development. It allows me to debug the SMTP messages without sending them to anyone.

 

Digg This

We all love Windows Vista in our own way.

WindowsExperience9.9 Working for and with many PC manufactures I used to love to run Sysmark and associated types of System Tests to show off why I paid more or why someone should pay more for one PC versus the other.

So when Microsoft announced that in Windows Vista(all versions(a marketing idea I might add)) would have a internal way to test the hardware in a given pc to give you a status(Windows Experience index)  of how well Vista will work with given hardware components. I thought great now I can determine which laptop I should buy next.

So I headed out to a well known Vendor of name brand PC’s. I was really debating between a HP, Sony(I know ewww right?) or Dell. All had great components on paper. The components were:

  • 3GB Ram
  • 250GB HD
  • Windows Vista Home Premium
  • The big difference was the Processor: AMD or Intel? Dual core 64bit or Quad core 32bit?

So being a hardware geek I wanted to know what the Windows Experience Index was. Not that this was a determining factor since I was in love with the AMD anyway.

So on to my point. I was doing some exploring of windows operating systems like I do. I know your questioning yourself do I really want to continue reading this? Be sure this Edit is very simple and the point I will make is valid.

Like I said I was doing some exploring of the Windows Directory of Windows Vista Home Premium and found a folder called performance. Opened it up and found WinSAT and well was curious so I dug further. Found a bunch of files that didn’t make much sense but started doing editing and looking at the resources. Came up with mostly nothing other than videos of water and a prx file. Then I decided it was time to open the DataStore folder and found some XML files. I love XML so easy to hack edit so I DecentPCpoked around in these files unsure if I was going to break anything. The amount of love(data) in these xml  files was tremendous(at least for a windows machine) oh the hacking I will go I thought.

Did some editing of the File named: YYYY-MM-DD HH.MM.SS.MSMSMS Assessment(Initial).WinSAT.xml and to no avail nothing changed. Crap.

So I did some editing of the File named: “”(Formal).WinSAT.xml and changed all the values in the Root Node from:

<WinSPR>
    <SystemScore>3.6</SystemScore>
    <MemoryScore>4.9</MemoryScore>
    <CpuScore>4.8</CpuScore>
    <CPUSubAggScore>4.7</CPUSubAggScore>
    <VideoEncodeScore>4.9</VideoEncodeScore>
    <GraphicsScore>3.6</GraphicsScore>
    <GamingScore>4.5</GamingScore>
    <DiskScore>5.3</DiskScore>
  </WinSPR>

to:

<WinSPR>
    <SystemScore>9.9</SystemScore>
    <MemoryScore>9.9</MemoryScore>
    <CpuScore>9.9</CpuScore>
    <CPUSubAggScore>9.9</CPUSubAggScore>
    <VideoEncodeScore>9.9</VideoEncodeScore>
    <GraphicsScore>9.9</GraphicsScore>
    <GamingScore>9.9</GamingScore>
    <DiskScore>9.9</DiskScore>
  </WinSPR>

BestComputerEverSaved the XML file and went back to the Windows Experience Index and found hey instead of a System Score of 3.6 I got the best score you can get 9.9 . Trust me the software WinSAT will choke on you.

The most interesting thing is even when you try to refresh the settings hoping that the program will regenerate the Scores it just uses the old Scores as if they were true.

So the question is: How do we know if the Hardware Manufacture knows about this hack/edit and in order to sell more PC’s they change the XML on the OS as it ships?

Great question eh? Ah paranoia. It looks like Microsoft might need to update this issue as I have yet to find a fix or hotfix that will make this little piece of software work correctly.

Looks like this is not the only issue with WinSAT.exe on the Windows Vista platform.

Also if you copy WinSAT folder to Windows XP it will work on that platform as well. Of course that is for another blog post.

Of course the next question is does anyone really care? Does anyone use Windows Experience Index to judge the difference in PC’s? I would like to know.

Digg This

SUBST is a great DOS command that allows you to map a local path to a drive letter. For VirtualDriveexample, wouldn’t it be easier to access you GAC folder by specifying G: instead of C:\windows\assembly?

The SUBST command is very easy to use. Simply open up a command prompt window and type:

SUBST G: C:\windows\assembly

Now when you go to Windows Explorer or My Computer you will see a new drive with a label of G: Open the drive and you are looking at the contents of C:\windows\assembly

You could do this for any path on your PC with the syntax being…

SUBST drive: path Any Folder

When you type the SUBST command into a Command Prompt window, you will lose the virtual drive when you restart your system, so a better way to make the virtual file would be to put the SUBST command into a batch file that lives in your Startup folder. Now every time you start your PC, the virtual drive will be there.

Virtual Drive Creator is a free program that will create the virtual drive from a GUI, thus saving you from the dreaded Command Prompt. The program is free, but they do offer a better version with more features for a small registration fee.

To Delete the virtual drive: SUBST drive letter: /D

 

Digg This

 

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

  • AmericanExpressAmerican Express: 3111-1111-1111-1117
  • VisaVisa: 4111-1111-1111-1111 or 4222-2222-2222-2222
  • masterCardMasterCard: 5111-1111-1111-1118 
  • DiscoverCardDiscover: 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).

Aa479040.scalability_fig02(en-us,MSDN.10)

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.

 

 

 

 

 

 

Digg This

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 untitled 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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

this is posted from windows mobile y.

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.AgileCrapDevelopement 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.

Digg This

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

Digg This
More Posts Next page »