How to: Remove all users from the ASP.Net Membership database by application name
Wednesday, December 22, 2010 7:08:47 PM (GMT Standard Time, UTC+00:00)
![delete_key[1]](http://blogs.thesitedoctor.co.uk/tim/images/53bad6f4d949_DE2B/delete_key1.jpg "delete_key[1]")
A while ago I wrote about How to: Remove users from the ASP.Net membership database which showed you how to remove a single user from the ASP.Net membership database from SQL Management Studio. That's great when you're deleting one or two users but what if you're testing and need to delete all users associated with an application?
There's a little more work involved with that one but it's not too difficult, rather than passing in the member's id, you pass in the ApplicationName (same as you set in your web.config) and it will find the various users that match and remove them for you.
USE DatabaseName --This should be your database name
GO
BEGIN TRANSACTION
DECLARE @ApplicationName nvarchar(256)
SET @ApplicationName = '##YOUR APPLICATION NAME -AS SET IN THE WEB.CONFIG ##'
--Lowercase it so it matches LoweredApplicationName exactly
SET @ApplicationName = LOWER(@ApplicationName)
--Values in the aspnet_Profile table
DELETE p
FROM
dbo.aspnet_Applications a
INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
INNER JOIN dbo.aspnet_Profile p ON u.UserId = p.UserId
WHERE a.LoweredApplicationName = @ApplicationName
--Values in the aspnet_UsersInRoles table
DELETE r
FROM
dbo.aspnet_Applications a
INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
INNER JOIN dbo.aspnet_UsersInRoles r ON u.UserId = r.UserId
WHERE a.LoweredApplicationName = @ApplicationName
--Values in the aspnet_PersonalizationPerUser table
DELETE p
FROM
dbo.aspnet_Applications a
INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
INNER JOIN dbo.aspnet_PersonalizationPerUser p ON u.UserId = p.UserId
WHERE a.LoweredApplicationName = @ApplicationName
--Values in the aspnet_Membership table
DELETE m
FROM
dbo.aspnet_Applications a
INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
INNER JOIN dbo.aspnet_Membership m ON u.UserId = m.UserId
WHERE a.LoweredApplicationName = @ApplicationName
--Values in the aspnet_users table
DELETE u
FROM dbo.aspnet_Applications a INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
WHERE a.LoweredApplicationName = @ApplicationName
ROLLBACK TRANSACTION
Calculating what an employee will cost you
Monday, December 20, 2010 10:17:56 PM (GMT Standard Time, UTC+00:00)
As people are now looking to employ I thought it would be helpful to overview the general costs involved with employing someone in the UK and how you can factor that back to an hourly charge.
Some Assumptions
- As most of my readers are within the IT industry, I've based these figures on hiring within our sector
- For simplicity's sake, someone who is over 21 (minimum wage and the factors vary when employing someone younger).
- The employ won't earn over £844 (around £44,000pa) to avoid needing to account for different NI values (refer to Directgov for more information)
The calculations
I've created a spreadsheet for you which calculates the hourly cost for employees on various salary levels. It should be fairly self explanatory, if it's not, leave a comment and I'll explain as necessary.
Download: Hourly_Rates_Breakdown.xls
Other costs to consider
Once employed, there are a number of other costs that haven't been factored into the spreadsheet:
Downtime
It's unlikely that your employee will be working at full capacity (if they are you should consider employing another!) so it is important factor in some downtime within your calculations.
First Year
Although the process of employment doesn't have to be too costly by using free job sites and pre-written employment contracts, there is still an inherent cost with employing someone.
Think carefully about what you'll need to buy for the new employee -you will need to give them somewhere to work (i.e. a desk), something to use to do the work (i.e. a computer) and importantly somewhere for them to sit!
On-going
As everything in business needs to be broken down to a monetary value so here are some other things that you will need to factor into your calculations:
- Office space -apportion the employee's area of the office's rent
- Stationary -pens, paper and ink all costs
- Telephone
- Training/course fees
- Electricity
- Software and licenses
- Business insurance (if this is your first employee this is likely to increase substantially)
Conclusion
Breaking the salary down to an hourly charge should help give you confidence in being able to afford the additional resource. If you're working flat out at £50ph and finding that work isn't getting done, you can in theory employ someone at around £25,000pa and by keeping them busy still earn £55,594.66 (approximately!) yourself without needing to do any work. I'm sure you can see that by adding to your team and keeping them busy you can very quickly start growing your business.
It's also worth noting, when making a considerable investment such as employing someone, it would be wise to have a contract written specifically for your role.
Update: I've already had some great feedback on the spreadsheet courtesy of Sean Ronan from Active Pixels. He added a new table "Weekly billable hours needed to break even". This breaks the total cost of employing someone down into the weeks they can actually work. As they're unlikely to work 52 weeks a year, it works out the number of weeks based on the other information you entered. Great idea, thanks Sean.
SQL Server: Warning: Fatal error 823 occurred at date / time Note the error and time, and contact your system administrator.
Tuesday, December 14, 2010 12:46:42 PM (GMT Standard Time, UTC+00:00)
![error[2]](http://blogs.thesitedoctor.co.uk/tim/images/616c06524d21_ABF1/error2.png "error[2]")
We were contacted the other day by a client with issues selecting data from one of their tables after a recent server crash (not running on our servers or a site that we were involved in developing). The issue was easy enough to recreate as you just needed to select records after the server crash and you'd get the error:
Warning: Fatal error 823 occurred at date / time Note the error and time, and contact your system administrator.
A quick Google suggests a physical disk drive error and having a quick look at the issues it wasn't pretty. Running:
DBCC CHECKDB('DatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGSResulted in:
Msg 8909, Level 16, State 1, Line 5
Table error: Object ID 0, index ID 12341, page ID (1:5880). The PageId in the page header = (9728:16777220).
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.
....
DBCC results for 'TableName'.
Msg 8928, Level 16, State 1, Line 5
Object ID 871674153, index ID 0: Page (1:5880) could not be processed. See other errors for details.
....
There are 20993 rows in 584 pages for object 'TableName'.
CHECKTABLE found 0 allocation errors and 8 consistency errors in table 'TableName' (object ID 871674153).
Msg 8909, Level 16, State 1, Line 5
Table error: Object ID 1109413712, index ID 24940, page ID (1:5883). The PageId in the page header = (25198:1632843825).
CHECKTABLE found 0 allocation errors and 1 consistency errors in table '(Object ID 1109413712)' (object ID 1109413712).
Most of the solutions found on Google resulted in some form of system restore but that's no good in this instance as the backups only existed for after the problem was identified (great eh!) so were useless.
Although it's not an ideal solution, you can use DBCC CHECKTABLE which in our case fixed the issue:
--Put the database into single user mode
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH NO_WAIT
--Check the erors and fix any issues found (that you can)
DBCC CHECKTABLE ('Orders', REPAIR_REBUILD)
--Put the database back into multiuser mode
ALTER DATABASE [DatabaseName] SET MULTI_USER WITH NO_WAIT
I'd be interested to know other solutions people may have to this issue.
Note to readers: Check that your hosting provider performs regular backups and checks the health of your server regularly to avoid this happening to you.