DatabaseProcApplicationCreatedLinks
sybsystemprocssp_passwordpolicy  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Messages for "sp_passwordpolicy" 
4     ** 17047, "The commands 'expire login passwords' and 'expire role passwords' require an argument."
5     ** 17231, "No login with the specified name exists."
6     ** 17260, "Can't run %1! from within a transaction."
7     ** 17925, "You entered an invalid option name. No change was made."
8     ** 17932, "You entered an invalid value. No change was made."
9     ** 18409, "The built-in function '%1!' failed. Please see the other messages printed along with this message."
10    ** 19213, "Invalid argument or unsupported command: %1!."
11    ** 19633, "Network password encryption keypair will be regenerated at %1!."
12    ** 19844, "There is no password protected role like '%1!'."
13    */
14    
15    /* 
16    ** IMPORTANT: Please read the following instructions before
17    **   making changes to this stored procedure.
18    **
19    **      To make this stored procedure compatible with High Availability (HA),
20    **      changes to certain system tables must be propagated 
21    **      to the companion server under some conditions.
22    **      The tables include (but are not limited to):
23    **              syslogins, sysservers, sysattributes, systimeranges,
24    **              sysresourcelimits, sysalternates, sysdatabases,
25    **              syslanguages, sysremotelogins, sysloginroles,
26    **              sysalternates (master DB only), systypes (master DB only),
27    **              sysusers (master DB only), sysprotects (master DB only)
28    **      please refer to the HA documentation for detail.
29    **
30    **      Here is what you need to do: 
31    **      For each insert/update/delete statement, add three sections to
32    **      -- start HA transaction prior to the statement
33    **      -- add the statement
34    **      -- add HA synchronization code to propagate the change to the companion
35    **
36    **      For example, if you are adding 
37    **              insert master.dbo.syslogins ......
38    **      the code should look like:
39    **      1. Before that SQL statement:
40    **              
41    **      2. Now, the SQL statement:
42    **              insert master.dbo.syslogins ......
43    **      3. Add a HA synchronization section right after the SQL statement:
44    **              
45    **
46    **      You may need to do similar change for each built-in function you
47    **      want to add.
48    **
49    **      Finally, add a separate part at a place where it can not
50    **      be reached by the normal execution path:
51    **      cleanup:
52    **              
53    **              return (1)
54    */
55    
56    create or replace procedure sp_passwordpolicy
57        @cmd varchar(30) = null,
58        @opt1 varchar(30) = null,
59        @opt2 varchar(30) = null,
60        @opt3 varchar(30) = null
61    as
62        declare @optname varchar(30), /*
63            ** To store mapped name of the 
64            ** attribute in spt_values
65            */
66            @retval int,
67            @count1 int, /* Temperory variable 1 */
68            @count2 int, /* Temperory variable 2 */
69            @result varchar(5),
70            @validation_step varchar(50),
71            @validation_test varchar(65),
72            @return_status int,
73            @nextregen datetime,
74            @log_for_rep int, /* Replication status of master db */
75            @db_rep_level_all int, /* Replication constants.	   */
76            @db_rep_level_none int,
77            @db_rep_level_l1 int,
78            @lt_rep_get_failed int,
79            @lt_rep_all int,
80            @lt_rep_l1 int,
81            @entered_date datetime, /* Stores datetime specified for
82            ** expiring stale login or role
83            ** passwords
84            */
85            @allow_pwd_dwn_status int,
86            @msg varchar(1024),
87            @allow_pwd_dwn_dt smalldatetime,
88            @allow_pwd_dwn_dt_int int,
89            @specified_datetime varchar(30),
90            @dbname varchar(255),
91            @HA_CERTIFIED tinyint, /* Is the SP HA certified ? */
92            @retstat int,
93            @list_keypair int,
94            @list_nonkeypair int,
95            @dummy varchar(30), /* This is used to pass to the
96            ** password_admin builtin. In future
97            ** any additonal argument to 
98            ** sp_passwordpolicy can be
99            ** replace with it.
100           */
101           @nullarg char(1),
102           @dummystat int,
103           @status int,
104           @gp_enabled int
105   
106   
107   
108       select @HA_CERTIFIED = 0
109       select @list_keypair = 0
110       select @list_nonkeypair = 0
111   
112   
113   
114       /* check to see if we are using HA specific SP for a HA enabled server */
115       exec @retstat = sp_ha_check_certified 'sp_passwordpolicy', @HA_CERTIFIED
116       if (@retstat != 0)
117           return (1)
118       /*
119       **  If we're in a transaction, disallow this since it might make recovery
120       **  impossible.
121       */
122   
123       if @@trancount > 0
124       begin
125           /*
126           ** 17260, "Can't run %1! from within a transaction."
127           */
128           raiserror 17260, "sp_passwordpolicy"
129           return (1)
130       end
131       else
132       begin
133           set chained off
134       end
135       set transaction isolation level 1
136   
137       /*
138       **  Begin command processing.
139       */
140   
141       /* 
142       ** If granular permissions is not enabled then sso_role is required.
143       ** If granular permissions is enabled then the permission 
144       ** 'manage security configuration' is required.  proc_role and proc_auditperm 
145       ** will also do auditing if required. Both will also print error message if 
146       ** required.
147       */
148   
149       select @nullarg = NULL
150       execute @status = sp_aux_checkroleperm "sso_role",
151           "manage security configuration", @nullarg, @gp_enabled output
152   
153       /* For Auditing */
154       if (@gp_enabled = 0)
155       begin
156           if (proc_role("sso_role") = 0)
157               return (1)
158       end
159       else
160       begin
161           select @dummystat = proc_auditperm("manage security configuration",
162                   @status)
163       end
164   
165       if (@status != 0)
166           return (1)
167   
168       /* 
169       ** Set the command text to lowercase so that we always compare it using
170       ** the same case.
171       */
172       select @cmd = lower(@cmd), @opt1 = lower(@opt1)
173   
174       if (@cmd is null or @cmd = 'help')
175       begin
176           /* Display sp_passwordpolicy usage information */
177           select @retval = 2
178           goto usage
179       end
180       else
181       if (@cmd not in ('set', 'clear', 'list', 'validate password options',
182                   'expire login passwords', 'expire role passwords',
183                   'expire stale login passwords',
184                   'expire stale role passwords', 'regenerate keypair',
185                   'downgrade', 'prepare_downgrade'))
186       begin
187   
188           /*
189           ** 19213, "Invalid argument or unsupported command: %1!."
190           */
191           raiserror 19213, @cmd
192           /* Display sp_passwordpolicy usage information */
193           select @retval = 2
194           goto usage
195       end
196   
197       /* Check if opt1 entered is valid */
198       if (@opt1 is null and (@cmd = 'set' or @cmd = 'expire stale login passwords'
199                   or @cmd = 'expire stale role passwords'))
200       begin
201           /*
202           ** 17932, "You entered an invalid value. No
203           ** change was made."
204           */
205           raiserror 17932
206           select @retval = 2
207           goto usage
208       end
209   
210       /* Lists the option values */
211       if (@cmd = 'list')
212       begin
213           /*
214           ** Traditionally, the names stored in spt_values match some
215           ** internal name in ASE, where the maximum length is 28 to
216           ** account for quoted identifiers. In this case, spt_values
217           ** is used store option names that must match sysattributes
218           ** options with 255 chars names. The option 'systemwide password 
219           ** expiration' is 30 chars long, and it does not fit in spt_values,
220           ** and the name has been chaged to 'password expiration'.
221           ** The variable @opt1 stores the sysattributes option name,
222           ** while the variable @optname stores the mapped name in
223           ** spt_values.
224           */
225           if (@opt1 = 'systemwide password expiration')
226               select @optname = 'password expiration'
227           else
228               select @optname = @opt1
229   
230           if ((@opt1 is not null)
231                   and not exists (select 1 from master.dbo.spt_values
232                       where (type in ('PC', 'PT', 'LG', 'SP'))
233                           and name = @optname))
234           begin
235               /*
236               ** 17925, "You entered an invalid option name.
237               ** No change was made."
238               */
239               raiserror 17925
240               select @retval = 2
241               goto usage
242           end
243           if (@opt2 is not null
244                   or @opt3 is not null)
245           begin
246               /*
247               ** 17932, "You entered an invalid value. No
248               ** change was made."
249               */
250               raiserror 17932
251               select @retval = 2
252               goto usage
253           end
254   
255           if (@opt1 = 'allow password downgrade')
256           begin
257               /*
258               ** when 'allow password downgrade' is set to 0,
259               ** date is recorded in syattributes.object column.
260               ** This column is of data type int.
261               ** To retrieve data from it in date format,
262               ** convert function is used.
263               ** Date is converted to binary first and then to date format, 
264               ** ASE does not support direct date -> int conversion.
265               */
266               select @allow_pwd_dwn_status = int_value,
267                   @allow_pwd_dwn_dt_int = object from sysattributes
268               where class = 31
269               if (@allow_pwd_dwn_status = NULL)
270               begin
271                   /*
272                   ** 19679, New 15.0.2 master database.
273                   */
274                   exec sp_getmessage 19679, @msg output
275                   select 'value' = @allow_pwd_dwn_status,
276                       @msg as message
277               end
278               else if (@allow_pwd_dwn_status = 1)
279               begin
280                   /*
281                   ** 19678, Password downgrade is allowed.
282                   */
283                   exec sp_getmessage 19678, @msg output
284                   select 'value' = @allow_pwd_dwn_status,
285                       @msg as message
286               end
287               else if (@allow_pwd_dwn_status = 0)
288               begin
289                   select @allow_pwd_dwn_dt =
290                       convert(smalldatetime, convert(binary(4),
291                       @allow_pwd_dwn_dt_int))
292                   /*
293                   ** 19680, Last Password downgrade was allowed on
294                   */
295                   exec sp_getmessage 19680, @msg output
296                   select 'value' = @allow_pwd_dwn_status,
297                       @msg + " " +
298                       convert(char(20), @allow_pwd_dwn_dt) as message
299               end
300           end
301   
302           /*
303           ** 'keypair regeneration period' and 'keypair error retry wait'
304           ** accept duration specifiers and so are stored in 
305           ** master.dbo.sysattributes.char_value
306           ** While setting values sp_passwordpolicy allows varchar(30) only
307           */
308           if ((@opt1 in ('keypair regeneration period',
309                           'keypair error retry wait')) or
310                   (@opt1 = null))
311           begin
312               select @list_keypair = 1
313           end
314           if ((@opt1 not in ('allow password downgrade',
315                           'keypair regeneration period', 'keypair error retry wait')) or
316                   (@opt1 = null))
317           begin
318               select @list_nonkeypair = 1
319           end
320   
321           select 'Policy_option' = convert(VARCHAR(30), a.object_cinfo),
322               'Curr_value' = convert(VARCHAR(30), a.char_value), 'Min_value' = s.low,
323               'Max_value' = s.high
324           from master.dbo.spt_values s, master.dbo.sysattributes a,
325               master.dbo.sysmessages m
326           where (@list_keypair = 1
327                   and s.name = a.object_cinfo
328                   and (s.msgnum = m.error)
329                   and (m.langid = null) /* restrict to us_english only */
330                   and (s.type = 'SP')
331                   and (a.object_cinfo = @opt1 or @opt1 is null)
332                   and (a.object_cinfo in ('keypair regeneration period',
333                           'keypair error retry wait')))
334           UNION
335           select 'Policy_option' = convert(VARCHAR(30), a.object_cinfo),
336               'Curr_value' = convert(VARCHAR(30), a.int_value), 'Min_value' = s.low,
337               'Max_value' = s.high
338           from master.dbo.spt_values s, master.dbo.sysattributes a,
339               master.dbo.sysmessages m
340           where (@list_nonkeypair = 1
341                   and ((s.name = a.object_cinfo) or
342                       (a.object_cinfo = 'systemwide password expiration'
343                           and s.name = 'password expiration'))
344                   and (s.msgnum = m.error)
345                   and (m.langid = null) /* restrict to us_english only */
346                   and (s.type in ('PC', 'LG', 'SP'))
347                   and (a.object_cinfo = @opt1 or @opt1 is null)
348                   and (a.object_cinfo not in ('keypair regeneration period',
349                           'keypair error retry wait',
350                           'allow password downgrade')))
351           return 0
352       end
353   
354       if (@cmd like '%downgrade')
355       begin
356           select @retval = password_admin('set', @cmd)
357           return @retval
358       end
359   
360       if (@cmd = 'validate password options')
361       begin
362           /* 
363           ** @opt1 and @opt2 are not passed to password_admin(),
364           ** hence check for null value here itself.
365           */
366           if ((@opt1 is not null) or (@opt2 is not null))
367           begin
368               /*
369               ** 17932, "You entered an invalid value. No
370               ** change was made."
371               */
372               raiserror 17932
373               select @retval = 2
374               goto usage
375           end
376   
377           select @retval = password_admin('set', 'policy', @cmd)
378           select @count1 = 0
379           select @count2 = 1
380           select @return_status = 0
381   
382           /* 
383           ** create a temporary table to store the information of validation 
384           ** tests.
385           */
386           create table #helpdisplay
387           (
388               validation_step varchar(50),
389               result varchar(5),
390               validation_test varchar(65)
391           )
392           /*
393           ** Each validation test is represented by two bits. 
394           ** If odd bit of the corresponding two bits for the test is set to 1,
395           ** the result is NA.
396           ** If even bit is set to 1, then result is Pass
397           ** else if even bit is set to 0, then result is Fail.
398           */
399           while (@count1 <= 10)
400           begin
401               if (@retval & power(2, @count1)) != 0
402                   select @result = "NA"
403               else if (@retval & power(2, @count2)) != 0
404                   select @result = "Pass"
405               else
406               begin
407                   select @result = "Fail"
408                   select @return_status = 1
409               end
410   
411               if @count1 = 0
412               begin
413                   select @validation_step = "min alpha in password"
414                   select @validation_test = "minalpha >= (minupper + minlower)"
415               end
416               else if @count1 = 2
417               begin
418                   select @validation_step = "minimum password length - 1"
419                   select @validation_test = "minlength >= (mindigit + minspc + minalpha)"
420               end
421               else if @count1 = 4
422               begin
423                   select @validation_step = "minimum password length - 2"
424                   select @validation_test = "minlength >= (mindigit + minspc + minupper + minlower)"
425               end
426               else if @count1 = 6
427               begin
428                   select @validation_step = "maximum password length - 1"
429                   select @validation_test = "max password length >= (mindigit + minspc + minalpha)"
430               end
431               else if @count1 = 8
432               begin
433                   select @validation_step = "maximum password length - 2"
434                   select @validation_test = "max password length >= (mindigit + minspc + minupper + minlower)"
435               end
436               else if @count1 = 10
437               begin
438                   select @validation_step = "password exp warn interval"
439                   select @validation_test = "pwdexpwarn <= pwdexp"
440               end
441   
442               insert into #helpdisplay values (@validation_step, @result, @validation_test)
443               select @count1 = @count1 + 2
444               select @count2 = @count2 + 2
445           end
446           exec sp_autoformat @fulltabname = #helpdisplay,
447               @selectlist = "'Validation Step' = validation_step, 'Pass/Fail/NA' = result,
448                          'Validation Test' = validation_test"
449           drop table #helpdisplay
450   
451           return @return_status
452       end
453   
454       /* Initialize constants need for replication */
455       select @db_rep_level_all = - 1,
456           @db_rep_level_none = 0,
457           @db_rep_level_l1 = 1,
458           @lt_rep_get_failed = - 2,
459           @lt_rep_all = 2048,
460           @lt_rep_l1 = 4096
461   
462       if (@opt1 != 'allow password downgrade')
463       begin
464           /* Get the replication status of the 'master' database */
465           select @log_for_rep = getdbrepstat(1)
466   
467           if (@log_for_rep = @lt_rep_get_failed)
468           begin
469               /*
470               ** 18409, "The built-in function getdbrepstat() failed. Please
471               ** see the other messages printed along with this message."
472               */
473               raiserror 18409, "getdbrepstat"
474               return 1
475           end
476   
477           /* Convert the replication status to boolean */
478           if ((@log_for_rep & @lt_rep_all = @lt_rep_all) or
479                   (@log_for_rep & @lt_rep_l1 = @lt_rep_l1))
480   
481               select @log_for_rep = 1
482           else
483               select @log_for_rep = 0
484   
485       end
486       else
487           select @log_for_rep = 0
488   
489       /*
490       ** If we are logging this system procedure for replication, we must be in
491       ** the 'master' database to avoid creating a multi-database transaction
492       ** which could make recovery of the 'master' database impossible.
493       */
494       if (@log_for_rep = 1) and (upper(db_name()) != upper("master"))
495       begin
496           /*
497           ** 18388, "You must be in the master database in order to
498           **	   run '%1!'"
499           */
500           raiserror 18388, "sp_passwordpolicy"
501           return (1)
502       end
503   
504       /* 
505       ** If replication is active, open the transaction.
506       **
507       ** IMPORTANT: The name rs_logexec is significant and is used by
508       ** replication.
509       */
510       if (@log_for_rep = 1)
511       begin
512           begin tran rs_logexec
513       end
514   
515       if (@cmd = 'expire login passwords')
516       begin
517           /* 
518           ** @opt2 is not passed to password_admin(),
519           ** hence check for null value here itself.
520           */
521           if (@opt2 is not null)
522           begin
523               /*
524               ** 17932, "You entered an invalid value. No
525               ** change was made."
526               */
527               raiserror 17932
528               select @retval = 2
529               goto usage
530           end
531   
532           if (@opt1 is null)
533           begin
534               /*
535               ** 17047, "The commands 'expire login passwords' and 
536               ** 'expire role passwords' require an argument."
537               */
538               raiserror 17047
539               select @retval = 2
540               goto usage
541           end
542   
543           if not exists (select name from master.dbo.syslogins
544                   where name like @opt1
545                       and ((status & 512) != 512)) /* not LOGIN PROFILE */
546           begin
547               /*
548               ** 17231, "No login with the specified name exists."
549               **      (was "Invalid Login user.")
550               */
551               raiserror 17231
552               select @retval = 1
553           end
554           else
555           begin
556               select @retval = password_admin('set', 'policy', @cmd, @opt1)
557           end
558       end
559       else if (@cmd = 'expire role passwords')
560       begin
561           /* 
562           ** @opt2 is not passed to password_admin(),
563           ** hence check for null value here itself.
564           */
565           if (@opt2 is not null)
566           begin
567               /*
568               ** 17932, "You entered an invalid value. No
569               ** change was made."
570               */
571               raiserror 17932
572               select @retval = 2
573               goto usage
574           end
575   
576           if (@opt1 is null)
577           begin
578               /*
579               ** 17047, "The commands 'expire login passwords' and 
580               ** 'expire role passwords' require an argument."
581               */
582               raiserror 17047
583               select @retval = 2
584               goto usage
585           end
586   
587           if not exists (select name from master.dbo.syssrvroles
588                   where name like @opt1 and password is not null)
589           begin
590               /*
591               ** 19844, "There is no password protected role like '%1!'."
592               */
593               raiserror 19844, @opt1
594               select @retval = 1
595           end
596           else
597           begin
598               select @retval = password_admin('set', 'policy', @cmd, @opt1)
599           end
600       end
601       else if ((@cmd = 'expire stale login passwords') or
602               (@cmd = 'expire stale role passwords'))
603       begin
604           /* 
605           ** @opt2 is not passed to password_admin(),
606           ** hence check for null value here itself.
607           */
608           if (@opt2 is not null)
609           begin
610               /*
611               ** 17932, "You entered an invalid value. No
612               ** change was made."
613               */
614               raiserror 17932
615               select @retval = 2
616               goto usage
617           end
618   
619           select @entered_date = @opt1
620   
621           /* 
622           ** Convert the entered date and time into required form 
623           ** of mm dd yy hh:mm:ss:zzzAM(PM) using style number 9
624           */
625           select @opt1 = convert(char(30), @entered_date, 9)
626   
627           select @retval = password_admin('set', 'policy', @cmd, @opt1)
628       end
629   
630   
631   
632       /* Changes or inserts the option value as specified. */
633       if (@cmd in ('set', 'regenerate keypair'))
634       begin
635           if (@cmd = 'regenerate keypair')
636           begin
637               /* If no datetime is specified then generate keypair now */
638               if (@opt1 is null)
639               begin
640                   select @opt1 = getdate()
641               end
642               /*
643               ** If only time is specified and it is passed,
644               ** generate keypair next day at @opt1 time.
645               ** Note: For only time: datepart() assume January 1, 1900
646               */
647               else if ((datepart(yy, @opt1) = 1900 and
648                           datepart(mm, @opt1) = 1 and
649                           datepart(dd, @opt1) = 1 and
650                           /* Dont't mislead with datetime like January 1, 1900 01:45AM */
651                           charindex("1900", @opt1) = 0)
652                       and
653                       (datepart(hh, @opt1) < datepart(hh, getdate()) or
654                           (datepart(hh, @opt1) = datepart(hh, getdate()) and
655                               datepart(mi, @opt1) < datepart(mi, getdate()))))
656               begin
657                   select @specified_datetime = substring(convert(char(20), getdate(), 0), 1, 12) + @opt1
658                   select @opt1 = dateadd(dd, 1, @specified_datetime)
659               end
660               select @retval = password_admin('set', 'regenerate keypair',
661                       @opt1)
662           end
663           else
664           begin
665               select @retval = password_admin(@cmd, 'policy', @opt1,
666                       @opt2, @opt3, @dummy)
667           end
668   
669           if ((@retval = 0) and ((@cmd = 'regenerate keypair') or
670                       (@opt1 = 'keypair regeneration period')))
671           begin
672               /* Display the current date if none was specified, i.e. NOW. */
673               if @opt1 = NULL
674                   select @nextregen = getdate()
675               else
676                   select @nextregen = @@nextkpgendate
677               /*
678               ** 19633, "Network password encryption keypair will be 
679               ** regenerated at %1!."
680               */
681               raiserror 19633, @nextregen
682           end
683   
684   
685       end
686   
687       /* Changes the option values to hardcoded values */
688       else if (@cmd = 'clear')
689       begin
690           /* 
691           ** @opt2 is not passed to password_admin(),
692           ** hence check for null value here itself.
693           */
694           if (@opt2 is not null
695                   or @opt3 is not null)
696           begin
697               /*
698               ** 17932, "You entered an invalid value. No
699               ** change was made."
700               */
701               raiserror 17932
702               select @retval = 2
703               goto usage
704   
705           end
706           else if (@opt1 = 'allow password downgrade')
707           begin
708               /*
709               ** 19658, "Option 'allow password downgrade' cannot be 
710               ** unset using 'clear' command"
711               */
712               raiserror 19658
713               select @retval = 2
714               goto usage
715           end
716           else
717           begin
718               select @retval = password_admin(@cmd, 'policy', @opt1)
719   
720   
721           end
722       end
723   
724       if (@retval = 0)
725       begin
726           /*
727           ** If the 'master' database is marked for replication, the T-SQL builtin
728           ** logexec(), will log the execution of this system procedure.
729           */
730           if (@log_for_rep = 1)
731           begin
732               if (logexec(1) != 1)
733               begin
734                   /*
735                   ** 17756, "The execution of the stored procedure '%1!'
736                   ** 	   in database '%2!' was aborted because there
737                   **	   was an error in writing the replication log
738                   **	   record."
739                   */
740                   raiserror 17756, "sp_passwordpolicy", "master"
741                   goto cleanup
742               end
743           end
744   
745   
746           commit transaction
747           return 0
748       end
749   
750   usage:
751       if (@retval = 2)
752       begin
753           print "sp_ passwordpolicy Usage: sp_passwordpolicy 'help'"
754           print "sp_ passwordpolicy Usage: sp_passwordpolicy command [, option1 [, option2 [, option3]]]"
755           print "sp_passwordpolicy commands:"
756           print "sp_passwordpolicy 'set',"
757           print "                  {'enable last login updates' | 'disallow simple passwords' |"
758           print "                   'min digits in password' | 'min alpha in password' |"
759           print "                   'min special char in password' | 'min upper char in password' |"
760           print "                   'min lower char in password' | 'password exp warn interval' |"
761           print "                   'systemwide password expiration' | 'minimum password length' |"
762           print "                   'maximum failed logins' | 'expire login' |"
763           print "                   'allow password downgrade' | 'keypair error retry wait' |"
764           print "			  'keypair error retry count' | 'unique keypair per session'},"
765           print "                  'value'"
766           print "sp_passwordpolicy 'set', 'keypair regeneration period',"
767           print "			 {'regeneration_period' |"
768           print "			  null, 'datetime' | "
769           print "			  'regeneration_period', 'datetime'}"
770           print "sp_passwordpolicy 'list',"
771           print "                  ['enable last login updates' | 'disallow simple passwords' |"
772           print "                   'min digits in password' | 'min alpha in password' |"
773           print "                   'min special char in password' | 'min upper char in password' |"
774           print "                   'min lower char in password' | 'password exp warn interval' |"
775           print "                   'systemwide password expiration' | 'minimum password length' |"
776           print "                   'maximum failed logins' | 'expire login' |"
777           print "                   'allow password downgrade' |"
778           print "			  'keypair error retry wait' | 'keypair error retry count' |"
779           print "			  'keypair regeneration period' | 'unique keypair per session']"
780           print "sp_passwordpolicy 'clear',"
781           print "                  {'enable last login updates' | 'disallow simple passwords' |"
782           print "                   'min digits in password' | 'min alpha in password' |"
783           print "                   'min special char in password' | 'min upper char in password' |"
784           print "                   'min lower char in password' | 'password exp warn interval' |"
785           print "                   'systemwide password expiration' | 'minimum password length' |"
786           print "                   'maximum failed logins' | 'expire login' |"
787           print "			  'keypair error retry wait' | 'keypair error retry count' |"
788           print "			  ''keypair regeneration period' | 'unique keypair per session'}"
789           print "sp_passwordpolicy 'expire login passwords'[, '{loginame | wildcard}']"
790           print "sp_passwordpolicy 'expire role passwords'[, '{rolename | wildcard}']"
791           print "sp_passwordpolicy 'expire stale login passwords', 'datetime'"
792           print "sp_passwordpolicy 'expire stale role passwords', 'datetime'"
793           print "sp_passwordpolicy 'regenerate keypair'[, 'datetime']"
794           print "sp_passwordpolicy 'validate password options'"
795       end
796   
797   cleanup:
798   
799   
800       if (@log_for_rep = 1)
801       begin
802           rollback tran rs_logexec
803       end
804   
805       if (@cmd = 'help')
806       begin
807           return 0
808       end
809       else
810       begin
811           return 1
812       end
813   


