Grant permissions to all stored procedures in a database

SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope.

What it means is that we can issue a statement like the example below and this will GRANT execute permissions on ALL existing stored procedures and scalar functions AND all subsequently created ones.

GRANT EXECUTE TO [myUser] AS [dbo]

If you only make use of stored procedures to access your database and have not implemented a complex security model, this is probably the easiest and cleanest way to manage access rights.

If users' Windows accounts are used to connect to the database, you will prefer creating a role like db_storedprocedures_executor and assigning it the EXECUTE permission. Then it is very much like the existing fixed database roles such as db_datareader.

Implementing a self-signed certificate

It is a must to be able to generate certificates in order to test various security scenarios like running HTTP over SSL, message signing and/or encryption.

The .Net Framework SDK is shipped with a command line tool makecrt.exe that can create a self-signed certificate. This certificate can then be used on a developer's workstation or testing server.

Note: This should not be used for Production purposes. You can buy certificates from various entities.

The following command can be used to create a self-signed SSL test certificate:

makecert -r -pe -n "CN=www.yourserver.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

To install this certificate in IIS (5.x, 6.0), open the IIS Management console:

• Right-click on your site (e.g. Default site) and select Properties
• Open the tab "Directory Security"
• Click the "Server Certificate..." button, pass the first screen of the wizard
• Choose "Assign an existing certificate"
• Select the newly generated certificate from the list and click Next until the end of the wizard

SSL is now enabled in IIS!

Note: Older versions of makecert.exe do not support the "-pe" option, which makes the private key exportable. If you have an old version of makecert.exe, you can omit the "-pe" option, but then the certificate cannot be exported including the private key.

The testing certificate above is also known as self certificate. Self certificates are not "trusted" by your computer or browser, which maintains a list of trusted authorities.

A certificate issued by a trusted authority is a trusted certificate.

When using a testing certificate, you will get security warning in your browser warning you that the certificate is not a trusted one. Moreover, if you force SSL on your VS.Net project you may not be able to open it if it uses a testing certificate.

The only way to get around these issues is to add your testing certificate to the list of trusted authorities.

• Open a command prompt and run the MS Management Console by typing mmc and enter
• Click File and Add/Remove Snap-in
• Click the Add button and choose Certificates
• Choose "Computer Account"
• Select the local computer
• Open the Personal \ Certificates
• Copy your testing certificate to Trusted Root Certification Authorities \ Certificates (Drag and Drop + Ctrl key)

UPDATE: IIS7 offers a new feature to create self-signed certificate easily. The only issue is that you cannot set all the certificate properties.

Self Signed Certificate in IIS6

You can use the IIS 6 Resources Kit to generate and install a self-signed certificate with the SelfSSL.exe command line tool.

The IIS 6 Resouces Kit is available on the Microsoft.com website: http://go.microsoft.com/fwlink/?LinkId=34407

Note that you should use a self-signed certificate when you need to troubleshoot third-party certificate problems or when you need to create a secure private channel between your server and a limited, known group of users, such as exists in a software test environment.

Follow this step in order to generate and install the self-signed certificate.

1. Create a virtual site (or use the one on which you want to install the certificate) and set up SSL (default port is 443)


2. Launch the SelfSSL tool (Start Menu All Programs IIS Resources SelfSSL SelfSSL Prompt)


3. Run the following from the prompt replacing the /N:CN with your DNS name and the /S parameter with the IIS site Id

SelfSSL /N:CN=dnsname.mydomain.org /V:365 /S:siteId /P:433

Note: If you create a SSL certificate for the main IIS site, you can omit the /S, else the site ID can be found from the IIS Manager console)

SelfSSL command help

Installs self-signed SSL certificate into IIS.
SELFSSL [/T] [/N:cn] [/K:key size] [/S:site id] [/P:port]

/T Adds the self-signed certificate to "Trusted Certificates" list.
The local browser will trust the self-signed certificate if this flag is specified.
/N:cn Specifies the common name of the certificate. The computer
name is used if not specified.
/K:key size Specifies the key length. Default is 1024.
/V:validity days Specifies the validity of the certificate. Default is 7 days.
/S:site id Specifies the id of the site. Default is 1 (Default Site).
/P:port Specifies the SSL port. Default is 443.
/Q Quiet mode. You will not be prompted when SSL settings are overwritten.

The default behaviour is equivalent with:
selfssl.exe /N:CN=MYSERVER /K:1024 /V:7 /S:1 /P:443

Resetting security after restoring a database backup

When you restore a database from one server to another (i.e. restoring from Production to QA or Development), the user permissions of SQL logins are not automatically reset even if a user with the same name exists (note that this does not apply if you use Windows authentication)

In order to reset the user permissions, you can use the system command sp_change_users_login.

When passing REPORT as a parameter, it will list all the orphan users.

sp_change_users_login REPORT
GO

You can then re-assign the orphan users to existing users by using the following command:

sp_change_users_login UPDATE_ONE, 'ORPHAN_USER', 'LOGIN'

Example:

sp_change_users_login UPDATE_ONE, 'production_account', 'qa_account'
GO

Resetting SQL identity columns

If you're somehow perfectionnist as I am, you probably want to reset the your identity columns when deleting all records from a table.

With SQL Server, you can do so by using the DBCC CHECKIDENT command.

DBCC CHECKIDENT ('MyTable', RESEED, 0)

Now if you want to reset the identity of a table that still contains some rows, you must reseed it to the last number used.

DBCC CHECKIDENT ('MyTable', RESEED, 123)

Edit Lotus Notes document without an agent

If you manage Lotus Notes application, there is always a time when you will need to edit a document to change a value. That say, you are not always able to do so even with full administrator rights (the application may not allow you to do it via the user interface)

