Tuesday, May 01, 2007

ASP.Net Membership SQL Roles access

We have recently moved over to SQL Server 2005 and as part of this transfer I decided to aggregate two separate ASP.Net Membership databases that were created purely out of error.

For those of you who don’t already know, you can happily run more than application’s security from a single membership database as long as you correctly configure the web.config’s security settings –for more information on doing that see my post on having dual records in the ASP.Net authentication table (see: Dual Records In The ASPNet Authentication Table). The important attribute/value set to configure if you are planning on running more than one application from the same roles database is “applicationName”. If you do not set “applicationName” you will find that users can log in across all your applications, roles/access levels will get mixed up and a whole bunch of other hullabaloo!

Luckily for me, the only records stored in one of the membership databases were two users, both of which I knew the password to so I decided I would simply update the web.config with the new database connection string and add them manually.

The next thing I wanted to sort however was the specific SQL Login’s access to the membership database, previously I simply added the user to all the various aspnet_ roles that were in the database which worked fine. As I’m looking to use this database for other applications in the future and I don’t like sharing usernames/passwords across applications, adding the roles each time would become a real PITA so I decided to add a new role with all the access required for the database so I could simply add the user to this new role. I called the role IIS_User.

A number of our applications build on the foundation of the ASP.Net Membership database with application specific values and so I tend to have another table for the application’s users within the applications specific database to store these values. The user has the usual UserId (usually an int) and a uniqueidentifier which allows me to link the two database together. With this in mind, I need additional access to the ASP.Net Membership database –SELECT permission on the tables. I don’t like adding more permissions to a role than needed but I needed a method of doing this quickly –assigning EXECUTE and SELECT permissions to the new role on the various tables/stored procedures. In time I’ll revisit this and work out which are needed by the role and remove the permissions not needed but for now this’ll do :)

The quick and dirty T-SQL

DECLARE @SQL nvarchar(4000),
    @Owner sysname,
    @objName sysname,
    @Return int,
    @objType nvarchar(5),
    @rolename nvarchar(255)

SET @rolename = 'IIS_User'

-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT 
    USER_NAME(uid) Owner, 
    [name] StoredProcedure,
    xtype
FROM
    sysobjects
WHERE
(
    xtype = 'U'
  OR
    xtype = 'P'
)
  AND
    LEFT([name], 7) = 'aspnet_'

OPEN cursStoredProcedures

-- Get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @objName, @objType

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))

  begin...end

  BEGIN

    --Determine the object's type (table/stored procedure) -could 
    --be done using a case too if more objects are added later
    IF @objType = 'P'

    begin...end

    BEGIN
        SET @SQL = 'GRANT EXECUTE ON [' + @Owner + '].[' + @objName  + '] TO ' + @rolename
    END

    IF @objType = 'U'

    begin...end

    BEGIN
        SET @SQL = 'GRANT SELECT ON [' + @Owner + '].[' + @objName  + '] TO ' + @rolename
    END

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @objName, @objType
  END

-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)

  begin...end

  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END

ELSE

  begin...end

  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
    
    -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @objName + ']'
    RAISERROR(@SQL, 16, 1)
  END

GO

ASP.Net Membership SQL Roles access

Useful Links:            Comments [0] 

Tags: ASP.Net | SQL Server | Web Development

 Thursday, April 19, 2007

Getting more work done - Early Mornings vs. Late Nights

If you’ve ever been involved with running your own business or indeed any start-up you’ll know long hours are sometimes a necessary evil to meet the demand or even just getting things in order. Well for quite some time now I’ve been wanting to work out whether it’s better to work early in the morning or late at night –I’m quite happy at working either but is there a better option.

Recently I’ve needed to work long hours to get a couple of systems completed and get back on track with a couple of projects so decided there wouldn’t be a better time. I did want to do something clever like monitor the number of lines of code I write or something equally measurable but to be quite honest I didn’t have the time and I couldn’t be bothered to spend hours searching for a solution so it’s all finger in the wind stuff…

Late Nights

Firstly I tried late nights. As I mentioned in my business start-up guide (see: Business start-up advice) I’m very strict with getting up –I get up when Stacey does, see her off and then I’m in the office from around 7:30am/8am (please don’t call then –I like to have a little “quiet time” to sort things out ;)) and I work until she gets home –usually around 7pm now she’s in her new role. When doing the late nights I’d go in at around 7pm, have a drink and a chat, a bite to eat, perhaps all in all an hour or two’s rest before returning to the desk for a couple more hours.