exec sp_procxmode 'sp_passwordpolicy', 'AnyMode'
go

Grant Execute on sp_passwordpolicy to public
go
RESULT SETS
sp_passwordpolicy_rset_004
sp_passwordpolicy_rset_003
sp_passwordpolicy_rset_002
sp_passwordpolicy_rset_001

DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 512
 MCTR 4 Conditional Begin Tran or Commit Tran 746
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch @nextregen: datetime = int 676
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 446
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
232
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_cinfo}
Uncovered: [class, attribute, object_type, object, object_info1, object_info2, object_info3]
327
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
330
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_cinfo}
331
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_cinfo}
Uncovered: [class, attribute, object_type, object, object_info1, object_info2, object_info3]
341
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_cinfo}
342
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
343
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 268
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 VRUN 4 Variable is read and not initialized @dummy 666
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_passwordpolicy  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MNAC 3 Not using ANSI 'is null' 269
 MNAC 3 Not using ANSI 'is null' 310
 MNAC 3 Not using ANSI 'is null' 316
 MNAC 3 Not using ANSI 'is null' 329
 MNAC 3 Not using ANSI 'is null' 345
 MNAC 3 Not using ANSI 'is null' 673
 MNER 3 No Error Check should check return value of exec 274
 MNER 3 No Error Check should check return value of exec 283
 MNER 3 No Error Check should check return value of exec 295
 MNER 3 No Error Check should check @@error after insert 442
 MNER 3 No Error Check should check return value of exec 446
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 243
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 278
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 308
 MUCO 3 Useless Code Useless Brackets 314
 MUCO 3 Useless Code Useless Brackets 326
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 354
 MUCO 3 Useless Code Useless Brackets 360
 MUCO 3 Useless Code Useless Brackets 366
 MUCO 3 Useless Code Useless Brackets 399
 MUCO 3 Useless Code Useless Brackets 462
 MUCO 3 Useless Code Useless Brackets 467
 MUCO 3 Useless Code Useless Brackets 478
 MUCO 3 Useless Code Useless Brackets 501
 MUCO 3 Useless Code Useless Brackets 510
 MUCO 3 Useless Code Useless Brackets 515
 MUCO 3 Useless Code Useless Brackets 521
 MUCO 3 Useless Code Useless Brackets 532
 MUCO 3 Useless Code Useless Brackets 559
 MUCO 3 Useless Code Useless Brackets 565
 MUCO 3 Useless Code Useless Brackets 576
 MUCO 3 Useless Code Useless Brackets 601
 MUCO 3 Useless Code Useless Brackets 608
 MUCO 3 Useless Code Useless Brackets 633
 MUCO 3 Useless Code Useless Brackets 635
 MUCO 3 Useless Code Useless Brackets 638
 MUCO 3 Useless Code Useless Brackets 647
 MUCO 3 Useless Code Useless Brackets 669
 MUCO 3 Useless Code Useless Brackets 688
 MUCO 3 Useless Code Useless Brackets 694
 MUCO 3 Useless Code Useless Brackets 706
 MUCO 3 Useless Code Useless Brackets 724
 MUCO 3 Useless Code Useless Brackets 730
 MUCO 3 Useless Code Useless Brackets 732
 MUCO 3 Useless Code Useless Brackets 751
 MUCO 3 Useless Code Useless Brackets 800
 MUCO 3 Useless Code Useless Brackets 805
 MUIN 3 Column created using implicit nullability 386
 QAFM 3 Var Assignment from potentially many rows 266
 QCRS 3 Conditional Result Set 275
 QCRS 3 Conditional Result Set 284
 QCRS 3 Conditional Result Set 296
 QCRS 3 Conditional Result Set 321
 QCTC 3 Conditional Table Creation 386
 QGWO 3 Group by/Distinct/Union without order by 321
 QISO 3 Set isolation level 135
 QNAJ 3 Not using ANSI Inner Join 324
 QNAJ 3 Not using ANSI Inner Join 338
 QPNC 3 No column in condition 326
 QPNC 3 No column in condition 331
 QPNC 3 No column in condition 340
 QPNC 3 No column in condition 347
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class}
268
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
328
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
344
 QSWV 3 Sarg with variable @optname, Candidate Index: spt_values.spt_valuesclust clustered(number, type) U 233
 QUNI 3 Check Use of 'union' vs 'union all' 321
 VNRD 3 Variable is not read @dummystat 161
 VNRD 3 Variable is not read @db_rep_level_all 455
 VNRD 3 Variable is not read @db_rep_level_none 456
 VNRD 3 Variable is not read @db_rep_level_l1 457
 VUNU 3 Variable is not used @dbname 90
 MRST 2 Result Set Marker 275
 MRST 2 Result Set Marker 284
 MRST 2 Result Set Marker 296
 MRST 2 Result Set Marker 321
 MSUB 2 Subquery Marker 231
 MSUB 2 Subquery Marker 543
 MSUB 2 Subquery Marker 587
 MTR1 2 Metrics: Comments Ratio Comments: 36% 56
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 76 = 85dec - 11exi + 2 56
 MTR3 2 Metrics: Query Complexity Complexity: 355 56
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, m=master..sysmessages, sv=master..spt_values} 0 321
 PRED_QUERY_COLLECTION 2 {a=master..sysattributes, m=master..sysmessages, sv=master..spt_values} 0 335

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysmessages (1)  
reads table master..syslogins (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..sysattributes (1)  
reads table master..syssrvroles (1)  
writes table sybsystemprocs..sp_passwordpolicy_rset_002 
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
reads table master..spt_values (1)  
writes table sybsystemprocs..sp_passwordpolicy_rset_001 
reads table sybsystemprocs..sysattributes  
calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   writes table sybsystemprocs..sp_autoformat_rset_002 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   writes table sybsystemprocs..sp_autoformat_rset_001 
   read_writes table tempdb..#colinfo_af (1) 
   reads table tempdb..systypes (1)  
   reads table master..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
writes table tempdb..#helpdisplay (1) 
writes table sybsystemprocs..sp_passwordpolicy_rset_003 
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
writes table sybsystemprocs..sp_passwordpolicy_rset_004 

CALLERS
called by proc sybsystemprocs..sp_downgrade