Getting locked out of a SQL Server can happen in a number of ways.  You could inherit a server that was managed by someone that left the company and only that person had System Admin rights for example. A recent encounter I had was were a database server was built and provisioned in one active directory domain and then moved and joined to another non trusted domain. All the accounts provisioned within the server include those for the DBA admins were basically useless since they couldn’t be authenticated.  The built in SA account is locked down per policy so no one knows the password.

What do you do if you find that you are locked out of a SQL Server instance? The way I handle it is to start SQL Server in single user mode, launch SQLCMD and create a new user. I then still using SQLCMD grant the new user the system admin role.  Once I have the new user created I restart SQL Server Service and log in using the new credentials. I can then clean up the instance by granting proper access.

If this was a newly inherited server I then take the time to do a health check of the instance to make sure that everything is to our standards. (Usually this means more work)

To start SQL Server in single user mode I open a command prompt as an administrator

I type the following minus the quotes (“ “)

“net stop mssqlserver” and press enter

“net start mssqlserver /m” and press enter

“sqlcmd” and press enter

“CREATE LOGIN security WITH PASSWORD = ‘Rec0very123’ “ and press enter

“GO” and press enter

“sp_addsrvrolemember ‘security’, ‘sysadmin’ “ and press enter

“GO” and press enter

“quit” and press enter

“net stop mssqlserver” and press enter

“net start mssqlserver” and press enter

You have now stop and restarted mssql in single user mode, created a new login called ‘security’ and given the user ‘security’ system admin rights. You then stopped and started mssql to put it back in multi user mode. I have included a screen shot of me completing the task on a development workstation so you can see the syntax and steps.

At this point you can connect to the server with SQL Server Management Studio with the new user and reset the SA password and or provision the proper people access.