使用 ALTER LOGIN 更改用户自己的密码 [英] Use ALTER LOGIN to change User's own Password

问题描述

我有一个运行以下内容的存储过程:

I have a stored procedure that runs the following:

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

我收到以下错误消息:

无法更改登录名aUser"，因为它不存在或您没有权限.

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

但是，如果我将用户添加到 SysAdmin 角色，那么它就成功了.如果我从 SysAdmin 中删除用户，我会再次收到错误消息，所以它似乎是用户权限.

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.

问题是，我需要用户能够更改他们自己的密码，而 ALTER LOGIN 命令似乎不允许这样做，即使 MSDN 说:

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.

有人有什么想法吗?

推荐答案

我认为要为没有 ALTER ANY LOGIN 的用户执行普通密码更改，您必须提供 OLD_PASSWORD 参数也是.这将与旧的 sp_password 存储过程:

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:

以下示例显示如何使用 ALTER LOGIN 将登录 Victoria 的密码从 B3r1000d#2-36 更改为 V1cteAmanti55imE.这是首选方法.用户 Victoria 无需额外权限即可执行此命令.其他用户需要 ALTER ANY LOGIN 权限:

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

这篇关于使用 ALTER LOGIN 更改用户自己的密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！