sql server 2012: cannot alter the login sa

tdc picture tdc · Jul 22, 2013 · Viewed 48.5k times · Source

I'm trying to create a database on my local machine using SSMS version 11.0.2100.60. I've run the application as administrator, logged in using Windows authentication, and I've added MYDOMAIN\my-username to the Logins. However if I try to create a db with this login I get the message

CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

If I try to add the privelage dbcreator to my user, I get the following error.

User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)

I can't log in as sa as I don't know/remember the password (is there a preset default?), and if I try to change the password I get the message:

Cannot alter the login 'sa', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

Finally I note that the account 'sa' is disabled, and if I try to enable it I get the same error as before. Is there any way around this or do I need to re-install?

Version info:

Microsoft SQL Server Management Studio                      11.0.2100.60
Microsoft Analysis Services Client Tools                        11.0.2100.60
Microsoft Data Access Components (MDAC)                     6.2.9200.16384
Microsoft MSXML                     3.0 4.0 6.0 
Microsoft Internet Explorer                     9.10.9200.16635
Microsoft .NET Framework                        4.0.30319.18051
Operating System                        6.2.9200

Answer

tdc picture tdc · Jul 22, 2013

I found the answer here:

In order to start SQL Server in single-user mode, you can add the parameter “-m” at the command line. You can also use the SQL Server Configuration Manager tool, which provides proper controls for the file access and other privileges. To use the Configuration Manager tool to recover your system, use the following steps:

  1. Open the Configuration Manager tool from the "SQL Server 2005| Configuration" menu
  2. Stop the SQL Server Instance you need to recover
  3. Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option
  4. Click the “OK” button and restart the SQL Server Instance