DatabaseProcApplicationCreatedLinks
sybsystemprocssp_displaylogin  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	5.0	1.0	10/22/91	sproc/src/displaylogin */
4     
5     /*
6     ** Messages for "sp_displaylogin"     17943
7     **
8     ** 17231, "No login with the specified name exists."
9     ** 17943, "Suid: %1!"
10    ** 17944, "Loginame: %1!"
11    ** 17945, "Fullname: %1!"
12    ** 17949, "Locked: %1!"
13    ** 17950, "Date of Last Password Change: %1!"
14    ** 17951, "Configured Authorization:"
15    ** 18334, "Default Database: %1!"
16    ** 18335, "Default Language: %1!"
17    ** 18675, "Password expiration interval: %1!"
18    ** 18676, "Password Expired: %1!"
19    ** 18677, "Minimum Password Length: %1!"
20    ** 18678, "Maximum failed logins: %1!"
21    ** 18679, "Current failed login attempts: %1!"
22    ** 18914, "Auto Login Script: %1!" 
23    ** 19258, "Authenticate with: %1!"
24    ** 19568, "No login with specified Server User ID exists"
25    ** 19644, "Login Password Encryption: %1!"
26    ** 19645, "Password column corrupted"
27    ** 19681, "Last login date: %1!"
28    ** 19682, "Account locked by ASE by manually executing sp_locklogin"
29    ** 19683, "Account locked by ASE since account was inactive"
30    ** 19684, "Account locked by ASE due to failed login attempts reaching max failed logins."
31    ** 19685, "Account locked by ASE since login has not transitioned to SHA-256, after password downgrade."
32    ** 17032, "Account locked by ASE automatically since account was inactive"
33    ** 19939, "Exempt inactive lock: %1!"
34    ** 18436, "Login Profile: %1!"
35    */
36    
37    create or replace procedure sp_displaylogin
38        @loginame varchar(255) = NULL as
39    
40        declare @suid int /* suid of person to display */
41        declare @name varchar(30) /* login name */
42        declare @suid_loginame varchar(30) /* login name obtained through suid */
43        declare @fullname varchar(30) /* login owner's full name */
44        declare @defdb varchar(30)
45        declare @deflang varchar(30)
46        declare @procname varchar(255) /* login script name */
47        declare @procid int /* proc id for the login script */
48        declare @auths int
49        declare @status int
50        declare @auth_status int
51        declare @pwdate datetime
52        declare @thisauth int
53        declare @authname varchar(30)
54        declare @msg varchar(1024)
55        declare @msg_yes_or_no varchar(30)
56        declare @local_msgbuf char(30)
57        declare @dummy int
58        declare @role_status int
59        declare @enable_login_role int /* value of the status bit which is used
60        ** for enabling a role
61        */
62        declare @loginexp int /* value of login expiration */
63        declare @passwdexp int /* value of password expiration */
64        declare @minpwdlen int /* value of minimum password length */
65        declare @pwdexpired varchar(3) /* has the login's password 
66        ** expired(YES/NO) ?? 
67        */
68        declare @maxfailedlogins int /* value of maximum failed logins */
69        declare @currentfails int /* value of current failed logins count */
70        declare @allauth int /* authentication mechanisms mask */
71        declare @passeclass int /* class number of PASSWD_SECURITY 
72        ** class in master.dbo/sysattributes
73        */
74        declare @row_count int /* value of number of rows selected */
75        declare @authmech varchar(15) /* Authentication mechanism */
76        declare @locked varchar(4) /* value of locked field */
77        declare @configroles varchar(1024) /* value of all configured roles stored
78        ** with delimiter
79        */
80        declare @password varbinary(128)
81        declare @vers1 tinyint /* Contains byte 2 of password column */
82        declare @vers2 tinyint /* Contains byte 32 of password column */
83        declare @encralgo varchar(30) /* Contains type of encryption algorithm
84        ** information
85        */
86        declare @lastlogindate datetime,
87            @locksuid int,
88            @lockreason int,
89            @lockdate datetime,
90            @lockreasonmsg varchar(1024), /* Contains text for locked reason */
91            @locksuidname varchar(30), /* Contains login name who locked 
92            ** the account to be displayed
93            */
94            @exemptinactivelock int, /* Indicates whether login is exempted
95            ** from getting locked due to inactivity
96            */
97            @exemptinactivelock_mask int, /* Mask bit in status for option
98            ** 'exempt inactive lock'
99            */
100           @max_sdr_srid int, /* Max system defined role id */
101           @lrid int, /* Login profile id. */
102           @lrname varchar(30) /* Login profile name. */
103   
104       declare @nullarg varchar(1)
105       declare @gp_enabled int
106       declare @status1 int
107       declare @status2 int
108   
109       select @status1 = 1
110       select @status2 = 1
111   
112       if @@trancount = 0
113       begin
114           set chained off
115       end
116   
117       set transaction isolation level 1
118   
119       /* Initialize variables */
120       select @enable_login_role = 1
121       select @exemptinactivelock_mask = 8
122       select @max_sdr_srid = 31 /* Value of server's MAX_SDR_ROLES */
123   
124       if @loginame is NULL
125           select @loginame = suser_name()
126   
127       /*
128       **  Only accounts with SSO authorization
129       **  can display other login info.
130       **  Call proc_role() with the required SSO role.
131       */
132   
133       /*
134       ** If @loginame contains only digits from 0-9 of any length
135       ** then consider it as suid and get the corresponding name
136       ** else consider it as name
137       */
138       if (isnumeric(@loginame) > 0)
139       begin
140           /*
141           ** Check whether loginame contains "-","$" or "."
142           ** since they are allowed by isnumeric() function
143           */
144           if @loginame not like '%[-$.]%'
145           begin
146               select @suid = convert(int, @loginame)
147               select @suid_loginame = name
148               from master.dbo.syslogins where suid = @suid
149                   and ((status & 512) != 512) /* not LOGIN PROFILE */
150               select @row_count = @@rowcount
151               if @row_count != 0 /* Got a name for vaild suid */
152               begin
153                   /* Assign the @suid_loginame to @loginame for furthur use */
154                   select @loginame = @suid_loginame
155               end
156           end
157       end
158   
159       if (@loginame != suser_name())
160       begin -- {
161           /*
162           **  If granular permissions is not enabled, 
163           **  Accounts that have either sa_role and sso_role can execute it.
164           **  If granular permissions is enabled then users with 
165           ** 'manage any login' or 'manage server' permission can execute it.
166           */
167           select @nullarg = NULL
168           execute @status1 = sp_aux_checkroleperm "sa_role", "manage any login",
169               @nullarg, @gp_enabled output
170   
171           if (@status1 != 0)
172           begin
173               execute @status2 = sp_aux_checkroleperm "sso_role",
174                   "manage server", @nullarg, @gp_enabled output
175   
176               if (@gp_enabled = 0)
177               begin
178                   if (@status2 != 0)
179                   begin
180                       /* 
181                       ** proc_role() will raise permission errors
182                       ** and send audit records to the audit trail.
183                       */
184                       select @dummy = proc_role("sa_role")
185                       select @dummy = proc_role("sso_role")
186                       return (1)
187                   end
188               end
189               else
190               begin
191                   if (@status2 != 0)
192                   begin
193                       /* 
194                       ** proc_role() will raise permission errors
195                       ** and send audit records to the audit trail.
196                       */
197                       select @dummy =
198                           proc_auditperm("manage any login",
199                               @status1)
200                       select @dummy =
201                           proc_auditperm("manage server",
202                               @status2)
203                       return (1)
204                   end
205               end
206           end
207   
208           /* 
209           ** Send apropriate audit records, already determined user has one
210           ** of the roles or the permission. 
211           */
212           if (@gp_enabled = 0)
213           begin
214               if (@status1 = 0)
215                   select @dummy = proc_role("sa_role")
216               if (@status2 = 0)
217                   select @dummy = proc_role("sso_role")
218           end
219           else
220           begin
221               if (@status1 = 0)
222                   select @dummy = proc_auditperm("manage any login",
223                           @status1)
224               if (@status2 = 0)
225                   select @dummy = proc_auditperm("manage server",
226                           @status2)
227           end
228       end -- }
229   
230       if @row_count = 0
231       begin
232           /* 19568, "No login with specified Server User ID exists." */
233           raiserror 19568
234           return (1)
235       end
236   
237       /*
238       ** For getting the number of row/rows selected in the result set of cursor
239       ** execute the query and get the rowcount in @row_count 
240       */
241   
242       select @row_count = count(1) from master.dbo.syslogins where name like @loginame
243           and ((status & 512) != 512) /* not LOGIN PROFILE */
244   
245       /*
246       ** Declare a read only cursor to select all the required fields to be displayed
247       ** after processing them for each login 
248       */
249       declare display_login cursor for
250       select suid, password, name, fullname, dbname, language, procid,
251           status, pwdate, logincount, lastlogindate, locksuid, lockreason, lockdate, lpid
252       from master.dbo.syslogins
253       where name like @loginame
254           and ((status & 512) != 512) /* not LOGIN PROFILE */
255   
256       for read only
257   
258       /* Open the cusor to fetch the contents in local variables */
259       open display_login
260   
261       fetch display_login into
262           @suid, @password, @name, @fullname, @defdb, @deflang, @procid,
263           @status, @pwdate, @currentfails, @lastlogindate, @locksuid, @lockreason,
264           @lockdate, @lrid
265   
266       select @loginame = @name
267   
268       if @suid is NULL
269       begin
270           /*
271           ** 17231, "No login with the specified name exists."
272           **	(was "Invalid Login user.")
273           */
274           exec sp_getmessage 17231, @msg output
275           print @msg
276           return (1)
277       end
278   
279       if @row_count <> 1
280       begin
281           /*
282           ** create a temporary table to store the information of logins matching
283           ** a wildcard expression
284           */
285           create table #helpdisplay
286           (
287               suid int,
288               name varchar(30),
289               fullname varchar(30) NULL,
290               dbname varchar(30) NULL,
291               language varchar(30) NULL,
292               procname varchar(255) NULL,
293               locked varchar(4),
294               pwdate datetime,
295               passwdexp int,
296               pwdexpired varchar(3),
297               minpwdlen int,
298               maxfailedlogins int,
299               currentfails int NULL,
300               authmech varchar(15) NULL,
301               configroles varchar(1024) NULL,
302               encralgo varchar(30) NULL,
303               lastlogindate datetime NULL,
304               exemptinactivelock int,
305               loginprofile varchar(30) NULL
306           )
307       end
308   
309       while @@sqlstatus <> 2
310       begin
311           if @@sqlstatus = 1
312           begin
313               /*
314               ** 18999, "An error occurred while fetching data from a temporary
315               ** table. If there are no other error messages and this error
316               ** persists, please contact Sybase Technical Support."
317               */
318               raiserror 18999
319               close display_login
320               deallocate cursor display_login
321               return (1)
322           end
323           /*
324           ** Process the required parameters and store values in local parameters
325           ** after processing for display purpose
326           */
327   
328           select @procname = object_name(@procid, db_id(@defdb))
329   
330           if (@status & 2) = 2 /* LOCKED */
331           begin
332               /* 17011, "yes" */
333               exec sp_getmessage 17011, @msg_yes_or_no output
334           end
335           else
336           begin
337               /* 17010, "no" */
338               exec sp_getmessage 17010, @msg_yes_or_no output
339           end
340           select @locked = upper(@msg_yes_or_no)
341   
342           begin
343               /* NOTE: For all password control related information printed
344               ** below, a check is made first if the specified login has 
345               ** a value configured for the attribute in sysattributes. If
346               ** there is no value, then the systemwide default is picked from
347               ** sysconfigures
348               */
349   
350               /* get the value of PASSWD_SECURITY class */
351               select @passeclass = class from master.dbo.sysattributes where
352                   object_type = "PS"
353   
354               /* Password expiration interval */
355               select @passwdexp = int_value from master.dbo.sysattributes
356               where class = @passeclass AND attribute = 0 AND object = @suid
357                   AND object_cinfo = "login"
358   
359               if @passwdexp is NULL
360                   select @passwdexp = int_value from master.dbo.sysattributes
361                   where class = 27 AND attribute = 7
362   
363               if @passwdexp is NULL
364                   select @passwdexp = value
365                   from master.dbo.sysconfigures
366                   where name = "systemwide password expiration"
367   
368               /* Has the login's password expired?? */
369               /*
370               ** Check if password complexity option - 'expire login'
371               ** is set for this login.
372               */
373               select @loginexp = (@status & (hextoint("0x0004")))
374               if @loginexp = 4
375                   select @pwdexpired = "YES"
376               else
377               if @passwdexp = 0
378                   select @pwdexpired = "NO"
379               else
380               if (datediff(dd, @pwdate, getdate()) > @passwdexp)
381                   select @pwdexpired = "YES"
382               else
383                   select @pwdexpired = "NO"
384   
385               /* Minimum password length */
386               select @minpwdlen = int_value from master.dbo.sysattributes
387               where class = @passeclass AND attribute = 1 AND object = @suid
388                   AND object_cinfo = "login"
389   
390               if @minpwdlen is NULL
391                   select @minpwdlen = int_value from master.dbo.sysattributes
392                   where class = 27 AND attribute = 8
393   
394               if @minpwdlen is NULL
395                   select @minpwdlen = value
396                   from master.dbo.sysconfigures
397                   where name = "minimum password length"
398   
399               /* Maximum failed logins */
400               select @maxfailedlogins = int_value from master.dbo.sysattributes
401               where class = @passeclass AND attribute = 2 AND object = @suid
402                   AND object_cinfo = "login"
403   
404               if @maxfailedlogins is NULL
405                   select @maxfailedlogins = int_value from master.dbo.sysattributes
406                   where class = 27 AND attribute = 9
407   
408               if @maxfailedlogins is NULL
409                   select @maxfailedlogins = value
410                   from master.dbo.sysconfigures
411                   where name = "maximum failed logins"
412   
413               /* Count of current failed logins */
414               select @currentfails = logincount from master.dbo.syslogins
415               where suid = @suid
416   
417               /* Set a mask with all the authentication bits */
418               select @allauth = low
419               from master.dbo.spt_values
420               where type = "ua" and upper(name) = 'AUTH_MASK'
421   
422               if ((@status & @allauth) = 0)
423                   select @auth_status = @allauth
424               else
425                   select @auth_status = (@status & @allauth)
426   
427               select @local_msgbuf = NULL
428   
429               /*
430               ** Select the authentication mechanism name from spt_values except
431               ** 'AUTH_MASK' which is used only as a value and not as a valid
432               ** authmech name to be displayed.
433               */
434               select @local_msgbuf = convert(char(15), name)
435               from master.dbo.spt_values
436               where low = @auth_status and type = "ua"
437                   and name not in ('AUTH_MASK')
438   
439               if (@local_msgbuf is null or @local_msgbuf = 'ANY')
440               begin
441                   select @local_msgbuf = upper(name) from master.dbo.spt_values
442                   where type = 'ua' and upper(name) = 'AUTH_DEFAULT'
443               end
444               select @authmech = @local_msgbuf
445   
446               /* Select password encryption algorithm information */
447               select @vers1 = substring(@password, 2, 1)
448               select @vers2 = substring(@password, 32, 1)
449   
450               /* Values 1 to 5 and 7 are valid for @vers1 field */
451               if @vers1 <= 5 and @vers1 >= 1
452               begin
453                   if @vers2 = NULL
454                       select @encralgo = "SYB-PROP"
455                   else if @vers2 = 6
456                       select @encralgo = "SYB-PROP,SHA-256"
457               end
458               else if @vers1 = 7 and @vers2 is not NULL
459               begin
460                   select @encralgo = "SHA-256"
461               end
462               else
463               begin
464                   /*
465                   ** 19645, "Password column corrupted"
466                   */
467                   exec sp_getmessage 19645, @msg output
468                   select @encralgo = @msg
469               end
470   
471               /* Exempt inactive lock */
472               if (@status & @exemptinactivelock_mask) = @exemptinactivelock_mask
473               begin
474                   select @exemptinactivelock = 1
475               end
476               else
477               begin
478                   select @exemptinactivelock = 0
479               end
480   
481               /* 
482               ** Retrieve the id of the default login profile if there is
483               ** no login profile associated directly.
484               */
485               if (@lrid = NULL)
486               begin
487                   /* Retreive the id of the default login profile. */
488                   select @lrid = object from master.dbo.sysattributes
489                   where class = 39 and attribute = 4 and
490                       object_type = "LR"
491               end
492               /*
493               ** There is no login profile associated with the login directly
494               ** or through a default login profile.
495               */
496               if (@lrid = NULL)
497               begin
498                   select @lrname = NULL
499               end
500               else
501               begin
502                   select @lrname = name from master.dbo.syslogins
503                   where suid = @lrid
504               end
505   
506           end
507           if @row_count = 1
508           begin
509               /* Output the messages */
510   
511               /* 17943, "Suid: %1!" */
512               exec sp_getmessage 17943, @msg output
513               select @local_msgbuf = convert(varchar, @suid)
514               print @msg, @local_msgbuf
515   
516               /* 17944, "Loginame: %1!" */
517               exec sp_getmessage 17944, @msg output
518               print @msg, @name
519   
520               begin
521                   /* 17945, "Fullname: %1!" */
522                   exec sp_getmessage 17945, @msg output
523                   print @msg, @fullname
524               end
525               /* 
526               ** Default database login override not displayed, if a login 
527               ** profile is associated with the login account.
528               */
529               if (@lrname = NULL)
530               begin
531                   /* 18334, "Default Database: %1!" */
532                   exec sp_getmessage 18334, @msg output
533                   print @msg, @defdb
534               end
535   
536               /* 
537               ** Default language login override not displayed, if a login
538               ** profile is associated with the login account.
539               */
540               if (@lrname = NULL)
541               begin
542                   /* 18335, "Default Language: %1!" */
543                   exec sp_getmessage 18335, @msg output
544                   print @msg, @deflang
545               end
546   
547               /* 
548               ** Login script login override not displayed, if a login
549               ** profile is associated with the login account.
550               */
551               if (@lrname = NULL)
552               begin
553                   /* 18914, "Auto Login Script: %1!" */
554                   exec sp_getmessage 18914, @msg output
555                   print @msg, @procname
556               end
557   
558               /*
559               ** 17951, "Configured Authorization:"
560               */
561               exec sp_getmessage 17951, @msg output
562               print @msg
563               select @thisauth = min(srid)
564               from master.dbo.sysloginroles
565               where suid = @suid
566               while (@thisauth is not NULL)
567               begin
568                   select @authname = name
569                   from master.dbo.syssrvroles
570                   where srid = @thisauth
571                   if @authname is not NULL
572                   begin
573                       select @msg = "        " + @authname
574                       select @role_status = status from master.dbo.sysloginroles
575                       where suid = @suid and srid = @thisauth
576                       if (((@role_status & @enable_login_role) != 0) or
577                               /* its a system defined roles */
578                               (@thisauth >= 0 and @thisauth <= @max_sdr_srid))
579                           select @msg = @msg + " (default ON)"
580                       else
581                           select @msg = @msg + " (default OFF)"
582                       print @msg
583                   end
584                   select @thisauth = min(srid)
585                   from master.dbo.sysloginroles
586                   where suid = @suid and srid > @thisauth
587               end
588   
589               /*
590               ** 17949, "Locked: %1!"
591               */
592               exec sp_getmessage 17949, @msg output
593               print @msg, @locked
594   
595               if @locked = 'YES'
596               begin
597                   select @msg = "        " + "Date when locked: %1!"
598                   print @msg, @lockdate
599   
600                   select @msg = "        " + "Reason: %1!"
601                   if @lockreason = 0
602                   begin
603                       /*
604                       ** 19682, "Account locked by ASE by manually
605                       ** executing sp_locklogin"
606                       */
607                       exec sp_getmessage 19682, @lockreasonmsg output
608                   end
609                   else if @lockreason = 1
610                   begin
611                       /*
612                       ** 19683, "Account locked by ASE since
613                       ** account was inactive"
614                       */
615                       exec sp_getmessage 19683, @lockreasonmsg output
616                   end
617                   else if @lockreason = 2
618                   begin
619                       /*
620                       ** 19684, "Account locked by ASE due to failed
621                       ** login attempts reaching max failed logins."
622                       */
623                       exec sp_getmessage 19684, @lockreasonmsg output
624                   end
625                   else if @lockreason = 3
626                   begin
627                       /*
628                       ** 19685, "Account locked by ASE since login
629                       ** has not transitioned to SHA-256, after
630                       ** password downgrade."
631                       */
632                       exec sp_getmessage 19685, @lockreasonmsg output
633                   end
634                   else if @lockreason = 4
635                   begin
636                       /*
637                       ** 17032, "Account locked by ASE automatically
638                       ** since account was inactive"
639                       */
640                       exec sp_getmessage 17032, @lockreasonmsg output
641                   end
642                   print @msg, @lockreasonmsg
643   
644                   select @msg = "        " + "Locking suid: %1!"
645                   select @locksuidname = suser_name(@locksuid)
646                   print @msg, @locksuidname
647               end
648   
649               /*
650               ** 17950, "Date of Last Password Change: %1!"
651               */
652               exec sp_getmessage 17950, @msg output
653               select @local_msgbuf = convert(char(20), @pwdate)
654               print @msg, @local_msgbuf
655   
656               /*
657               ** 18675, "Password expiration interval: %1!"
658               */
659               exec sp_getmessage 18675, @msg output
660               select @local_msgbuf = convert(char(10), @passwdexp)
661               print @msg, @local_msgbuf
662   
663               /*
664               ** 18676, "Password Expired: %1!"
665               */
666               exec sp_getmessage 18676, @msg output
667               select @local_msgbuf = upper(@pwdexpired)
668               print @msg, @local_msgbuf
669   
670               /*
671               ** 18677, "Minimum Password Length: %1!"
672               */
673               exec sp_getmessage 18677, @msg output
674               select @local_msgbuf = convert(char(10), @minpwdlen)
675               print @msg, @local_msgbuf
676   
677               /*
678               ** 18678, "Maximum failed logins: %1!"
679               */
680               exec sp_getmessage 18678, @msg output
681               select @local_msgbuf = convert(char(10), @maxfailedlogins)
682               print @msg, @local_msgbuf
683   
684               /*
685               ** 18679, "Current failed login attempts: %1!"
686               */
687               exec sp_getmessage 18679, @msg output
688               select @local_msgbuf = convert(char(10), @currentfails)
689               print @msg, @local_msgbuf
690   
691               /* 
692               ** Authenticate with login override not displayed, if a
693               ** login profile is associated with the login account.
694               */
695               if (@lrname = NULL)
696               begin
697                   /*
698                   ** 19258, "Authenticate with: %1!"
699                   */
700                   exec sp_getmessage 19258, @msg output
701                   print @msg, @authmech
702               end
703   
704               /*
705               ** 19644, "Login Password Encryption: %1!"
706               */
707               exec sp_getmessage 19644, @msg output
708               print @msg, @encralgo
709   
710               /*
711               ** 19681, "Last login date: %1!"
712               */
713               exec sp_getmessage 19681, @msg output
714               print @msg, @lastlogindate
715   
716               /*
717               ** 19939, "Exempt inactive lock: %1!"
718               */
719               exec sp_getmessage 19939, @msg output
720               print @msg, @exemptinactivelock
721   
722               if (@lrname != NULL)
723               begin
724                   /*
725                   ** 18436, "Login Profile: %1!"
726                   */
727                   exec sp_getmessage 18436, @msg output
728                   print @msg, @lrname
729               end
730           end
731           else
732           begin
733               /*
734               ** Storing the configured roles with delimiter between the values
735               */
736               select @configroles = NULL
737               select @thisauth = min(srid)
738               from master.dbo.sysloginroles
739               where suid = @suid
740               while (@thisauth is not NULL)
741               begin
742                   select @authname = name
743                   from master.dbo.syssrvroles
744                   where srid = @thisauth
745                   if @authname is not NULL
746                   begin
747                       select @msg = @authname
748                       select @role_status = status from master.dbo.sysloginroles
749                       where suid = @suid and srid = @thisauth
750                       if (((@role_status & @enable_login_role) != 0) or
751                               /* its a system defined roles */
752                               (@thisauth >= 0 and @thisauth <= @max_sdr_srid))
753                           select @msg = @msg + "(default ON)"
754                       else
755                           select @msg = @msg + "(default OFF)"
756                       select @configroles = @configroles + @msg
757                   end
758                   select @thisauth = min(srid)
759                   from master.dbo.sysloginroles
760                   where suid = @suid and srid > @thisauth
761                   if @thisauth is not NULL
762                       select @configroles = @configroles + "; "
763               end
764               insert into #helpdisplay values
765               (
766                   @suid, @name, @fullname, @defdb, @deflang, @procname,
767                   @locked, @pwdate, @passwdexp, @pwdexpired, @minpwdlen,
768                   @maxfailedlogins, @currentfails, @authmech,
769                   @configroles, @encralgo, @lastlogindate,
770                   @exemptinactivelock, @lrname
771               )
772           end
773   
774           fetch display_login into
775               @suid, @password, @name, @fullname, @defdb, @deflang, @procid,
776               @status, @pwdate, @currentfails, @lastlogindate, @locksuid,
777               @lockreason, @lockdate, @lrid
778       end
779   
780       close display_login
781       deallocate cursor display_login
782   
783       if (@row_count <> 1)
784       begin
785           if exists (select 1 from #helpdisplay where (loginprofile = NULL))
786           begin
787               exec sp_autoformat @fulltabname = #helpdisplay,
788                   @selectlist = "'Suid' = suid, 'Loginname' = name, 
789   		'Fullname' = fullname, 'Default Database' = dbname,
790   		'Default Language' = language,
791   		'Auto Login Script' = procname, 'Locked' = locked,
792   		'Date of Last Password Change' = pwdate,
793   		'Password expiration interval' = passwdexp,
794   		'Password expired' = pwdexpired,
795   		'Minimum password length' = minpwdlen,
796   		'Maximum failed logins' = maxfailedlogins,
797   		'Current failed login attempts' = currentfails,
798   		'Authenticate with' = authmech,
799   		'Configured Authorization' = configroles,
800   		'Login Password Encryption' = encralgo,
801   		'Last login date' = lastlogindate,
802   		'Exempt inactive lock' = exemptinactivelock",
803                   @whereclause = "where loginprofile = NULL",
804                   @orderby = "order by name"
805           end
806           if exists (select 1 from #helpdisplay where (loginprofile is not NULL))
807           begin
808               exec sp_autoformat @fulltabname = #helpdisplay,
809                   @whereclause = "where loginprofile != NULL",
810                   @selectlist = "'Suid' = suid, 'Loginname' = name, 
811   		'Fullname' = fullname, 'Locked' = locked,
812   		'Date of Last Password Change' = pwdate,
813   		'Password expiration interval' = passwdexp,
814   		'Password expired' = pwdexpired,
815   		'Minimum password length' = minpwdlen,
816   		'Maximum failed logins' = maxfailedlogins,
817   		'Current failed login attempts' = currentfails,
818   		'Configured Authorization' = configroles,
819   		'Login Password Encryption' = encralgo,
820   		'Last login date' = lastlogindate,
821   		'Exempt inactive lock' = exemptinactivelock,
822   		'Login profile' = loginprofile",
823                   @orderby = "order by name"
824           end
825       end
826       return (0)
827   


exec sp_procxmode 'sp_displaylogin', 'AnyMode'
go

Grant Execute on sp_displaylogin to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch @authmech: varchar(15) = char(30) 444
 MTYP 4 Assignment type mismatch @encralgo: varchar(30) = varchar(1024) 468
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 787
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 808
 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_type}
352
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
420
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
436
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
442
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 356
 QTYP 4 Comparison type mismatch smallint = int 356
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 361
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 387
 QTYP 4 Comparison type mismatch smallint = int 387
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 392
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 401
 QTYP 4 Comparison type mismatch smallint = int 401
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 406
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 489
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 TNOU 4 Table with no unique index master..sysloginroles master..sysloginroles
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..sysloginroles  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_displaylogin  
 MNAC 3 Not using ANSI 'is null' 453
 MNAC 3 Not using ANSI 'is null' 485
 MNAC 3 Not using ANSI 'is null' 496
 MNAC 3 Not using ANSI 'is null' 529
 MNAC 3 Not using ANSI 'is null' 540
 MNAC 3 Not using ANSI 'is null' 551
 MNAC 3 Not using ANSI 'is null' 695
 MNAC 3 Not using ANSI 'is null' 722
 MNAC 3 Not using ANSI 'is null' 785
 MNER 3 No Error Check should check return value of exec 274
 MNER 3 No Error Check should check return value of exec 333
 MNER 3 No Error Check should check return value of exec 338
 MNER 3 No Error Check should check return value of exec 467
 MNER 3 No Error Check should check return value of exec 512
 MNER 3 No Error Check should check return value of exec 517
 MNER 3 No Error Check should check return value of exec 522
 MNER 3 No Error Check should check return value of exec 532
 MNER 3 No Error Check should check return value of exec 543
 MNER 3 No Error Check should check return value of exec 554
 MNER 3 No Error Check should check return value of exec 561
 MNER 3 No Error Check should check return value of exec 592
 MNER 3 No Error Check should check return value of exec 607
 MNER 3 No Error Check should check return value of exec 615
 MNER 3 No Error Check should check return value of exec 623
 MNER 3 No Error Check should check return value of exec 632
 MNER 3 No Error Check should check return value of exec 640
 MNER 3 No Error Check should check return value of exec 652
 MNER 3 No Error Check should check return value of exec 659
 MNER 3 No Error Check should check return value of exec 666
 MNER 3 No Error Check should check return value of exec 673
 MNER 3 No Error Check should check return value of exec 680
 MNER 3 No Error Check should check return value of exec 687
 MNER 3 No Error Check should check return value of exec 700
 MNER 3 No Error Check should check return value of exec 707
 MNER 3 No Error Check should check return value of exec 713
 MNER 3 No Error Check should check return value of exec 719
 MNER 3 No Error Check should check return value of exec 727
 MNER 3 No Error Check should check @@error after insert 764
 MNER 3 No Error Check should check return value of exec 787
 MNER 3 No Error Check should check return value of exec 808
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 212
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 224
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 276
 MUCO 3 Useless Code Useless Brackets 321
 MUCO 3 Useless Code Useless Brackets 373
 MUCO 3 Useless Code Useless Brackets 380
 MUCO 3 Useless Code Useless Brackets 422
 MUCO 3 Useless Code Useless Brackets 439
 MUCO 3 Useless Code Useless Brackets 485
 MUCO 3 Useless Code Useless Brackets 496
 MUCO 3 Useless Code Useless Brackets 529
 MUCO 3 Useless Code Useless Brackets 540
 MUCO 3 Useless Code Useless Brackets 551
 MUCO 3 Useless Code Useless Brackets 566
 MUCO 3 Useless Code Useless Brackets 576
 MUCO 3 Useless Code Useless Brackets 695
 MUCO 3 Useless Code Useless Brackets 722
 MUCO 3 Useless Code Useless Brackets 740
 MUCO 3 Useless Code Useless Brackets 750
 MUCO 3 Useless Code Useless Brackets 783
 MUCO 3 Useless Code Useless Brackets 785
 MUCO 3 Useless Code Useless Brackets 806
 MUCO 3 Useless Code Useless Brackets 826
 MUIN 3 Column created using implicit nullability 285
 QAFM 3 Var Assignment from potentially many rows 351
 QAFM 3 Var Assignment from potentially many rows 355
 QAFM 3 Var Assignment from potentially many rows 360
 QAFM 3 Var Assignment from potentially many rows 364
 QAFM 3 Var Assignment from potentially many rows 386
 QAFM 3 Var Assignment from potentially many rows 391
 QAFM 3 Var Assignment from potentially many rows 395
 QAFM 3 Var Assignment from potentially many rows 400
 QAFM 3 Var Assignment from potentially many rows 405
 QAFM 3 Var Assignment from potentially many rows 409
 QAFM 3 Var Assignment from potentially many rows 418
 QAFM 3 Var Assignment from potentially many rows 434
 QAFM 3 Var Assignment from potentially many rows 441
 QAFM 3 Var Assignment from potentially many rows 488
 QAFM 3 Var Assignment from potentially many rows 574
 QAFM 3 Var Assignment from potentially many rows 748
 QCTC 3 Conditional Table Creation 285
 QISO 3 Set isolation level 117
 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: {object, object_cinfo, attribute, class}
356
 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, attribute}
361
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
366
 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: {object, object_cinfo, attribute, class}
387
 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, attribute}
392
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
397
 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: {object, object_cinfo, attribute, class}
401
 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, attribute}
406
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
411
 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, object_type, attribute}
489
 QSWV 3 Sarg with variable @auth_status, Candidate Index: spt_values.spt_valuesclust clustered(number, type) U 436
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F 565
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 575
 QSWV 3 Sarg with variable @thisauth, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 575
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 586
 QSWV 3 Sarg with variable @thisauth, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 586
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F 739
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 749
 QSWV 3 Sarg with variable @thisauth, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 749
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 760
 QSWV 3 Sarg with variable @thisauth, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 760
 VNRD 3 Variable is not read @dummy 225
 VNRD 3 Variable is not read @loginame 266
 VUNU 3 Variable is not used @auths 48
 CRDO 2 Read Only Cursor Marker (has for read only clause) 250
 MSUB 2 Subquery Marker 785
 MSUB 2 Subquery Marker 806
 MTR1 2 Metrics: Comments Ratio Comments: 31% 37
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 90 = 94dec - 6exi + 2 37
 MTR3 2 Metrics: Query Complexity Complexity: 417 37

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