1 create procedure sp_password 2 @old sysname = NULL, -- the old (current) password 3 @new sysname, -- the new password 4 @loginame sysname = NULL -- user to change password on 5 as 6 -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- 7 set nocount on 8 declare @self int 9 select @self = CASE WHEN @loginame is null THEN 1 ELSE 2 END10 11 -- RESOLVE LOGIN NAME12 if @loginame is null13 select @loginame = suser_sname()14 15 -- CHECK PERMISSIONS (SecurityAdmin per Richard Waymire) --16 IF (not is_srvrolemember(‘securityadmin‘) = 1)17 AND not @self = 118 begin19 dbcc auditevent (107, @self, 0, @loginame, NULL, NULL, NULL)20 raiserror(15210,-1,-1)21 return (1)22 end23 ELSE24 begin25 dbcc auditevent (107, @self, 1, @loginame, NULL, NULL, NULL)26 end27 28 -- DISALLOW USER TRANSACTION --29 set implicit_transactions off30 IF (@@trancount > 0)31 begin32 raiserror(15002,-1,-1,‘sp_password‘)33 return (1)34 end35 36 -- RESOLVE LOGIN NAME (disallows nt names)37 if not exists (select * from master.dbo.syslogins where38 loginname = @loginame and isntname = 0)39 begin40 raiserror(15007,-1,-1,@loginame)41 return (1)42 end43 44 -- IF non-SYSADMIN ATTEMPTING CHANGE TO SYSADMIN, REQUIRE PASSWORD (218078) --45 if (@self <> 1 AND is_srvrolemember(‘sysadmin‘) = 0 AND exists46 (SELECT * FROM master.dbo.syslogins WHERE loginname = @loginame and isntname = 047 AND sysadmin = 1) )48 SELECT @self = 149 50 -- CHECK OLD PASSWORD IF NEEDED --51 if (@self = 1 or @old is not null)52 if not exists (select * from master.dbo.sysxlogins53 where srvid IS NULL and54 name = @loginame and55 ( (@old is null and password is null) or56 (pwdcompare(@old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )57 begin58 raiserror(15211,-1,-1)59 return (1)60 end61 62 -- CHANGE THE PASSWORD --63 update master.dbo.sysxlogins64 set password = convert(varbinary(256), pwdencrypt(@new)), xdate2 = getdate(), xstatus = xstatus & (~2048)65 where name = @loginame and srvid IS NULL66 67 -- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --68 exec(‘use master grant all to null‘)69 70 -- FINALIZATION: RETURN SUCCESS/FAILURE --71 if @@error <> 072 return (1)73 raiserror(15478,-1,-1)74 return (0) -- sp_password