SQL2000, sa Password Change Solution

Query Analyzer: Change Password
==========================================================
Exec sp_password null, 'your new password', 'sa'

 

Query Analyzer: The Stored Procedure sp_password is not found.
==================================

Use master

Go

Create procedure sp_password
@ Old sysname = NULL, -- the old (current) password
@ New sysname, -- the new password
@ Loginame sysname = NULL -- user to change password on
As
-- Setup runtime options/declare variables --
Set nocount on
Declare @ self int
Select @ self = case when @ loginame is null then 1 else 2 end

-- Resolve Login Name
If @ loginame is null
Select @ loginame = suser_sname ()

-- Check permissions (securityadmin Per Richard Waymire )--
If (not is_srvrolemember ('securityadmin') = 1)
And not @ self = 1
Begin
DBCC auditevent (107, @ self, 0, @ loginame, null)
Raiserror (15210,-1,-1)
Return (1)
End
Else
Begin
DBCC auditevent (107, @ self, 1, @ loginame, null)
End

-- Disallow user transaction --
Set implicit_transactions off
If (@ trancount> 0)
Begin
Raiserror (15002,-1,-1, 'SP _ password ')
Return (1)
End

-- Resolve Login Name (disallows nt names)
If not exists (select * from Master. DBO. syslogins where
Loginname = @ loginame and isntname = 0)
Begin
Raiserror (15007,-1,-1, @ loginame)
Return (1)
End

-- If non-SysAdmin attempting change to SysAdmin, require password (218078 )--
If (@ self <> 1 and is_srvrolemember ('sysadmin') = 0 and exists
(Select * from Master. DBO. syslogins where loginname = @ loginame and isntname = 0
And SysAdmin = 1 ))
Select @ self = 1

-- Check old password if needed --
If (@ self = 1 or @ old is not null)
If not exists (select * from Master. DBO. sysxlogins
Where srvid is null and
Name = @ loginame and
(@ Old is null and password is null) or
(Pwdcompare (@ old, password, (case when xstatus & 2048 = 2048 then 1 else 0 end) = 1 )))
Begin
Raiserror (15211,-1,-1)
Return (1)
End

-- Change the password --
Update master. DBO. sysxlogins
Set Password = convert (varbinary (256), pwdencrypt (@ new), xdate2 = getdate (), xstatus = xstatus &(~ 2048)
Where name = @ loginame and srvid is null

-- Update protection timestamp for Master dB, to indicate syslogins change --
Exec ('use master grant all to null ')

-- Finalization: Return success/failure --
If @ error <> 0
Return (1)
Raiserror (15478,-1,-1)
Return (0) -- sp_password

 

 

 

 

 

Query Analyzer: Change Password
==========================================================
Exec sp_password null, 'your new password', 'sa'