Stored Procedure to assign permissions
Monday, October 09, 2006 3:42:49 PM (GMT Daylight Time, UTC+01:00)
This is a useful stored procedure for assigning permissions to users quickly and easily. We tend to assign a new login to each application we develop, this way we limit the damage possible in the event of a username/password compromise.
/*--------------------------------------------------------------------------
Automatically assign the role permissions
--------------------------------------------------------------------------*/
USE DatabaseName
SET NOCOUNT ON
DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE @username varchar(100)
SET @username = 'UserNameToAssignPermissionsTo'
DECLARE grant_perms_on_sps CURSOR FOR
SELECT name, type
FROM SYSOBJECTS
WHERE
(
(type = 'P')
OR
(type = 'FN')
OR
(type = 'TF')
OR
(type = 'U')
OR
(type = 'V')
)
AND
uid = 1
AND
status > -1
AND
LEFT(name, 3) <> 'dt_' --See Note 1
OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps
INTO @objName, @objType
WHILE @@FETCH_STATUS = 0
BEGIN
IF @objType = 'P' OR @objType = 'FN'
BEGIN
EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO ' + @username)
PRINT ('GRANTED EXECUTE ON dbo.' + @objName + ' TO ' + @username)
END
IF @objType = 'TF'
BEGIN
EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO ' + @username)
PRINT ('GRANTED SELECT ON dbo.' + @objName + ' TO ' + @username)
END
FETCH NEXT FROM grant_perms_on_sps
INTO @objName, @objType
END
CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps
GO
------------------------------------------------------------------------
Note 1: In addition, we tend to prefix our database objects with useful prefixes to group relevant tables, i.e. if we had login information stored in the database we may use “Login_” as the prefix, using this method with this Stored Procedure to assign permissions you can easily select the relevant objects. So you could alter the stored procedure a touch:
DECLARE @prefix varchar(100)
SET @prefix = 'PrefixToUse'
LEFT(name, LEN(@prefix)) = @prefix
I’m a bot! –According to Google Anyways
Monday, October 09, 2006 9:51:17 AM (GMT Daylight Time, UTC+01:00)
While fitting my new chipset cooler fan this morning I was checking out an error message on Google (which as it turns out I knew the answer to) and I was presented with a CAPTCHA image –I guess Google’s getting a lot of automated requests. This was using the built in Firefox search box but then I got it again on the PC using Google’s search box!
String.Format and Input string was not in a correct format Error
Monday, October 02, 2006 6:26:29 PM (GMT Daylight Time, UTC+01:00)
I ran into (another) interesting error today when using String.Format1. I was getting the error message "Input string was not in a correct format.". In this instance I was calling the content from an XML file, the data was wrapped in CDATA tags so there shouldn't have been an issue with line breaks etc. On investigating the error further I found it was being caused because the content was a HTML page. More specifically, the String.Format error "Input string was not in a correct format." was being caused by the CSS declaration's curly braces.
To work around the method perceiving them as placeholders, simply replace each curly brace "{" with double braces "{{".
To replicate the error:
String.Format("<style type=\"text/css\">p{font-size: 1.2em;}</style><p>Your username is: {0}</p>", "UserName");
The fix in place:
String.Format("<style type=\"text/css\">p{{font-size: 1.2em;}}</style><p>Your username is: {0}</p>", "UserName");
1The String.Format method accepts a format string which can include place holders (designated by the curly braces {} and a number that refers to the location of the item that should appear in the list). A quick example of String.Format:
String.Format("This is some text the date is {0}.", DateTime.Today.ToShortDateString);
Would produce: "This is some text the date is 02/10/2006"
Useful Visual Studio Plugin
Saturday, September 30, 2006 7:13:45 PM (GMT Daylight Time, UTC+01:00)
On surfing around the net the other day looking for a replacement to Visual Studio 2003’s clipboard monitor I stumbled across this excellent Clipboard manager plug-in for Visual Studio 2005. As his blog was offline at the time I wasn’t sure exactly how to use it but now I’ve had a play I’m not sure I’ll be able to do without it!
Check out the Clipboard Manager plug-in at
http://www.csharper.net/blog/clipboard_manager_upgraded_to_package.aspx
For those of you interested, the plug-in monitors the clipboard activity allowing you to resurrect previous clipboards and make them current and even locking items so you can use them at a later date. I’ve found it incredibly useful when testing sites as it allows me to keep common messages on the clip. Downside is you have to have Visual Studio open at the time so I’ll have to look into a standalone version.
Here are a few shots of it in “action”:
The items that have been added to the clipboard in the past show up in the list at the top and there's a small preview window below (which I think you can select parts out of). You can also remove all items from the history by clicking the icon in the top left.
To re-select an old item double click it and it's instantly the main item! The currently selected item is the one in black text with a green arrow next to it.
The context menu offers a number of extra options including the ability to lock and unlock an item, this means when you restart your computer the item is still in the history -great if you have common items such as test credit card numbers! If you want to remove a single item from the history, you can do that using the context menu too. I've not yet used the "Save to File" or "Search Online" items.
Update: There does seem to be some sort of glitch with it, I think my history has got corrupt at somepoint so when I clear all the unlocked items new items weren’t getting caught anymore. I've found two solutions: The first is to restart Visual Studio, the other is to unlock all items and clear the history completely. This seemed to sort it. I did also note that I had one blank item at the top of the list so I guess that's what was causing it.
IIf Issues
Saturday, September 30, 2006 1:39:47 PM (GMT Daylight Time, UTC+01:00)
Sean Ronan (from Activepixels.co.uk) was asking me the other day why he was receiving a “Cannot convert ‘’ to DateTime” when writing:
IIf(String.IsNullOrEmpty(DateOfBirth.Text), Nothing, CDate(DateOfBirth.Text))
Thanks to Doug Setzer from 27Seconds in pointing him in the right direction, unlike C#’s ability to write:
(TrueFalseStatement ? TruePart : FalsePart)
VB’s IIf is a function not a statement so unlike C#’s equivielent which is run un-evaluated, VB’s IIf function evaluates both sides of the statement regardless of whether the statement is true of false.
Yet another reason to use C# IMNSHO 
Remembering your age
Friday, September 29, 2006 11:35:52 AM (GMT Daylight Time, UTC+01:00)
Ok, I think this has to be the most random post yet, I'm forever forgetting how old I am so popped onto Google and got a little calculator to tell me, the result was 24 (I was pretty darn sure about that already) but what I did find out was that I was born on a Thursday, thinking about that old Wives' rhyme I had to find out what I am:
- Monday's child is fair of face,
- Tuesday's child is full of grace,
- Wednesday's child is full of woe,
- Thursday's child has far to go.
- Friday's child is loving and giving,
- Saturday's child works hard for a living,
- But the child born on the Sabbath Day, Is fair and wise and good and gay.
So apparently I've got far to go, I hope it means I'm going to do well in life rather than I'm going to have to travel far but only time will tell!
Giant Bug Invades Germany
Friday, September 29, 2006 9:08:57 AM (GMT Daylight Time, UTC+01:00)
I was sent this link by Craig this morning, it's most ammusing and yet another reason not to goto Germany... :)
http://maps.google.com/maps?hl=en&t=k&q=Germany&ie=UTF8&z=18&ll=48.857699,10.205451&spn=0.002404,0.006738&om=1
It got me wondering how they take these photos, I always assumed it was digital sent down to NASA (or similar) but having an in-focus insect on the shot is most intreging, even if it was crawling across during post-processing it would be black and not in focus.
Perhaps there really is a 50m bug in Germany, or perhaps it's just another rouse like the guy that wrote f**k in the field.
National Canoe Finals Bedford
Thursday, September 28, 2006 8:28:27 PM (GMT Daylight Time, UTC+01:00)
For those of you interested, the photos from the Hastler Finals (National Canoe Finals) are now online, taken a few days but oh well that's life 
Photos from the Bedford Finals - http://www.thesitedoctor.co.uk/photoalbum/default.aspx?fld=photos/Canoeing/Hastler%7EFinals%7EBedford%7E2006_09_16_17
Again it's on the old album for now so I apologise.
On the whole the race went ok, Sam paddled very well but I had to spend most of my time bracing against the incredible wash -at times the water was coming over the front of the boat and into Sam's cockpit. Pat and Paul had a bad experience with the start gun going before they managed to get to the line -despite my comments to the starter that not everyone was there. The organisation of the race was terrible, the "accomodation" (one toilet) wasn't great which was a shame but all was soon forgotten when we were off the main canal without the wash it was a nice race.
We're still awaiting the results but I'll post them here when they're online.
SQL Server a memory hog? No, not at all
Thursday, September 28, 2006 7:12:59 PM (GMT Daylight Time, UTC+01:00)
Craig found an amusing dity today, he was messing around with the settings in SQL Server Express and noticed these memory settings:
For those of you who don’t want to deliminate it, that’s:
- 2,147,483,647 Megabytes
- 2,147,483 Gigabytes
- 2,147 Terabytes
- Or 2 Petabytes!!
One does have to wonder what sort of super computer Microsoft are expecting to have run the database. It's certainly future-proof that's for sure. I'd love to have 1TB of RAM, let alone 2PB! So the next time you wonder what’s taking up all the memory, best check SQL Server –you might just find that lost Petabyte…
BTW if this blog post is still around when Petabytes are as common as Mega and Gigabytes are now then feel free to lean back in your chair and recollect “the good ol’ days” when computers only had 200-300GB of disk space and a couple of Gig’s RAM.
Thailand Photos
Wednesday, September 27, 2006 12:32:15 PM (GMT Daylight Time, UTC+01:00)
Ok, I've just had the chance to go through the photos we sent off to the printers and organise them for the photo album. For now I've left the photos on my
old photo album but it works so... :)
Enjoy these
Thailand Photoshttp://www.thesitedoctor.co.uk/photoalbum/default.aspx?fld=photos/Thailand~Photos~2006_07_07_22Don't forget to read the articles about our trip, I've still got to complete the ones about Bangkok but the rest are online now :)
Oh and I also post-dated another article I wrote before leaving but didn't have the chance to post:
The
scourge of Google and public facing blogsOver the next few weeks I'm going to get the rest of the articles I've written online so don't forget to check the archives.
Tim
Long time no blog
Tuesday, September 26, 2006 9:47:16 PM (GMT Daylight Time, UTC+01:00)
I wish I could say I’ve not posted anything for a while because I’ve been burning litres of fuel in the RX-8, sadly however that’s not the case, although I have done nearly 2,500miles in it and spent something silly like £600 in fuel, but I’ve not posted anything for a while because I’ve been up to my eyeballs in work.
Those of you that have spoken with me recently know that not only is The Site Doctor currently going through a few alterations, but we’re also in the process of moving house, and in addition to this we’ve got a fair few projects going on -both paid and unpaid so watch this space for updates!
For those of you interested, I’m still working on the articles about SEO and eCommerce which I hope to get online soon as well as a few more updates re life the universe and just about everything else.
As for you Doug, thanks for your comments the other day, I'm working on it, but unlike you Yanks we don't all not work at work. Well, that is except for Craig...
Cisco VPN Software and Local Network conflicts
Friday, September 22, 2006 10:24:42 PM (GMT Daylight Time, UTC+01:00)
This is a post for anyone else having connectivity issues with their LAN while connected to a VPN of some sort. When setting up the Virtual Private Network (VPN) on our new server we encountered a number of issues of not being able to connect to the local network while the VPN was established. Initially it was merely an inconvenience and something that we could live with, but as time went on we needed to stay connected and access files from the local network, it grew more and more irritating.
After much searching on the internet for a solution with terms like “Cisco VPN Local Area Connection Issues”, “Cisco VPN LAN Issues”, “Cisco VPN LAN files” and various inflections of we didn’t find anything useful.
While trying to sort the issue, I noticed that the VPN was establishing itself on the subnet of 192.168.1.x, that in itself is not a problem but we were running our LAN under the same subnet. It was only then that we realised that we had a conflict as the VPN was overriding the local subnet so on trying to access 192.168.1.5 it would attempt to contact the remote server group (which didn’t exist).
If you’ve got the same issue, the fix is simple, all you need to do is change your local subnet to something else i.e. 3, this means when the VPN is connected it will connect over 1 and the LAN will remain unaffected –lovely!