SQL Server 2012 - Inheritance Lockdown

So, I inherited a system with SQL Server 2012 already installed, but no passwords available and the creator of the system gone.  How to recover this development system without having to uninstall and re-install everything?  That was my question and my aim.  So some Googling found me a few answers, though not all clear to me.

Here is a link to a blog that had correct information, though I had to find some other steps along the way. 

http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx

Step By Step Process

First thing you need to do is set the SQL Server instance to single user mode and add a new sysadmin role.  Here are the steps I took with screen shots.

Using SQL Server Configuration Manager

SQL Server Configuration Manager is found under the Start > All Programs > SQL Server (version) > Configuration Tools

1. Under SQL Server Services > Right-click on the SQL Server instance and choose Properties.
   
    
2. Properties
   
   
3. Specify a startup parameter.  Put -m in the input field and click Add.  Then Ok.  You will get the following:
   
   
4. The SQL Service will need to restart to take effect.  You will get prompted as such.
   
   
5. Right-click on the SQL Server service for the instance you set the properties on in step 1 and choose Restart.  

Command Line Portion

1. Go to a command prompt by going to Start > Run and typing cmd.
2. If your paths are set you should be able to type sqlcmd to access the SQL command line.  However, it's possible this is not set, or if you are like me, you have multiple versions of SQL installed.  To affect the version you need, go to the path by changing directory.
   1. Change directory to root, if not there, with cd\.  Then change directory to your SQL installation path.
      
      For example, typing cd "\Program Files\Microsoft SQL Server\110\Tools\Binn" will take you to the SQL Server 2012 BIN folder.
2. Run the  command line:
   sqlcmd
   -OR-
   sqlcmd -S <servername>\<instancename>
   
   Eg. sqlcmd-S kbajan\MSSQLSERVER2012
    
3. This should bring up the SQLCMD prompt as 1>.
4. To create a login with sysadmin rights you have a couple of options
1. Windows Authenticated
2. SQL Authenticated

For Windows Authenticated login:

1> EXEC sp_addsrvrolemember 'domain\username', 'sysadmin';
Press Enter will change prompt to 3>.  Type the following and press Enter:
2> GO
Prompt will go back to 1> and you can type :quit to exit.

Eg.
1> EXEC sp_addsrvrolemember 'kbajan\kDBAjan', 'sysadmin';
2> GO
1> :quit

For SQL Authenticated login:

1> CREATE LOGIN username WITH PASSWORD='mypassword'
Press Enter will change prompt to 2>.  Type the following:
2> EXEC sp_addsrvrolemember 'username', 'sysadmin';
Press Enter will change prompt to 3>.  Type the following and press Enter:
3> GO
Prompt will go back to 1> and you can type :quit to exit.

Eg.

1> CREATE LOGIN kDBAjan WITH PASSWORD='mypassword'

2> EXEC sp_addsrvrolemember 'kDBAjan', 'sysadmin';

3> GO

1> :quit

Final steps

Time to reverse the single-user mode setting.  Follow the first steps taken to get to the Properties of the SQL Service.

1. Next click on the -m parameter and click the Remove button.






Click OK back to the Services.


Right-click on the SQL Server service for the instance and click Restart.

Open up a SQL Server Management Studio (SSMS) session and logon with your newly created user with sysadmin rights.

At this point, if you do not know the SA password, use the newly created user to change it.

*Note: Caution here.  If the SA account is being used elsewhere then this password change could break the system.

Hello World...

Yeah, yeah, cliche but you gotta start somewhere.  For me, this is just about creating a place to come and get some useful references.  Seriously, how many times do you have to Google things to remind yourself of syntax, or to find the exact command you know is there?!  Somethings I just don't commit to memory enough to have a reference manual in my head.  Having the know how, doesn't mean we don't have to refer to the manual for clarification.

So, anyway, this will be my blog page for this information.  I'm only now building it out, and it will depend on how busy I am.  I just finished installing 2 new SQL Server 2012 installations and it will depend on how busy things are.  If you happen upon this page, now you know...  If not, it's just me talking to myself.  I promise not to answer back (yeah right).