One solution is to create a simple agent that will do the work for you but it ends being painful!

Another solution is to use the very useful tool "Edit Document Fields"

http://www.chadsmiley.com/chadsmiley/home.nsf/htdocs/Edit_Document_Fields_History

The tool is setup by adding a new button to your Notes toolbar and sets its formula to the code given. You're now all set and can edit any field in any document!

Note that there are version for R4.x, R5, R6.x and R7

Discovering Writely

I just discovered the Writely project, which the Google online word processing project.

It is actually very good, providing with most of the features you are looking for in a word processing tool.

It is certainly not as good as a full-blown desktop software but provides some other features like online reviews with other users.

Other cools features are the capabilities to save to other standards formats (Word, Open Office but also PDF !)

And finally it's completely free...

Visual Studio 2003 SP1 finally released!

For those like me who have not yet fully migrated to Visual Studio 2005, MS has finally released the very long-waited service pack for Visual Studio 2003.

The list of bug fixes can be found at http://support.microsoft.com/?kbid=918007

The SP1 fixes a large number of issues, which were bugging us every day. One regret is that it took 3 years for Microsoft to get it out. Let's be positive and say that it will make my last months of work with VS 2003 less painful before I move completely to VS 2005...

Int32.Parse() vs Convert.ToInt()

What's the difference between Int32.Parse() and Convert.ToInt()?

That's a good question, isn't it? At least, that's a question that I often hear.

Int32.Parse converts a string variable (or some other valid types) into a variable of type int. However if you pass a non-recognized value (text string, empty string or null string), it will throw an exception (System.NullArgumentException if null else System.FormatException)

Below is the code of Int32.Parse(string) using Reflector

public static int Parse(string s)
{
return int.Parse(s, NumberStyles.Integer, null);
}

It calls internally another signature of the method with all arguments


public static int Parse(string s, NumberStyles style, IFormatProvider provider)
{
NumberFormatInfo info1 = NumberFormatInfo.GetInstance(provider);
NumberFormatInfo.ValidateParseStyle(style);
return Number.ParseInt32(s, style, info1);
}

Convert.ToInt32 also converts a string (or some other valid types) into a variable of type int. It basically behaves the same way as Int32.Parse() except that it won't throw an exception System.NullArgumentException if a null string is passed to the method, instead it will return 0.

Below is the code of Convert.ToInt32(string) using Reflector

public static int ToInt32(string value)
{
if (value == null)
{
return 0;
}
return int.Parse(value);
}

You may download http://julien.jacobs.free.fr/download/ConvertProject.zip, a windows application, which highlights each case.

You should therefore use the method that better suits your scenario.

Finally for those who are already using .Net 2.0, you may prefer to use the new Int32.TryParse method, which accepts a string, an ouput int and returns true or false if the conversion succeeds. If the conversion succeeds, the output int is set to the result.

.Net Events made easy

There is one thing that is always difficult to understand for C# beginners (and even more experience developers): .Net events. Many persons keep asking me questions about events handling.

What is the delegate?
Where/How do I declare my event?
etc...

First reflex is to look for a good article or some documents that will make it crystal clear....but sincerely I was not able to find a good one, or let say a good and not too long one.

What is an event?
An event allows an element (page, control, winform) to send a message that can be caught or not by other elements.

How does it work?
One element defines and exposes an event. This is the sender or the provider. Other elements can then subscribe to this event and do something when they receive it. They are the receivers.

What does it look like in code?
First we will declare the event in the sender (page, control, etc..) To declare an event we will need to define the event, its delegate and the method to raise it. We will also declare a class for passing data along with the event but it is not required.

To declare an event, you will need to define a delegate. A delegate simply defines the definition of the method, which will receive the event (this is the handler)

public delegate void MyEventHandler(object sender, MyEventArgs e);

In the example above, the handler will receive the sender as an object and the arguments of the event of type MyEventArgs (If you don't need to pass arguments through your event just use the default System.EventArgs)

Then you declare your event as shown below (note that the event name "MyEvent" is preceeded with the handler defined previously)

public event MyEventHandler MyEvent;

Finally, declare the method that will raise your event. This is the method you will call to raise the event.

protected void OnMyEvent(MyEventArgs e)
{
if(MyEvent!= null)
{
MyEvent(this, e);
}
}

When you want to raise your event, just write

OnMyEvent( new MyEventArgs(param1, param2) );

Below is the code for the event's arguments class

public class MyEventArgs : EventArgs
{
private string param1= "";
private string param2= "";

public MyEventArgs (string param1, string param2)
{
this.param1= param1;
this.param2= param2;
}

public string Param1
{
get { return param1; }
}

public string Param2
{
get { return param2; }
}

}

Now, let's see how that works on the receiver. We will take a ASP.Net Web Form for this purpose. You will need to declare that the web form should subscribe to the event.
In the OnInit method, you will instantiate the handler passing the method that will handle the event and attach the handler to the event.

override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
MyControl.MyEvent += new MyControl.MyEventHandler(MyControl_MyEvent);
base.OnInit(e);
}

The method definition that will handle the event is the one we defined for the delegate. Once you are in the body of the method, you can easily access the parameters sent throught the event arguments.

private void MyControl_MyEvent(object sender, MyControl.MyEventArgs e)
{
string param1 = e.Param1;
string param2 = e.Param2;
//Do your stuff
}

When should I use events?
Events are the basis of programmation in most plaform today. When you click a button, it raises a "onclick" event that you can decide to catch or not.Just think the same way for the blocks you build...

For instance, a block is creating an order; it can raises the event OrderCreated for other blocks to catch it and do whatever is necessary (send a message to a queue, refresh the screen, etc...)
Events are really a powerful way to expose data without being tied to the other blocks that will receive the events.

Well that's it! I hope you find this post useful.