Help: SQL Server

How would you feel if you are not able to change password of your own account? In almost all website, every login/user can change his/her own password. An Administrator need not change password for everyone and keep whispering via email. Login in the SQL Server should also be able to do so. Correct? Let’s try.

I have created a SQL login in SQL Server using below T-SQL.

USE [master]
GO
CREATE LOGIN [SQLServer-Help]
    WITH password = N'Initial@Password'
GO

There is no super power given to the account SQLServer-Help. He is part of pubic role. I have logged into SQL Server using this account and the password. I was surprised to see that my attempt to change my own password failed.

USE [master]
GO
ALTER LOGIN [SQLServer-Help] WITH PASSWORD=N'pass@word1'
GO

Here is the error which I received

Msg 15151, Level 16, State 1, Line 1

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

if I make the account SysAdmin then it works. As per http://msdn.microsoft.com/en-us/library/ms189828.aspx

{

A principal can change the password, default language, and default database for its own login.

}

There is something I am doing which is not right. Why should a Sysadmin rights be given to change password and that too my own password. Now, try to co-relate with the other websites where you change the password. You have been asked for old password… and that was it! There is a parameter in ALTER LOGIN called old_password.

USE [master]
GO
ALTER LOGIN [SQLServer-Help] WITH PASSWORD=N'pass@word123' old_password = N'Initial@Password'
GO

After learning this I realized that why there is a textbox called “Specify old password” in the Login Properties Screen (Highlighted below)

By default checkbox is unchecked and that’s why I never used it.

This brings up and interesting question. “If I know someone’s old password can I change it via logging from my account?” To test this, I created new account called FarFarAway and tried changing from SQLServer-Help login.

select ORIGINAL_LOGIN()
go
USE [master]
GO
ALTER LOGIN [FarFarAway] WITH PASSWORD=N'pass@word123' old_password = N'sa'
GO

Here is the error I received.

Msg 15151, Level 16, State 1, Line 1

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

So the answer is “No. To change someone else’s password, login should have “ALTER ANY LOGIN” permission”

Well, I didn’t know this till someone posted in SQL Bangalore User Group Facebook page. There is a lot of action there. It’s like a Mini SQL Forum. Join there for more learning!