What I found with working into the night was that by the time I got back into the office I was already fairly tired from the day that had preceded (which a lot of the time is unpredictable as far as clients are concerned!) so my productivity for these additional 5/6 hours was lower than during the “normal” working day, then when I got up the next morning I would be pretty shattered from having little sleep (I still had to get up at 6:30am) which meant the next day was somewhat of a write off. So although I managed an additional 5 or 6 hours of lower productivity work, I also lost time the next day so it was semi-even.

Early Mornings

After testing working late for a couple of weeks I decided to try getting up early –initially I would get up at 5:30am as that would give me an hour or so before Stacey was awake, then 10mins waking her/refilling my tea and then another hour or so before breakfast and seeing Stacey off. I tried the 5:30am start a couple of times and could see the productivity difference almost straight away. As I was starting after a good nights rest (I could finish earlier and even get to bed earlier if I wanted!) I was fresh and ready to go again so I decided to start getting up at 5am, that way I could have nearly 4 hours of uninterrupted time before clients started calling.

By getting up at 5am you avoid the jet lagged feeling at the end of the day, oddly enough this still comes at around the same sort of time so you don’t really lose out there and the 4 hours or so before other people make it into the office which means you get an additional 4 hours of uninterrupted time.

The other advantage was if you had a deadline to meet you also had the evening available to work into –again at a fairly similar productivity level to when you get up at 6:30am.

Conclusion

Although I’ve not done any productivity level testing I know for sure that getting up in the mornings is far better without a shadow of a doubt, having the uninterrupted time where you’re fresh and alert means the development is a lot faster, unlike in the evening/night, no-one is online just “chilling” which a lot of the time can be distracting in itself, you’ve also got the added advantage of having a full nights sleep if required or working late into the night if you need to hit a deadline.

Despite my findings I still know a lot of people that stay in the office late into the night thinking that it’s of benefit but one thing I found was not only was your productivity lower, your likelihood in making mistakes was higher –which you’d just have to correct the next day. A lot of the time they were simple things that had you been paying attention/been awake for you’d have sorted!
Another thing I would like to look into at some point is whether morning or afternoon coding is more efficient and whether you can use that to structure your day better as from this “investigation” I’m inclined to do more adminy type work in the afternoon as I tire and leave the development to the morning (that is my 8 hour morning… ;))

FWIW I really enjoy getting up before everyone else, watching the sun rise and then having an evening to go to the gym and have been getting up early now for a fair few weeks –and don’t see it changing anytime soon! I think any earlier than 5am however may well be detrimental.

Getting more work done - Early Mornings vs. Late Nights

Useful Links:            Comments [0] 

Tags: Business | Business Start-up Advice | The Site Doctor

 Tuesday, April 17, 2007

SQL Server Delete all data

I expect many people already know about this technique but I wanted to share it with those that don’t. The other day I needed to remove all data from a database before importing data from another database. I usually use DTS to copy the data across but knew that the database (one test) had conflicting ids so I decided deleting all the data out of the test database would be the best way to ensure all data’s up to date.

I found this useful little set of SQL at: http://sqljunkies.com/WebLog/roman/archive/2006/03/03/18386.aspx, there are two solutions propsed within the post and comments so here they both are:

Delete the data without resetting the identities

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Delete the data and reset the identities

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
GO

SQL Server Delete all data

Useful Links:            Comments [6] 

Tags: SQL Server | Web Development

 Wednesday, April 11, 2007

Rackspace -awesome managed dedicated servers

Seeing as I’ve had a number of posts recently that were, well, er slating/pointing out terrible customer service and/or service in general I thought it was about time I posted about the guys that did it right in my eyes (that and a couple of seriously cool programming things I’ve found/done –but those can wait!).

If you read my recent post about Fasthosts and how they’ve been going down hill for the last year or so you’ll already know that we’ve signed up to one of the worlds greatest managed hosting companies ever –Rackspace. Not only that, we also recently became an official Rackspace partner which I’ll come back to in a moment.

