Impersonate a user in SQL2005

It's often useful to run a stored procedure or any script with the permissions of another user (i.e. impersonate a different user than the current one).

It is possible to do so since SQL 2005 is out there, and is as simple as shown below:

EXECUTE AS USER = 'anotheruser'
GO

--Your SQL Statement

REVERT
GO

Note the REVERT command to restore the original user.

In order to impersonate a user, you will grant the IMPERSONATE rights on the user you want to impersonate to the user who will be impersonated it.

GRANT IMPERSONATE ON USER:: [AnotherUser] TO [MyCurrentUser];

Users with sysadmin role can impersonate any user, without being granted the IMPERSONATE right.

For all details, check the online reference @ http://msdn.microsoft.com/en-us/library/ms181362.aspx

Running multiple instances of Firefox

It is always useful to have several browser instances when developing and testing so that you can "impersonate" several different users at the same time.

IE does it for each different window; Firefox always shares the same instance even for different windows.

As per the title of this entry, you already know that Firefox allows you to do it but the "feature" is simply not available as a simple check box.

1. Start Firefox with the Profile Manager

If you installed Firefox in the default installation location, open a command prompt and run "C:\Program Files\Mozilla Firefox\firefox.exe" -no-remote -ProfileManager

2. Create a new profile

You can choose any name but I used "testprofile"

3. You are ready now to run a second instance of Firefox with the testprofile

Again, if you installed Firefox in the default installation location, open a prompt and run "C:\Program Files\Mozilla Firefox\firefox.exe" -no-remote -P testprofile

If you need to run the other instance on a regular basis, you will simply create a shortcut with the command line above.

Need to run more than 2 instances at a time? You will need to repeat the process above and create more test profiles.

For more command line arguments, check the Firefox KB.

Windows Authentication with Firefox

With Firefox, follow the steps below to enable Windows authentication for your servers.

1. Open Firefox 2.x or 3.x

2. Navigate to the url "about:config"

3. Locate the following preference names and set their value with the list of servers separated with comma.

• network.automatic-ntlm-auth.trusted-uris
• network.negotiate-auth.delegation-uris
• network.negotiate-auth.trusted-uris

Your value should look something like this: localhost,myserverA,myserverB

Google's browser...

Google had just made the first BETA version of his (open-source) browser available.

First impression is good! The browser starts really fast with a simple UI as Google knows hwo to make them.

Browsing is also fast, using the same rendering engine as Safari (i.e WebKit)

Oops, I also forgot to give its name: Google Chrome

Read the comics explaining the history of the browser,

Download and install it,

And finally check that your web sites render properly with Safari and Chrome ;-) 

Tips for MyGeneration

I am a big fan of code generation based on templates. It gives all the flexibility you are expecting when coding by hand, but still

• Increase your productivity
• Decrease maintenance efforts
• Code consistency across your project(s)
• etc...

I have been using MyGeneration for a while now and I recently found a good post from Michael Freidgeim summarizing most of the good links you need if you use it.

Replacing the configuration in Web Deployment Projects

I am using Microsoft Web Deployment Projects in order to deploy automatically my nightly build on the development and test servers.

It worked great but I struggled quite some time trying to replace the configuration section.

Well, it was as stupid as that you cannot replace the whole system.serviceModel because it's not a configuration section, but is a configuration section group...and guess what?

WDP can only replace configuration sections and not configuration section groups.

What you can do though is replacing a section within the section group.

Within system.serviceModel, you can replace the client configuration section for instance using the replacement string "system.serviceModel/client=system.serviceModel.client.dev.config;"

SQL Code Prettifier from Simple-Talk

Use the Simple-Talk Prettifer to format your code (SQL, C#, VB and Python)http://extras.sqlservercentral.com/prettifier/prettifier.aspx in your web pages.

Update (23-March-10): I have been recently using http://www.manoli.net/csharpformat/, which does a better job and allow xml formatting.

Calling a WCF Service

Based on WCF best practices, calling a WCF service should not be wrapped in a using statement as exceptions can be raised (http://msdn2.microsoft.com/en-us/library/aa355056.aspx). Below is a code snippet highlighting the best practice:

ServiceClient client = new ServiceClient();

try
{
client.MyOperation();
client.Close();
}
catch (CommunicationException e)
{
//Handle exception if necessary
client.Abort();
}
catch (TimeoutException e)
{
//Handle exception if necessary
client.Abort();
}
catch (Exception e)
{
//Handle exception if necessary
client.Abort();
throw;
}

However the code to write to do so is quite painful, hence I have written a VS code snippet (wcfproxycall.snippet) to minimize the pain.

To install the code snippet, simply copy the attached snippet to "My Documents\Visual Studio 2008\Code Snippets\Visual C#\My Code Snippets"

When you need to call a proxy, type wcfproxycall in your code and hit the Tab key to expand the code snippet.

• Fill the highlighted fields
• Use the tab key to navigate to the next field
• Hit Enter to complete when all fields are filled

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)