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


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) 396
 MTYP 4 Assignment type mismatch @encralgo: varchar(30) = varchar(1024) 420
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 739
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 760
 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}
304
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
372
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
388
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
394
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 308
 QTYP 4 Comparison type mismatch smallint = int 308
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 313
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 339
 QTYP 4 Comparison type mismatch smallint = int 339
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 344
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 353
 QTYP 4 Comparison type mismatch smallint = int 353
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 358
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 441
 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..syslogins  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_displaylogin  
 MNAC 3 Not using ANSI 'is null' 405
 MNAC 3 Not using ANSI 'is null' 437
 MNAC 3 Not using ANSI 'is null' 448
 MNAC 3 Not using ANSI 'is null' 481
 MNAC 3 Not using ANSI 'is null' 492
 MNAC 3 Not using ANSI 'is null' 503
 MNAC 3 Not using ANSI 'is null' 647
 MNAC 3 Not using ANSI 'is null' 674
 MNAC 3 Not using ANSI 'is null' 737
 MNER 3 No Error Check should check return value of exec 226
 MNER 3 No Error Check should check return value of exec 285
 MNER 3 No Error Check should check return value of exec 290
 MNER 3 No Error Check should check return value of exec 419
 MNER 3 No Error Check should check return value of exec 464
 MNER 3 No Error Check should check return value of exec 469
 MNER 3 No Error Check should check return value of exec 474
 MNER 3 No Error Check should check return value of exec 484
 MNER 3 No Error Check should check return value of exec 495
 MNER 3 No Error Check should check return value of exec 506
 MNER 3 No Error Check should check return value of exec 513
 MNER 3 No Error Check should check return value of exec 544
 MNER 3 No Error Check should check return value of exec 559
 MNER 3 No Error Check should check return value of exec 567
 MNER 3 No Error Check should check return value of exec 575
 MNER 3 No Error Check should check return value of exec 584
 MNER 3 No Error Check should check return value of exec 592
 MNER 3 No Error Check should check return value of exec 604
 MNER 3 No Error Check should check return value of exec 611
 MNER 3 No Error Check should check return value of exec 618
 MNER 3 No Error Check should check return value of exec 625
 MNER 3 No Error Check should check return value of exec 632
 MNER 3 No Error Check should check return value of exec 639
 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 665
 MNER 3 No Error Check should check return value of exec 671
 MNER 3 No Error Check should check return value of exec 679
 MNER 3 No Error Check should check @@error after insert 716
 MNER 3 No Error Check should check return value of exec 739
 MNER 3 No Error Check should check return value of exec 760
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 325
 MUCO 3 Useless Code Useless Brackets 332
 MUCO 3 Useless Code Useless Brackets 374
 MUCO 3 Useless Code Useless Brackets 391
 MUCO 3 Useless Code Useless Brackets 437
 MUCO 3 Useless Code Useless Brackets 448
 MUCO 3 Useless Code Useless Brackets 481
 MUCO 3 Useless Code Useless Brackets 492
 MUCO 3 Useless Code Useless Brackets 503
 MUCO 3 Useless Code Useless Brackets 518
 MUCO 3 Useless Code Useless Brackets 528
 MUCO 3 Useless Code Useless Brackets 647
 MUCO 3 Useless Code Useless Brackets 674
 MUCO 3 Useless Code Useless Brackets 692
 MUCO 3 Useless Code Useless Brackets 702
 MUCO 3 Useless Code Useless Brackets 735
 MUCO 3 Useless Code Useless Brackets 737
 MUCO 3 Useless Code Useless Brackets 758
 MUCO 3 Useless Code Useless Brackets 778
 MUIN 3 Column created using implicit nullability 237
 QAFM 3 Var Assignment from potentially many rows 303
 QAFM 3 Var Assignment from potentially many rows 307
 QAFM 3 Var Assignment from potentially many rows 312
 QAFM 3 Var Assignment from potentially many rows 316
 QAFM 3 Var Assignment from potentially many rows 338
 QAFM 3 Var Assignment from potentially many rows 343
 QAFM 3 Var Assignment from potentially many rows 347
 QAFM 3 Var Assignment from potentially many rows 352
 QAFM 3 Var Assignment from potentially many rows 357
 QAFM 3 Var Assignment from potentially many rows 361
 QAFM 3 Var Assignment from potentially many rows 370
 QAFM 3 Var Assignment from potentially many rows 386
 QAFM 3 Var Assignment from potentially many rows 393
 QAFM 3 Var Assignment from potentially many rows 440
 QAFM 3 Var Assignment from potentially many rows 526
 QAFM 3 Var Assignment from potentially many rows 700
 QCTC 3 Conditional Table Creation 237
 QISO 3 Set isolation level 109
 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}
308
 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}
313
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
318
 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}
339
 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}
344
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
349
 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}
353
 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}
358
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
363
 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}
441
 QSWV 3 Sarg with variable @auth_status, Candidate Index: spt_values.spt_valuesclust clustered(number, type) U 388
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F 517
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 527
 QSWV 3 Sarg with variable @thisauth, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 527
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 538
 QSWV 3 Sarg with variable @thisauth, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 538
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F 691
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 701
 QSWV 3 Sarg with variable @thisauth, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 701
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 712
 QSWV 3 Sarg with variable @thisauth, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 712
 VNRD 3 Variable is not read @dummy 178
 VNRD 3 Variable is not read @loginame 218
 VUNU 3 Variable is not used @auths 48
 CRDO 2 Read Only Cursor Marker (has for read only clause) 202
 MSUB 2 Subquery Marker 737
 MSUB 2 Subquery Marker 758
 MTR1 2 Metrics: Comments Ratio Comments: 32% 37
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 85 = 88dec - 5exi + 2 37
 MTR3 2 Metrics: Query Complexity Complexity: 392 37

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