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 BUILTINAdministrators to the sysadmin fixed server role, meaning that any local NT administrator is automatically a SQL Server’s sysadmin. But 2008 onwards, Windows Group BUILTINAdministrators 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. 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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn”. So need to run CD C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn.
  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.
1.sqlservr -m"SQLCMD"
  • Now open another command prompt window and write command SQLCMD
  • Run following two commands
  • 1.CREATE LOGIN [testAdmin] WITH PASSWORD=N'test@1234', DEFAULT_DATABASE=[master];
    2.EXEC sys.sp_addsrvrolemember @loginame = N'testAdmin', @rolename = N'sysadmin';
  • 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.
  • Start SQL server again and no startup parameters need to specified this time.
  • Now using SSMS, try to connect with “testAdmin” as user and test@abcd as password.
  • Create your own logins
  • 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 <<DOMAINUSERNAME>> is placeholder for your user name

    1.create login [<<DOMAINUSERNAME>>] from windows; 
    2.EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAINUSERNAME>>', @rolename = N'sysadmin'
    Update 08-Jan-2011:- Recently, I found the script that is useful in  doing all this task, may be it's useful to you when you are in thin air.