We got our first dedicated server with Rackspace nearly two years ago as part of a large project we had in the pipeline, although cost was a large factor in our decision, we took into account other aspects of the service such as how our enquiries were handled, the reputation of the hosting company throughout the development community etc. Rackspace although they were one of the more costly solutions for our requirements came off best all around. Even before we were signed up, Rackspace’s customer service was second to none, they were quite happy to discuss the more technical aspects of our requirements and even worked closely with us to identify the most suitable solution.

One thing that I really liked about Rackspace before we signed up was the fact that they were very open and honest –where the sales person didn’t know the answer, they felt perfectly at ease to say so and pass you over to one of the technical support staff who always knew the answer. From my POV, not having someone “guessing” the answer is incredibly important –even more so when mission critical applications are being put in place.

One thing that was very important to us when signing up was scalability, at the time, the requirement for the server was relatively low (the application was still in the pre-development stages but we knew we had to get a dedicated server in place before it got to the develop stages) so it was a risk that we needed to take but we also needed to limit the risk involved. Rackspace were able to offer the backbone (and supporting evidence) that we were looking for. They were quite happy letting us have a smaller solution to start with which could then easily be upgraded when we needed to –within a maximum of 24 hours notice!

Another key factor was the on-going customer help and support, not having any real-world prior experience in managing a server I was adamant that whoever we hosted our server with had to also be able to offer us the technical support we would no-doubt be needing –not only with getting started but also looking to the future general day-to-day cock-up’s. Rackspace has a motto of “Fanatical support” which although many companies claim similar was clear they really believed in. Unlike many other hosting providers out there Rackspace had dedicated support specialists in the various aspects from Window Servers, Linux/Apache Servers to IMail specialists. This has saved our bacon on many an occasion. Although you may get through to someone who is specialised in another area, if they don’t know the answer you’re put through to the next available support member almost immediately. It certainly beats speaking to some spotty teenager who kinda knows what you mean when you talk about the web but is more interested in the Yankee dollar for a day of picking up the phone and pissing people off.

Since signing up with Rackspace, having our dedicated server managed by Rackspace has been a dream. Like I’ve said, they are expensive but that cost is more than compensated by the complete lack of stress I have when dealing with them, the solution they recommended has met our needs perfectly so I’m pleased we’ve not been spending out unnecessarily. On top of this, Rackspace’s motto of “Fanatical support” is an understatement, their customer service and support is second to none without a shadow of a doubt. I’ve called them at some very odd hours and there has always been a friendly understanding voice at the other end… “that’s ok Tim, we can restore that folder you deleted before your client notices…” ;)

In the two years I’ve been with them I’ve had three issues with the server:

• A HDD failure –not sure what it was beyond that, IIRC it was just a dodgy disk (manufacturers fault). Rackspace notified me of the issue, replaced the HDD and ensured all the data etc was how it should be in the space of 15minutes! As the RAID setup meant the server continued to be operable while Rackspace were sorting the issue I wouldn’t have known if they hadn’t had called me.
• Power loss for approximately 1hours. Shortly after our server was moved into a new data centre we lost all connectivity. When we called Rackspace about the issue we were told that a capacitor had blown between the server room and all power supplies. We later found out that they had used a new company to install the equipment and they hadn’t checked it all in as much detail as they perhaps should have done. At the time we only had a couple of sites on the server so I wasn’t too concerned about it but Rackspace refunded something like 10% of the entire month’s hosting bill for the inconvenience.
• Failed firewall. This only happened the other day but the firewall died for some reason, at first they thought it was the PSU but on replacing that it still wasn’t rectified. All sites were down for about 40minutes while it was being sorted. Not great but unavoidable and at least they sounded genuinely concerned. I was even kept in the loop throughout and knew exactly what was going on –right down to “the tech is running to the storage area now to get the PSU… …now he’s running back” (I don’t think my comment of “Run forest RUUNNN” went down as well as I had hoped!).

On top of those issues Rackspace have also helped us out on countless occasions where we weren’t quite sure if we were going about something the right way or we’d messed something up, I’ve only had to use the restore facility once IIRC but that was simple, within 20mins I had restored around 3GB of files to their state from the night before.

Mainly through sheer laziness and not having a re-useable statistics package on the dedicated server (I don’t need it as we analyse the raw logs) until recently the majority of our web sites remained on a Fasthosts shared server account and seeing as a shared server account has many limitations in regards what you can do with your programming and the terrible customer service we’d been receiving we recently transferred the majority (not all) of our web sites away from Fasthosts onto our shiny Rackspace server.

The transition was so simple and easy it was an absolute pleasure. You manage your DNS settings through their online portal which also has a very detailed and easy-to-use support ticketing system. Once setup in the portal you setup the domain on your server and away you go so I’m one happy bunny. They even helped install (unsupported) software for statistics analysis (without any additional charges) and helped me remove the quotas I had applied to the C: drive by accident. As if that wasn’t enough Rackspace even took the time to talk through some of the finer points of server management with me.

What’s this about being a Rackspace partner? Well, you may think that my judgement is swayed by the fact that we were recently asked to be partners with Rackspace but I really wish I’d written about them before this because they are genuinely a superb company and I don’t want you to think that I’m just saying that for the commission (though that is an added bonus I must admit!). If you want unparalleled customer support and a managed hosting solution that really is there all the time for you then choose Rackspace –I’m yet to fault them (and having seen what it gets me, I can’t fault them on the price as it’s well worth it for the help and support they’ve offered me in the past).

In many ways, Rackspace and the Rackspace support team are very much part of my team, I know they’re always there and unlike other people in my team are always happy to hear from me (at the start of the call at least!).

Rackspace, Rackspace, Rackspace, oh how I love ye! If you’re thinking about setting up a server with Rackspace let me know and I’ll put you in contact with people that can help I definitely recommend Rackspace as your hosting partner even if it's just for an easier life :)

Oh, and if all this wasn’t enough –they even send you a bunch of freebies for being a customer and a 12month congratulations certificate! –Cheesy I know but it’s the thought that counts! –and I use the free 256MB key they sent me as part of becoming a partner to store my blog posts on the go now* so thanks for that. Note to any supply company -I (and I think anyone else out there) LOVES free stuff so send it over... ;)

*Yes I know I could have got several hundred 1GB disk keys for the price I pay on hosting but then I wouldn’t get to talk to the lovely support staff ;)

Rackspace -awesome managed dedicated servers

Useful Links:            Comments [0] 

Tags: Business | The Site Doctor

 Friday, March 30, 2007

Discounts on organic aromatherapy products including essential oils and more

I don't want to be seen to be using my blog as another way of pimping out my client's services to my lovely readers -I'm really not, it's just that I've been so busy recently I've not had a chance to finalize some of the content that I'm going to upload shortly. In the mean time though, if you're into aromatherapy -or more to the point organic aromatherapy check out Florame organic aromatherapy's special offers on all sorts of great items.

Here's the email (there's no need to use a special offer code with this one)

Discounts on organic aromatherapy products including essential oils and more

Useful Links:            Comments [0] 

Tags: Client | The Site Doctor

 Friday, March 23, 2007

Threshers do it again -another 40% discount voucher! This time for Easter

As you're probably aware, at Christmas the Threshers Group put out a 40% discount voucher for the Threshers Group, well it would appear they’ve decided to do it again so I thought I should share it again, as before, the Threshers Group 40% Easter discount voucher can has a couple of T&Cs including no Champagne, Sparkling or Fortified Wine, other than that, it doesn’t appear to have the same £500 limit as before so drink up guys and girls!

Download the 40% Easter discount voucher for the Threshers Group

The voucher is valid until 1st April 2007

Remember: If you're looking for some naughty fun this Easter, check out the Miss Mays Adult Store for all sorts of adult toys and games

Threshers do it again -another 40% discount voucher! This time for Easter

Useful Links:            Comments [0] 

Tags: Fun and Games | Random | WebDD

 Wednesday, March 21, 2007

Simple accounting database

As part of my mini-series on Business start-up advice I posted a simple accounting database (see: Finances (VAT, Accountants etc)) but today I realised I had just uploaded the mdb which won't be served by IIS so I just wanted to let you know I've uploaded it as a zip (incase you were one of the people that tried to download it).

Example Microsoft Access Accounting Database (21KB)

Simple accounting database

Useful Links:            Comments [0] 

Tags: Business | Business Start-up Advice | The Site Doctor | WebDD

 Monday, March 19, 2007

How to use Phil's error reporting code

I’ve done a number of posts now on Phil Whinstanley’s error reporting class and this blog appears to be getting a lot of hits because of that which is pretty neat, as a result I’ve had a couple of people write to me asking similar questions about the code so I thought it would be an idea to write a little summary.

Where can I download the code?

It would appear that most of the old copies of Phil’s code have disappeared from the web, I’m not sure why so I’ve uploaded the versions I’ve got below. For convenience I have compiled the code into DLLs for those that don’t know/want to do this and I’ve also included the Visual Studio solutions. I’m not sure if these are based on the original codebase but I don’t think I’ve made any major alterations to these versions:

• The original WebException v2 (62KB)
• WebException v4¹ (20KB)

¹ This is a version I was sent as his original including changes and example email was lost...

DLLs only:

• ASP.Net 1.1 DLL only (17KB)
• ASP.Net 2.0 DLL only (16KB)

If you have Visual Studio:

• ASP.Net 1.1 (Visual Studio 2003) version without DLL (24KB)
• ASP.Net 2.0 (Visual Studio 2005) version without DLL (26KB)
• ASP.Net 1.1 (Visual Studio 2003) version with DLL (131KB)
• ASP.Net 2.0 (Visual Studio 2005) version with DLL (59KB)

If you don’t have Visual Studio you can either download one of the above projects and delete the solution/project files or download the original WebException code. Ok, now you have the files :) -FWIW I can accept no responsibility for any of the files or the code, I just zipped them!

How to do I use the WebException class?

I’m now using a slightly modified version of the code to enable error reporting within AJAX (see: Reporting errors from AJAX using the WebException Class) which I’ll try and upload later but whichever version of the code you choose the use is pretty much the same.

Once you have referenced the DLL in your project (see: Importing/Referencing DLLs in Visual Studio) you will be able to use the WebException. As I’ve covered what you need to do to use the code from within an AJAX application in another post (see: Reporting errors from AJAX using the WebException Class) I’ll just cover how to use it to report global errors. To capture and respond to all application errors you will need to place this code within the global.asax, your project should automatically have one, if it doesn’t then you will need to add one.

Using the global.asax file, the first thing you need to do is add a reference to the DLL at the top of your code (this will allow you to call the methods and access the properties):

<%@ Import Namespace="ErrorReporting" %>

Next locate the Application_Error event handler, this is the method that handles all errors within the application (with exception of those thrown from within an AJAX application, read this post to report errors from within an AJAX application). Now replace your Application_Error and Application_PreRequestHandlerExecute handlers with (for more information on what I'm doing here see: ASP.Net WebException and Error Reporting useful code):

void Application_Error(object sender, EventArgs e)

{...}

{
    bool reportErrors = Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["SendErrors"]);

    if (reportErrors)

    {...}

    {
        Exception currentError = Server.GetLastError();

        Deal with 404's

        #region Deal with 404's

        //Redirect the user to a friendly page
        if(CheckForErrorType(currentError, "FileNotFound"))
            RedirectToFriendlyUrl("");

        #endregion

        Deal with Spambots

        #region Deal with Spambots

        if (CheckForErrorType(currentError, "System.FormatException"))

        {...}

        {
            if (HttpContext.Current.Request.Form.Count > 0)

            {...}

            {
                foreach (string key in HttpContext.Current.Request.Form)

                {...}

                {
                    if (key.IndexOf("_VIEWSTATE") > 0 && HttpContext.Current.Request.Form[key].ToString().IndexOf("Content-Type") > 0)
                        return;
                }

            }

        }

        #endregion

        //Enable the trace for the duration of the error handling
        TraceContext t = HttpContext.Current.Trace;
        bool bCurrentState = t.IsEnabled;
        t.IsEnabled = true;

        Handle the Exception

        #region Handle the Exception

        ErrorHandling.WebException WE = new ErrorHandling.WebException();
        WE.CurrentException = Server.GetLastError();
        WE.MailFrom = "you@yourdomain.com";
        WE.MailTo = "you@yourdomain.com";
        WE.MailAdmin = "you@yourdomain.com";
        WE.Site = "Your Site's Name or URL";
        WE.SmtpServer = "localhost";
        WE.FloodCount = 10;
        WE.FloodMins = 5;

        #endregion

        Choose what you're interested in

        #region Choose what you're interested in

        WE.ReturnCache = true;
        WE.DrillDownInCache = true;
        WE.IncludeApplication = true;
        WE.IncludeBrowser = true;
        WE.IncludeEnvironmentVariables = true;
        WE.IncludeForm = true;