Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Welcome to my blog. I work as Database lead at Synaptic Digital. Hope you find some interesting stuff here.
Browse by Tags · View All
BRH 17
SQL Server 15
#SQL Server 11
#BI 10
BI 7
#SQLServer 6
SSRS 2008R2 5

Archive · View All
January 2011 6
December 2010 5
September 2012 4
May 2012 4
March 2011 4
November 2012 2
October 2012 2
January 2012 2
February 2011 2
November 2010 2

How to access a newly installed SQL Server 2008 R2 instance if you do not know the login and password

Jul 20 2010 12:00AM by Chintak Chhapia   

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';
  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'; 

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.

Tags: new install, username passowrd unknown, SQL 2008 R2, #DBA, BRH,

Chintak Chhapia
40 · 5% · 1477
5 Readers Liked this
Jacob Sebastian Liked this on 1/3/2012 6:40:00 AM
Profile · Blog · Facebook · Twitter
Guru Samy Liked this on 2/25/2012 3:53:00 AM
Profile · Blog
app+56mp6us2vu.2qucgga7bq.f932725d9819058b36d6dba3 Liked this on 2/7/2012 5:02:00 PM
Profile · Facebook
Nakul Vachhrajani Liked this on 4/7/2013 5:49:00 AM
Profile · Blog · Facebook · Twitter
santa Liked this on 11/26/2013 5:00:00 AM



  • Hey, Chintak! Good one! Very useful. Sorry for reading this so late. Thanks, Nakul.

    commented on Aug 6 2010 1:58AM
    Nakul Vachhrajani
    4 · 36% · 11648
  • Thanks, this was super and useful.

    commented on Feb 25 2012 3:53AM
    Guru Samy
    9 · 16% · 5042
  • Thank you very much.

    This solution saved my job today. Nothing more needs to be said.

    commented on Nov 26 2013 5:00AM
    2807 · 0% · 4
  • hi chintek Thanks, this was useful tip

    commented on Dec 22 2013 11:42PM
    Deepak kelath
    213 · 1% · 222
  • Even though it is not explicitly stated, I believe there is a pre-requisite. The member should be a member of local administrators group ( From TechNet - "Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role. " )

    Correct me if I am wrong.

    Anoo S Pillai

    commented on Dec 23 2013 3:22AM
    Anoo S Pillai
    1436 · 0% · 15

Your Comment

Sign Up or Login to post a comment.

"How to access a newly installed SQL Server 2008 R2 instance if you do not know the login and password" rated 5 out of 5 by 5 readers
How to access a newly installed SQL Server 2008 R2 instance if you do not know the login and password , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]