Use ALTER LOGIN to change User's own Password

Question

I have a stored procedure that runs the following:

'ALTER LOGIN ' + @Login + ' WITH PASSWORD = ''' + @Password + ''''

I get the following error message:

Cannot alter the login 'aUser', because it does not exist or you do not have permission.

HOWEVER, if I make add the user to the SysAdmin role, then it is successful. If I remove the user from SysAdmin, I get the error message again, so it appears to be user rights.

The problem is, I need users to be able to change their own passwords, and the ALTER LOGIN command does not seem to allow for that, even though MSDN says:

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

Anyone got any ideas?

Answer 1

I think to perform an ordinary password change for a user, who doesn't have ALTER ANY LOGIN, you must supply the OLD_PASSWORD parameter also. This would match the example from the older sp_password stored procedure:

The following example shows how to use ALTER LOGIN to change the password for the login Victoria from B3r1000d#2-36 to V1cteAmanti55imE. This is the preferred method. User Victoria can execute this command without additional permissions. Other users require ALTER ANY LOGIN permission:

ALTER LOGIN Victoria WITH 
     PASSWORD = 'V1cteAmanti55imE' 
     OLD_PASSWORD = 'B3r1000d#2-36';
GO

Answer 2

You can have your application run as a user with sufficient rights. For that, you would create a service account for the application in SQL Server, and then have your application run as that user.

However, it might be better to create a Users table for your application and manage this data there, rather than allowing your users direct access to altering logins.

Answer 3

I'm using following script (You can create as proc)

declare @l sysname = suser_sname(); declare @x nvarchar(max) = 'alter login [' + @l + '] with default_database = Northwind'; exec (@x);

Something similar You can use with other login settings.