Database	Proc	Application	Created	Links
sybsystemprocs	sp_password		31 Aug 14	Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /* 	4.8	1.1	06/14/90	sproc/src/password */
4     
5     /*
6     ** Generated by spgenmsgs.pl on Thu Feb  2 00:39:18 2006 
7     */
8     /*
9     ** raiserror Messages for password [Total 3]
10    **
11    ** 17260, "Can't run %1! from within a transaction."
12    ** 17720, "Error:  Unable to set the Password."
13    ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record."
14    */
15    /*
16    ** sp_getmessage Messages for password [Total 1]
17    **
18    ** 17721, "Password correctly set."
19    */
20    /*
21    ** End spgenmsgs.pl output.
22    */
23    
24    /* 
25    ** IMPORTANT: Please read the following instructions before
26    **   making changes to this stored procedure.
27    **
28    **	To make this stored procedure compatible with High Availability (HA),
29    **	changes to certain system tables must be propagated 
30    **	to the companion server under some conditions.
31    **	The tables include (but are not limited to):
32    **		syslogins, sysservers, sysattributes, systimeranges,
33    **		sysresourcelimits, sysalternates, sysdatabases,
34    **		syslanguages, sysremotelogins, sysloginroles,
35    **		sysalternates (master DB only), systypes (master DB only),
36    **		sysusers (master DB only), sysprotects (master DB only)
37    **	please refer to the HA documentation for detail.
38    **
39    **	Here is what you need to do: 
40    **	For each insert/update/delete statement, add three sections to
41    **	-- start HA transaction prior to the statement
42    **	-- add the statement
43    **	-- add HA synchronization code to propagate the change to the companion
44    **
45    **	For example, if you are adding 
46    **		insert master.dbo.syslogins ......
47    **	the code should look like:
48    **	1. Before that SQL statement:
49    **		
50    **	2. Now, the SQL statement:
51    **		insert master.dbo.syslogins ......
52    **	3. Add a HA synchronization section right after the SQL statement:
53    **		
54    **
55    **	You may need to do similar change for each built-in function you
56    **	want to add.
57    **
58    **	Finally, add a separate part at a place where it can not
59    **	be reached by the normal execution path:
60    **	clean_all:
61    **		
62    **		return (1)
63    */
64    
65    create procedure sp_password
66        @caller_password varchar(255) = NULL, /* the current password of caller */
67        @new_password varchar(256) = NULL, /* the new password of the target acct*/
68        /* a length of 256 is required to test if
69        ** user entered a passwd > 255 chars.
70        */
71        @loginame varchar(255) = NULL, /* user to change password on */
72        @immediate int = 0 /* if not 0, change the password in
73    					** all running processes for loginame.
74    					*/
75    as
76    
77        declare @returncode int
78        declare @msg varchar(1024)
79        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
80        declare @retstat int
81    
82    
83        select @HA_CERTIFIED = 0
84    
85    
86    
87    
88        /* check to see if we are using HA specific SP for a HA enabled server */
89        exec @retstat = sp_ha_check_certified 'sp_password', @HA_CERTIFIED
90        if (@retstat != 0)
91            return (1)
92    
93        /*
94        ** Do not allow this system procedure to be run from within a transaction
95        ** to avoid creating a multi-database transaction where the 'master'
96        ** database is not the co-ordinating database.
97        */
98        if @@trancount > 0
99        begin
100           /*
101           ** 17260, "Can't run %1! from within a transaction."
102           */
103           raiserror 17260, "sp_password"
104           return (1)
105       end
106       else
107       begin
108           set chained off
109       end
110   
111       set transaction isolation level 1
112   
113       /*
114       **  Encrypt and store the input @new_password.
115       **  @caller_password will be checked against the password of the caller.
116       **  set_password() builtin will print out nice messages.
117       */
118       select @returncode = set_password(@caller_password, @new_password, @loginame, @immediate)
119   
120   
121   
122   
123       if (@returncode = 0)
124       begin
125           /*
126           ** 17720, "Error:  Unable to set the Password."
127           */
128           raiserror 17720
129           return (1)
130       end
131       else
132       begin
133           /*
134           ** Before we log our system procedure execution instance,
135           ** re-initialize the '@caller_password' parameter to NULL and the
136           ** '@new_password' parameter to the encrypted form of the password.
137           ** This prevents the passwords from being stored in clear text in
138           ** the transaction log as well as in the Replication Server stable
139           ** queues.
140           **
141           ** When the ASE RepAgent Thread sends the system procedure
142           ** execution instance to the Replication Server, the ASE RepAgent
143           ** will re-name the system procedure from 'sp_password()' to
144           ** 'sp_password_rep()'.  This will cause the Replication Server to
145           ** execute, at the target ASE, the system procedure
146           ** 'sp_password_rep()' which knows how to properly process the
147           ** encrypted password.
148           */
149           select @caller_password = NULL
150   
151           if (@loginame is not NULL)
152           begin
153               select @new_password = password
154               from master.dbo.syslogins
155               where name = @loginame
156           end
157           else
158           begin
159               select @new_password = password
160               from master.dbo.syslogins
161               where suid = suser_id()
162           end
163   
164           /*
165           ** If the 'master' database is marked for replication, the T-SQL
166           ** built-in 'logexec()' will log for replication the execution
167           ** instance of this system procedure.  Otherwise, the T-SQL
168           ** built-in 'logexec()' is a no-op.
169           */
170           if (logexec(1) != 1)
171           begin
172               raiserror 17756, "sp_password", "master"
173               return (1)
174           end
175   
176           /*
177           ** 17721, "Password correctly set."
178           */
179           exec sp_getmessage 17721, @msg output
180           print @msg
181           return (0)
182       end
183   
184   

exec sp_procxmode 'sp_password', 'AnyMode'
go

Grant Execute on sp_password to public
go

DEFECTS
MGTP 3 Grant to public master..syslogins
MGTP 3 Grant to public sybsystemprocs..sp_password
MNER 3 No Error Check should check return value of exec	179
MUCO 3 Useless Code Useless Brackets	90
MUCO 3 Useless Code Useless Brackets	91
MUCO 3 Useless Code Useless Brackets	104
MUCO 3 Useless Code Useless Brackets	123
MUCO 3 Useless Code Useless Brackets	129
MUCO 3 Useless Code Useless Brackets	151
MUCO 3 Useless Code Useless Brackets	170
MUCO 3 Useless Code Useless Brackets	173
MUCO 3 Useless Code Useless Brackets	181
QISO 3 Set isolation level	111
VNRD 3 Variable is not read @caller_password	149
VNRD 3 Variable is not read @new_password	159
MTR1 2 Metrics: Comments Ratio Comments: 72%	65
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 2 = 5dec - 5exi + 2	65
MTR3 2 Metrics: Query Complexity Complexity: 37	65

DEPENDENCIES

PROCS AND TABLES USED calls proc sybsystemprocs..sp_getmessage      reads table master..sysmessages (1)      reads table master..syslanguages (1)      reads table sybsystemprocs..sysusermessages      calls proc sybsystemprocs..sp_validlang         reads table master..syslanguages (1)   reads table master..syslogins (1)   calls proc sybsystemprocs..sp_ha_check_certified      reads table tempdb..sysobjects (1)   CALLERS called by proc sybsystemprocs..sp_addlogin