sql server 2012: cannot alter the login sa

2019-05-07 07:39发布

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

3条回答
贪生不怕死
2楼-- · 2019-05-07 07:51

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.
查看更多
乱世女痞
3楼-- · 2019-05-07 07:56

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
查看更多
ゆ 、 Hurt°
4楼-- · 2019-05-07 08:11

I'd like to point out an alternative answer laid out on DBA SE. Download PSExec onto the box that is having the problem and follow the instructions laid out in this blog post to effortlessly change admin settings using the NT Authority\System account.

Wanted to share this solution as it solved my problem!

查看更多
登录 后发表回答