Help : I lost sa password and no one has System Administrator (SysAdmin) permission. What should I do?
Posted by blakhani on February 8, 2012
If you are thinking that I am going to show you black magic to recover sa password or other login’s password then you have hit the wrong blog post. go back to search engine and search for better tool/utility. 
After spending many years with SQL Server product, I have seen situations where someone wants to get in to SQL Server as system administrator as someone recently left company who had System Administrator permission (or hundred other reasons). Till SQL 2000 days, it was impossible to solve such problem other than reinstalling SQL Server. Here are the typical questions I saw in forum:
- Only sysadmin user is SA and I Lost SA password.
- I am locked out of SQL server i.e. that no windows users are added (or removed them) as sysadmin and I forgot the password for sa
- I am windows admin. How can I get sys admin privileges on SQL server express as I removed all sysadmin accounts from SQL.
Here are the various error you might see
- Unable to create new database. This is generic error which means that you are not having permission.
TITLE: Microsoft SQL Server Management Studio
——————————
Create failed for Database ‘SQLServer-Help’. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476
——————————
BUTTONS:
OK
——————————
- When you login to Management Studio, you would see only your own account and ‘sa’ as shown below
- When you attempt to change password of ‘sa’ you might see below error.
TITLE: Microsoft SQL Server Management Studio
——————————
Change password failed for Login ‘sa’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Change+password+Login&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot alter the login ‘sa’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15151&LinkId=20476
——————————
BUTTONS:
OK
——————————
All error messages appear because your account is not a System Administrator of SQL Server Instance.
So, what should you do now? Answer is simple, get yourself added as System Administrator. I know, you would say “don’t you think I have tried that as I got this error!”
TITLE: Microsoft SQL Server Management Studio
——————————
Add member failed for ServerRole ‘sysadmin’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+ServerRole&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15247&LinkId=20476
——————————
BUTTONS:
OK
——————————
Okay, here is the step by step guide to add any account as System Administrator of SQL Server. This is documented and completely supported way to gain back the rights. To log into SQL Server as SysAdmin, you need to have Local Administrator permission on the windows which is hosting SQL Server. If you don’t have that also then you may want to check with your windows team to get access (I am not a windows guy)
Steps to login to SQL Server as System Administrator. [Provided you are having windows local administrator permissions]
- Stop the SQL Server Service using ANY of below command.
- Net Stop MSSQLServer (for default instance) / Net Step MSSQL$<InstanceName> If you want to know instance name, refer my earlier blog
- Use SQL Server Configuration manager and stop the SQL service. [Start>Programs>Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager]
- Use Services console [ Start > Run > Services.msc] and locate the SQL instance you want to stop.
- Start SQL Server in Single User mode. You need to use start-up parameter m to start SQL Service in single user mode. I prefer command line but its your choice.
- Using command line
- net start MSSQLServer /m SQLCMD [For default instance]
- net start MSSQL$<InstanceName> /m SQLCMD [For named instance]
- Using configuration Manager
- Locate the service which you have stopped earlier. Go to its properties, “Advanced”, click on drop down at “Startup Parameters” and add ;-mSQLCMD as shown below
- Using command line
You might notice that I have use SQLCMD after m. That’s not a typo. Many times, when you start SQL Server in single user mode, application grabs connection before you could. SQLCMD ensures that only SQLCMD program can connect to SQL Server when its running in single use mode. Here is the error you might see if above happens. SQLCMD should be in UPPERCASE. else that would also show same error. Please make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”
TITLE: Connect to Server
——————————
Cannot connect to (local)\SQL2k8R2.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
——————————
BUTTONS:
OK
——————————
- Connect to SQL Server and add desired account in SysAdmin role.
I normally prefer to do it from SQLCMD (that’s why I added SQLCMD after m)- Open administrator command prompt. (i.e. right click on command prompt shortcut and choose “Run As Administrator”
- Type sqlcmd –S <complete instance name> For example
- sqlcmd –S. (for default instance)
- sqlcmd –S.\MyInstance
For getting exact name, your my earlier blog
- You are connected as System Administrator, because you are part of local administrator group in windows.
- At this point you can add any account to sysadmin because you are connect as sysadmin. Here is the script I normally use to add local administrator group as a part of SysAdmin group in SQL Server Instance. You may want to tweak this as per your needs because I am adding all local admin as sysadmin which is not a good practise.
USE [master]
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’
GO
- Here is how it would look on sqlcmd command prompt
- Stop SQL Server Service. You can use any method as described in first step
- Start SQL Service normally. This means that you need to remove startup parameter
Here is the MSDN reference for above, just to show that its documented.
So, to conclude, I have not shown any trick to recover any password. Just showed you detailed steps to gain sysadmin access provided you have windows admin rights.
DISCLAIMER: Use the method that is described in this article only as a failure recovery mechanism
Help: SQL Server 
nitinsalgar said
Have had to do it several times in the past 4 years.
Recovering Windows Domain admin password is even worse
phobosq said
As per recovery purpose, after starting SQL Server is single user mode you may also think about restoring master database from backup.
Satish said
If we restore master, I think the added login will be removed.
Ademus Prime said
BEST. THING. EVAR!
Karthick P.K said
Good blog 🙂
brian said
after i typed CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
it still said i didnt have access
GS Sohal said
Hi Balmukand,
im having the same issue on one of the critical servers (i got my self added in admin group) .. and i want to avoid having to restart ..
can you let me know .. wht difference starting the SQL in single -user makes .. as compared to doing the same thing .. by
leaving the SQL running as it was .. and then adding a login VIA Sqlcmd
Thanks
GS Sohal said
i also added .. a thread over here ..
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/5e16a19b-acd0-4fa6-80dd-801363f2becc/
how to add yourself to sysadmin role | SQL DBA learning curve said
[…] Option: 2 https://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sys… […]
Junk said
I used to be suggested this website via my cousin.
I am not positive whether or not this publish is written by way
of him as nobody else recognise such precise approximately my trouble.
You are incredible! Thank you!
SQL SERVER – The Story of a Lesser Known Startup Parameter in SQL Server – Guest Post by Balmukund Lakhani | Journey to SQL Authority with Pinal Dave said
[…] can pass a string with -m which has special meaning and use. I have used this parameter in my blog here but looks like not many of you have seen […]
Need help said
Can anyone help me, am getting the error:
1> EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @role
name = N’sysadmin’
2> GO
Msg 102, Level 15, State 1, Server MyServer , Line 1
Incorrect syntax near ”’.
Where am i getting it wrong?
blakhani said
this generally happens when you copy the code from webpage and paste in command prompt. In above query, remove single quotes and type them again.
Connect to SQL server when all system administrators passwords are lost! | Tech Innovation said
[…] or do any admin related stuff on my SQLEXPRESS 2012 instance. After search around I came across this blog that showed how you can add an existing windows administrator to SQL and give it admin […]
To Be….or not to Be….a sysadmin | What's Weird in SQL this week said
[…] https://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sys… […]
B-Square said
I tried several other similar techniques, but they all failed. This is the technique that resolved my issue! Thanks so much!
blakhani said
Thanks.
Eugene said
THANK YOU! -mSQLCMD is a very important detail that the other articles I came across left out! I kept getting “Login failed for user ‘xxx’. Reason: Server is in single user mode…”, thinking “DUH… these instructions make no sense.”
blakhani said
I am glad that this blog was able to help you.
Joey said
Another way is to switch to the mixed mode authentication and enable the SA account. Here are step-by-step instructions:
http://www.top-password.com/blog/how-to-login-to-sa-account-if-sql-server-authentication-is-disabled/
Suresh said
Thanks Balmukund. It was helpful.
blakhani said
Thanks.
frerelance copywriter said
After looking over a handful of the articles on your website, I really appreciate your way of writing a blog.
I added it to my bookmark website list and will be checking
back soon. Please visit my website as well and let me
know how you feel.
Ambar said
One of the best posts I’ve seen! I’ve looked everywhere for this! Thank you! 🙂
Eric said
this is fabulous. for the people having issues with it… follow the advice and don’t try to use ssms. do it in the cmd window… it’s very easy. follow these steps and you’ll get in.
blakhani said
Thanks Eric!
Don Don said
We’re having the same issue, can run throught the majority of the article but when I run sqlcmd -S. I get an error below:
C:\>sqlcmd -S.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user ‘
EUROPE\xe00068’. Reason: Server is in single user mode. Only one administrator c
an connect at this time..
blakhani said
You have to use SQLCMD along with -m so that no one else can connect other than SQLCMD.
Don Don said
Thanks, I’ll try running it with the -m switch
Don Don said
Nearly there! I ran it without the /m switch and got the last part and failed, see below. I have changed the server name and accounts details for oblivious reasons!
C:\>sqlcmd -S.
1> USE [master]
2> go
Changed database context to ‘master’.
1> CREATE LOGIN [mydomain\my adm account] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
2> GO
Msg 15025, Level 16, State 2, Server MYServername, Line 1
The server principal ‘mydomain\my adm account’ already exists.
1> EXEC master..sp_addsrvrolemember @loginame = N’mydomain\my adm account’, @rolename =
N’sysadmin’
2> GO
Msg 102, Level 15, State 1, Server MYServername, Line 1
Incorrect syntax near ”’.
1>
blakhani said
you need to take care of single quotes. copying from webpage messes up with quotes. I would suggest to type the command.
SQL Server SA 最佳实践 - 数据库 - 阿里欧歌 said
[…] 2. https://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sys… […]
pkeerthyKeerthy said
Great post! helped me to get the sa enabled. thanks a bunch.
pkeerthy said
Great Post! helped me to enable my sa account which was locked. thanks a bunch.
blakhani said
Good to know that!
Sambit said
Great Stuff. It really helped me. Many Thanks. 🙂
blakhani said
Thanks!
Sambit said
Great stuff. It actually worked. Many thanks. 🙂
N V said
Good Post.
DB said
What can you do if sql server is installed on primary DC?
Sigriest said
I used the single use mode just to unlock the sa account. thanks
Jason Elizondo said
Hello everyone, i got this message, after the command: CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
Msg 15247, Level 16, State 1, Server DESKTOP-0LEUPE5\SECURITYEXPERT, Line 1
User does not have permission to perform this action.
Could you help me?
Regards,
tsamo said
I used this command sqlcmd -S myServer\instanceName.I get this message “Login failed account is disabled” and it is the same message I get when I login with ssms.My admin account is the local windows account.Need help please