Set quoted_identifier on
Go
Set ansi_nulls off
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
Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go