sql-serverwindowsosqlsa

Why I cannot change sa password using osql?

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?

Solution

  • 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