以下是我的SP，你修改一下吧。
CREATE PROCEDURE dbo.password
@old varchar(30) = NULL, /* the old (current) password */
@new varchar(30), /* the new password */
@loginame varchar(30) = NULL /* user to change password on */
as
declare @suid int /* suid of person to change pw on */
/*
** If we're in a transaction, disallow this since it might make recovery
** impossible.
*/
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sp_password')
return (1)
end
/*
** If the system administrator (sa) is running this command, the sa can
** change anyone's password and doesn't have to know the old password.
** We have this case is suser_id() = 1 and @loginame is NOT NULL.
** In this case, we'll fill in the old password and set the suid.
*/
if suser_id() <> 1 and @loginame IS NOT NULL
begin
/*
** Only the sa can use the @loginame.
*/
raiserror(15210,-1,-1)
return (1)
end
/*
** Does the SA want to change someone's password?
*/
if suser_id() = 1 and @loginame IS NOT NULL
begin
/* Check to seeif the user exists. */
select @suid = suid from master..syslogins where name = @loginame
if @suid is null
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
/*
** Get the old password for the user. If @old not given, don't
** bother checking against the old password.
*/
if @old IS NOT NULL
begin
/*
** Reinitialize @suid since previous assignment
** set it.
*/
select @suid = NULL
select @suid = suid
from master..syslogins
where name = @loginame
and (password = @old or
pwdcompare(@old, password) = 1
)
if @suid is null
begin
raiserror(15211,-1,-1)
return (1)
end
end
end
/*
** This is just the normal case when someone wants to change their own
** password. Set the @suid.
*/
else
if @loginame is null
select @suid = suid
from master..syslogins
where (password = @old or pwdcompare(@old, password) = 1)
and suid = suser_id()
if @suid is null
begin
raiserror(15211,-1,-1)
return (1)
end
/*
** This is the final case. The sa is running the command to change
** someone else's password and is supplying the old password. Confirm
** that the old password is correct.
*/
if suser_id() = 1
begin
select @suid = suid
from master..syslogins
where (password = @old or pwdcompare(@old, password) = 1)
and suid = suser_id(@loginame)
if @suid is null
begin
raiserror(15211,-1,-1)
return (1)
end
end
/*
** If @suid still hasn't been set then the user running the procedure
** doesn't have the right current password.
*/
if @suid is null
begin
raiserror(15211,-1,-1)
return (1)
end
/*
** Everything is consistent so change the password and set encrypt flag.
*/
update master..syslogins set password = pwdencrypt(@new),status = status | 8
where suid = @suid
print 'Password changed.'
return (0)
