Many time, colleagues come to me ask, they have setup SQL 2008 or SQL 2008 R2 for development or QA environment, but they don't know the login by which they can connect to server.  In SQL server 2005, SQL use to add group BUILTIN\Administrators to the sysadmin fixed server role, meaning that any local NT administrator is automatically a SQL Server's sysadmin. But 2008 onwards, Windows Group BUILTIN\Administrators is not included in the SQL Server sysadmin server role by default.   

To avoid this situation, we need to specify the SQL server administrator on "Database Engine Configuration" screen while setup sql server. But, sometime this step is missed or the IT guys set some odd password to SA user. The same question is also posted on  MSDN forum . One way to recover from this situation is to uninstall and re-install the sql server again. But, using the below mentioned steps we can create a new login

1. Open command prompt.
2. If you have default instance run following command on command prompt to stop SQL service: "net stop mssqlserver"
3. Now go to the directory where SQL server is installed. In my case the directory is "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn". So need to run CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn.
4. Now run the following command to start sql server in single user mode. As we have specified "SQLCMD", now only SQLCMD connection can be made.

   sqlservr -m"SQLCMD"

   Note:- for named instance need to suppy the instancename after -s switch example: id instancename is SQL2008R2 then need to run sqlservr -m"SQLCMD" -sSQL2008R2

5. Now open another command prompt window and write command SQLCMD. In case of connecting to named instance we need to supply server and instance name as with -S switch
6. Run following two commands

   CREATE LOGIN [testAdmin] WITH PASSWORD=N'test@1234', DEFAULT_DATABASE=[master];
   EXEC sys.sp_addsrvrolemember @loginame = N'testAdmin', @rolename = N'sysadmin';
   GO

7. Go back to first command window and press Ctrl+C to stop the SQL server and then type 'Y' for confirmation. This will stop the sql server.
8. Start SQL server again and no startup parameters need to specified this time.
9. Now using SSMS, try to connect with "testAdmin" as user and "test@1234" as password.
10. Create your own logins
11. Drop testAdmin as it is not required any more.

Note: If you do not have SQL authentication enabled then you can try adding your windows user and replace setp-6 with below queries. Here <<DOMAIN\USERNAME>> is placeholder for your user name

create login [<<DOMAIN\USERNAME>>] from windows; 
EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin'; 
GO;

Update 08-Jan-2011:- Recently, I found the script that is useful in doing all this task. This script is available here.

Update 08-Aug-2012:- One more way of doing this using Sysinternal tool PSEXEC as mentioned in this post

Update 17-Dec-2012:- Refer this post by Feodor Georgiev to see how to do this in clustered environment.