DatabaseFunctionApplicationCreatedLinks
sybsystemprocsudf_inactivity_check  31 Aug 14Defects Dependencies

1     create function udf_inactivity_check(
2         @lpid int, /* associated login profile id to login for which
3         ** inactivity is to be determined
4         */
5         @lastlogindate datetime, /* lastlogindate value of login */
6         @pwdate datetime, /* pwdate value of login */
7         @current_date datetime, /* current datetime, this is required as parameter
8         ** because getdate() cannot be called in udf
9         */
10        @inactive_days int) /* value of inactive_days passed to sp_locklogin */
11        returns int
12    as
13        begin
14            declare @stale_period int, /* Value of stale_period attribute
15                ** defined in login profile
16                */
17                @def_lpid int, /* Login profile id of default login profile */
18                @ret int, /* Store return value */
19                @lp_class int, /* login profile class number */
20                @stale_period_attr int, /* attribute value of stale_period */
21                @def_lpid_attr int /* attribute value of default login profile */
22    
23            select @stale_period = null,
24                @def_lpid = null,
25                @ret = 0,
26                @lp_class = 39,
27                @stale_period_attr = 2,
28                @def_lpid_attr = 4
29    
30            /* If login profile is associated with the login */
31            if (@lpid is not null)
32            begin
33                /*
34                ** Get the stale_period attribute value defined for
35                ** the associated login profile
36                */
37                select @stale_period = int_value from master.dbo.sysattributes
38                where class = @lp_class
39                    and attribute = @stale_period_attr
40                    and object_cinfo = 'stale period'
41                    and object = @lpid
42                    and object_type = 'LR'
43            end
44    
45            if (@stale_period is null)
46            begin
47                /*
48                ** Either the login profile was not associated with the login
49                ** or stale_period attribute was not defined.
50                ** Now, default login profile is applicatble to this login.
51                */
52    
53                /* Get default default login profile */
54                select @def_lpid = object from master.dbo.sysattributes
55                where class = @lp_class
56                    and attribute = @def_lpid_attr
57                    and object_type = 'LR'
58                if (@def_lpid is not null)
59                begin
60                    select @stale_period = int_value from master.dbo.sysattributes
61                    where class = @lp_class
62                        and attribute = @stale_period_attr
63                        and object_cinfo = 'stale period'
64                        and object = @def_lpid
65                        and object_type = 'LR'
66                end
67            end
68    
69            if (@stale_period is not null)
70                select @inactive_days = @stale_period
71    
72            if (datediff(dd, isnull(@lastlogindate, @pwdate), @current_date) >= @inactive_days)
73                select @ret = 1
74    
75            return @ret
76        end
77
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 38
 QTYP 4 Comparison type mismatch smallint = int 38
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 39
 QTYP 4 Comparison type mismatch smallint = int 39
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 55
 QTYP 4 Comparison type mismatch smallint = int 55
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 56
 QTYP 4 Comparison type mismatch smallint = int 56
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 61
 QTYP 4 Comparison type mismatch smallint = int 61
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 62
 QTYP 4 Comparison type mismatch smallint = int 62
 MGTP 3 Grant to public master..sysattributes  
 MUCO 3 Useless Code Useless Begin-End Pair 13
 MUCO 3 Useless Code Useless Brackets 31
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 72
 QAFM 3 Var Assignment from potentially many rows 37
 QAFM 3 Var Assignment from potentially many rows 54
 QAFM 3 Var Assignment from potentially many rows 60
 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_type, object, object_cinfo, attribute, class}		 38
 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}		 55
 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_type, object, object_cinfo, attribute, class}		 61
 MTR1 2 Metrics: Comments Ratio Comments: 39% 1
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 15dec - 1exi + 2 1
 MTR3 2 Metrics: Query Complexity Complexity: 34 1

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysattributes (1)