How can non-sysadmin user change their own password in SQL Server?

Last time one of my friend asked me to how can i change the own password in SQL Server? Why every time administrator change my password when needed.? I don’t want that administrator or SA know my password?

I am going to show you some simple steps below:-

I have created Sql login in SQL Server using T-SQL

USE [master]

CREATE LOGIN [Demo_Login]

WITH password = N’Before_Your_Password’

There is no super privilege given to the account Demo_Login and it is part of public role.

I have logged into SQL Server using this account and the password and try to change the password but it is getting failed and i am getting below error:-

USE [master]

ALTER LOGIN [Demo_Login] WITH PASSWORD=N’password@12345′

I am getting below error

Msg 15151, Level 16, State 1, Line 1

Cannot alter the login ‘Demo_Login’, because it does not exist or you do not have permission.

If i assign sysadmin permission to Demo_Login then it works.

There is something I am doing which is not right because There is a parameter in ALTER LOGIN called old_password after that i used below query and changed the password.

USE [master]

ALTER LOGIN [Demo_Login] WITH PASSWORD=N’password@12345′ old_password=N’Before_Your_Password’

Then I realized that’s why there is a textbox called “Specify old password” in the Login Properties Screen:-

By default checkbox is unchecked and because of that we never used them.

There is one more question came into my mind can i change the old password of someone if i know his/her older password?

USE [master]
GO
ALTER LOGIN [DemoSQL] WITH PASSWORD=N’pass@word123′ old_password = N’12345′

GO

Then i am getting below error:-
Msg 15151, Level 16, State 1, Line 1

Cannot alter the login ‘DemoSQL’, because it does not exist or you do not have permission.

conclusion is:-To change someone else’s password, login should have “ALTER ANY LOGIN” permission but you can change your own password.

Leave a Reply Cancel reply