Wednesday, July 17, 2013

Unlock System administrator account in SQL 2008 R2

If for some reason the system admin account got locked out of SQL server, you may take the following steps to unlock it.

Step 1 Add a new user using current ‘Windows Login user name’, e.g. PBTECH\Michael , then  Stop SQL server

Step 2 Change the server into single user mode, by add “;-m” at the end of path under SQL server property window advanced tab

Step 3: Save & restart, login to SSMS with windows authentication

Step 4: Give the new user added in step 1 System admin permission by executing :
sp_addsrvrolemember [domain\username],'sysadmin'

Step 5: Under security- login find the locked user, reset password and untick the lock, save and close.

Step 5: reverse step of step2, change SQL server back to multi user mode

The SA should be unlocked.