SQL: Recover login when all hope is lost
Author: jason
Date: 2015-07-09 12:41:04
Category: Musing

If you installed SQL and just went NEXT through the screens or lost the SA password and just can't figure out how to get in or who has permissions in SQL...

Steps

1) Verify the SQL Instance (Default or other)
a) Check the SQL Service Name in Computer Management Services
b) The Default Instance is MSSQLSERVER. Any other Instance needs to be connected as SERVERNAMEINSTANCENAME
c) If there is an Instance, make sure the SQL Browser Service is running

2) Try running SQL Server Management Studio as Administrator, sometime UAC is just plain mean

3) Check Authentication Mode
a) Check in the registry to see how SQL Server is set up
b) HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLServer
i) Note the 11 in the above registry path is specific to the version of SQL, 11 is SQL 2012, your mileage may vary
c) The LoginMode controls the Authentication Mode, it should be set to Mixed Mode
i) 1 = Windows Authentication
ii) 2 = Mixed Mode
d) If the Authentication Mode is 1 then change it to 2

4) Stop the SQL Server Service (Computer Management – Services)

5) Open an Administrator Elevated Command Prompt
a) Navigate to the BINN directory of your SQL Server
i) C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBINN
ii) Note the 11 in the above path is specific to the version of SQL, 11 is SQL 2012
b) Run the following command to start SQL in Maintenance / Single User Admin Mode: sqlservr.exe –m

6) Open another Administrator Elevated Command Prompt
a) Run the following command to connect to SQL: SQLCMD –S YOURSERVERNAME
i) If you don't have SQLCMD you may need to install the command line utilities via the SQL Server Installer. It should be a part of the Tools that come with SQL Server though…
b) Run the following command to create a Login: CREATE LOGIN myuser WITH PASSWORD='Password01'
c) Run the following command: GO
d) Run the following command to make the user SysAdmin: SP_ADDSRVROLEMEMBER 'myuser', 'sysadmin'
e) Run the following command: GO
f) Run the following command: Exit

7) In the Command Prompt running the SQL Server press Control+C to shutdown the server

8) Start the SQL Server Service (Computer Management – Services)

9) Run SQL Server Management Studio as Administrator
a) Use SQL Authentication, enter your new user and password

10) Add the BUILTIINAdministrators as a SysAdmin ServerRole

11) Run SQL Server Management Studio as Administrator
a) Try Windows Authentication now
b) Don't forget to remove/cleanup your SQL user

Reference
http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx
http://www.verboon.info/2012/03/how-to-get-the-sqlcmd-standalone-for-microsoft-sql-server-management/
https://www.microsoft.com/en-us/download/details.aspx?id=36433




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009