Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql server 2012: cannot alter the login sa

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
like image 876
tdc Avatar asked Jul 22 '13 13:07

tdc


People also ask

What is the default sa password for SQL Server 2012?

This connection is normally authenticated using SQL Server Authentication, and with the user name [sa] and default password [RPSsql12345].


2 Answers

A little more specific :

  1. Open Sql Configuration Manager.
  2. Select SQL Server Services.
  3. On the right hand side, select the instance.
  4. Right click on it and open properties.
  5. In the advanced tab attach ";-m" at the end of the Startup Parameters field.
  6. Apply and restart the service.
  7. Now you have privilege to enable "sa" user and modify its password.
  8. once done, remove ";-m" and restart the service.
  9. You are good to go.
like image 68
S'chn T'gai Spock Avatar answered Feb 16 '23 06:02

S'chn T'gai Spock


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
like image 41
tdc Avatar answered Feb 16 '23 05:02

tdc