How to get sysadmin access on SQL Server 2008

If you've ever inherited a  PC with SQL Server 2008 installed on it have probably hit the situation where you are a local admin but you can't access the SQL Server instance.This is because SQL Server 2008 unlike earlier versions of SQL Server doesn't by default give sysadmin access to the local Administrators group.

Here is a list of steps that will allow you to gain administrator access - provided you are a windows local admin.

Force SQL server to support mixed-mode authentication

1. Run REGEDIT
2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
   Set "LoginMode" to 2.
   (Source: http://support.microsoft.com/kb/285097)
3. Restart SQL Server.

Force SQL server to let you in temporarily

1. Go to services.
2. Stop SQL Server.
3. Grab the SQL server command-line (right click the service - properties). Mine is:
   "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -sSQLEXPRESS
4. Open an administrative command prompt.
5. Run the command-line from step 3, but add -m -c for single-user maintenance mode command-line.
6. Open another administrative command prompt.
7. Run "sqlcmd -S localhost\SQLEXPRESS" from that same directory (replace with your server and instance name)
8. Now you can do all the stuff everyone told you to do that didn"t work. For example, to create a hero user with administrative access:
   CREATE LOGIN hero WITH PASSWORD="123", DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
   EXEC sys.sp_addsrvrolemember @loginame = "hero", @rolename = "sysadmin"
   GO
9. QUIT and close the command-prompt
10. Go to the SQL Server command-line window and hit ctrl+C. It will prompt "Do you wish to shutdown SQL Server (Y/N)?" and enter Y.
11. Close the command-prompt
    (Source: http://msdn.microsoft.com/en-us/library/dd207004.aspx)

Finally, login using your hero

1. Restart the SQL Server service
2. Login using SQL Server authentication as the user "hero" with password "123"
3. *BAM* now you are in. Now give yourself sysadmin access, etc, etc.

How to send an email using Gmail and c#

How you can sent an email in c # using Gmail's SMTP servers. The only trick here is that Gmail requires you  to use port 587, enable SSL and supply valid login details.

            var client = new SmtpClient("smtp.gmail.com", 587)

            {

                Credentials = new NetworkCredential("[valid Gmail username]", "[valid Gmail password]"),

                EnableSsl = true

            };

            client.Send("[email from address]", "[email to addression]", "[email subject]", "[email body]");

How to check the progress when Shrinking Database SQL Server

Compacting a large databases in SQL Server can take what feels like forever. If you're like me self doubt is sure to set in as there is no way to tell that the command is progressing in SSMS!
However you can use the system views in SQL Server to see how its going using the following query:

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests

Works on SQL Server 2005+

Source: http://blogs.msdn.com/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx