0

I got a laptop on which SQL Server Express is already installed. The server name is SRH\SQLEXPRESS and the version is 10.0.2531.0. The operating system is Windows 7.

I can connect to it through Windows Authentication. But I would like to connect to it though sa. Since I dont know the password of sa so I want to reset it. I decided to change it using osql.

These are the steps I took to change the password of sa:

  1. I opened command prompt (cmd) in Administrator mode.
  2. I typed in osql -S SRH\SQLEXPRESS -E and press Enter key.
  3. Then I get the 1> prompt. I then typed in sp_password null, 'HseWork11', 'sa' and press Enter key.
  4. Then I get the 2> prompt. I then typed in GO and press Enter key, But I get this error message:

Msg 15151, Level 16, State 1, Server SRH\SQLEXPRESS, Line 1 Cannot alter the login 'sa', because it does not exist or you do not have permission.

Bummer.

To confirm that the login sa exist, I connect through Windows Authentication and run this statement select * from sys.syslogins where name = 'sa'. I got 1 row back so the login sa exist.

So why I cannot change sa password using osql? what I am doing wrong?

srh
  • 1,661
  • 4
  • 30
  • 57

2 Answers2

1

Instead of using sp_password, have you tried using ALTER LOGIN, perhaps with the UNLOCK option? I use sqlcmd instead of osql - I don't actually know if that makes a difference.

In the command prompt:

sqlcmd -S .\SQLEXPRESS
>1 ALTER LOGIN sa WITH PASSWORD = ‘whateveryouwant’ UNLOCK
>2 GO
>3 exit

These are obviously different commands, and I know that sometimes ALTER LOGIN has worked for me where sp_password has not. I don't believe it's a cure-all, but it might be worth taking a look.

Side Note:

If this doesn't work, but you can still connect to SQL Server (you said you can, just not as SA), it might be worthwhile to run this query to see who can access SQL Server as an administrator. Maybe you have a built-in account that was given that role...

USE Master
GO

SELECT
      P.Name
     ,P.Type_desc
     ,P.is_disabled
FROM sys.server_principals P
INNER JOIN sys.syslogins L
    ON L.SID = P.SID
WHERE P.Name NOT LIKE '#%'
AND L.SysAdmin = 1 
;

This will give you both SQL Server and Windows users who have the SysAdmin role.

First Edit:

You said that is_disabled is marked as 1 for the SA account after running this query.

Go back to your command prompt and try the following:

>1 ALTER LOGIN sa ENABLE
>2 GO
>3 exit
3BK
  • 1,338
  • 1
  • 8
  • 11
  • I tried `ALTER LOGIN` using `sqlcmd` as you said but I get the same error message. Then I ran your query and it returned 1 row: **Name** is `sa` , **Type_desc** is `SQL_LOGIN` and **is_disabled** is `1`. Why **is_disabled** is `1` and not `0`? – srh Mar 02 '17 at 19:08
  • Updated my answer. Please try the solution I've added and let me know if it works out for you. – 3BK Mar 02 '17 at 19:10
  • After enabling the login, you should be able to retry using the previous `ALTER LOGIN` command to change the password. – 3BK Mar 02 '17 at 19:11
  • I tried `ALTER LOGIN sa ENABLE` using `sqlcmd` as you said but I get the same error message. – srh Mar 02 '17 at 19:23
  • I would recommend you take a look at [this article](https://support.microsoft.com/en-us/help/937682/how-to-add-a-windows-user-to-the-sysadmin-fixed-server-role-in-sql-server-2005-as-a-failure-recovery-mechanism) and see if the steps listed there can help you. – 3BK Mar 02 '17 at 19:35
1

You might not be a System Administrator in SQL Server instance with your windows login. If you have system administrator permissions, you should be able to change the password of sa login.

Please refer Wikihow

Note: OSQL is a discontinued feature, please use SQLCMD.

Venu
  • 455
  • 2
  • 7
  • How to check if my windows login is a System Administrator in SQL Server? – srh Mar 02 '17 at 19:33
  • @srh You already know it isn't, because you ran the query I suggested and your Windows account wasn't listed as one of the System Administrators. – 3BK Mar 02 '17 at 19:36
  • @3BK oh I get it; that query has that filter `SysAdmin = 1` to only show system administrator logins – srh Mar 02 '17 at 19:45
  • @Venu so all answers in http://www.wikihow.com/Reset-SA-Password-in-Sql-Server would not work for me since all those answers assume that the windows login is a System Administrator which is not true in my case – srh Mar 02 '17 at 20:12
  • @srh, a non system administrator user cannot change the password of a system administrator account. – Venu Mar 03 '17 at 06:44