DatabaseProcApplicationCreatedLinks
sybsystemprocssp_locklogin  14 déc. 14Defects Propagation Dependencies

1     
2     /* 
3     ** IMPORTANT: Please read the following instructions before
4     **   making changes to this stored procedure.
5     **
6     **	To make this stored procedure compatible with High Availability (HA),
7     **	changes to certain system tables must be propagated 
8     **	to the companion server under some conditions.
9     **	The tables include (but are not limited to):
10    **		syslogins, sysservers, sysattributes, systimeranges,
11    **		sysresourcelimits, sysalternates, sysdatabases,
12    **		syslanguages, sysremotelogins, sysloginroles,
13    **		sysalternates (master DB only), systypes (master DB only),
14    **		sysusers (master DB only), sysprotects (master DB only)
15    **	please refer to the HA documentation for detail.
16    **
17    **	Here is what you need to do: 
18    **	For each insert/update/delete statement, add three sections to
19    **	-- start HA transaction prior to the statement
20    **	-- add the statement
21    **	-- add HA synchronization code to propagate the change to the companion
22    **
23    **	For example, if you are adding 
24    **		insert master.dbo.syslogins ......
25    **	the code should look like:
26    **	1. Before that SQL statement:
27    **		
28    **		if (@nHARSTClass = 1)
29    **			begin tran ha_dynsyn
30    **		
31    **	2. Now, the SQL statement:
32    **		insert master.dbo.syslogins ......
33    **	3. Add a HA synchronization section right after the SQL statement:
34    **		
35    **		if (@@error != 0)
36    **			goto clean_all
37    **		if (@nHARSTClass = 1)
38    **		begin
39    **			insert master.dbo.rmt_ha_syslogins ......
40    **			if (@@error != 0)
41    **				goto clean_all
42    **			
43    **			commit tran ha_dynsyn
44    **		end
45    **		
46    **
47    **	You may need to do similar change for each built-in function you
48    **	want to add.
49    **
50    **	Finally, add a separate part at a place where it can not
51    **	be reached by the normal execution path:
52    **	clean_all:
53    **		
54    **			if (@nHARSTClass = 1)
55    **				rollback tran ha_dynsyn
56    **		
57    **		return (1)
58    */
59    
60    create or replace procedure sp_locklogin
61        @loginame varchar(30) = NULL, /* name of the login to be locked */
62        @locktype varchar(30) = NULL, /* "locked" or "unlock"            */
63        @except varchar(30) = NULL, /* login/role to be exempted	*/
64        @inactive_days int = NULL /* no of inactive days of a login */
65    as
66        begin
67            declare @suid int /* suid of login to be modified   */
68            declare @msg varchar(1024) /* message text */
69            declare @dummy int
70            declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
71            declare @retstat int
72            declare @current_id int /* suid of the current login */
73            declare @except_is_login tinyint /* flag to indicate if exception is login/role */
74            declare @except_is_role tinyint
75            declare @encompasses_all_logins tinyint
76            declare @warning_active tinyint
77            declare @row_count_temp int
78            declare @encompasses_all_rmt_ha_logins tinyint
79            declare @temp_current_id int
80            declare @current_date datetime
81            declare @scope varchar(32) /* SDC command execution scope */
82    
83            select @HA_CERTIFIED = 0
84            select @except_is_login = 0
85            select @except_is_role = 0
86            select @encompasses_all_logins = 1
87            select @encompasses_all_rmt_ha_logins = 1
88            select @warning_active = 0
89            select @row_count_temp = 0
90            select @current_date = getdate()
91    
92    
93    
94            declare @log_for_rep int
95            declare @db_rep_level_all int
96            declare @db_rep_level_none int
97            declare @db_rep_level_l1 int
98            declare @lt_rep_get_failed int
99            declare @nullarg char(1)
100           declare @status int
101           declare @gp_enabled int
102           declare @maxfailedlogins int /* value of maximum failed logins */
103           declare @currentfails int /* value of login attempt failures */
104           declare @passeclass int
105           declare @has_manage_any_login int
106           declare @has_change_password int
107           declare @single_user int
108           declare @toomanyfailedlogins int
109           declare @uwp varchar(16384)
110           declare @lockpriverr int
111   
112   
113   
114           /*
115           ** Initialize some constants
116           */
117           select @db_rep_level_all = - 1,
118               @db_rep_level_none = 0,
119               @db_rep_level_l1 = 1,
120               @lt_rep_get_failed = - 2,
121               @has_manage_any_login = 0,
122               @has_change_password = 0,
123               @single_user = 0,
124               @lockpriverr = 0,
125               @toomanyfailedlogins = 0
126   
127   
128   
129   
130           /* check to see if we are using HA specific SP for a HA enabled server */
131           exec @retstat = sp_ha_check_certified 'sp_locklogin', @HA_CERTIFIED
132           if (@retstat != 0)
133               return (1)
134   
135           /* create a dummy table to store temporary rows */
136           create table #dummy_table(suid int)
137   
138           /*
139           ** Do not allow this system procedure to be run from within a transaction
140           ** to avoid creating a multi-database transaction where the 'master'
141           ** database is not the co-ordinating database.
142           */
143           if @@trancount > 0
144           begin
145               /* 17260, "Can't run %1! from within a transaction." */
146               exec sp_getmessage 17260, @msg output
147               print @msg, "sp_locklogin"
148               return (1)
149           end
150           else
151           begin
152               set chained off
153           end
154   
155           set transaction isolation level 1
156   
157           /*
158           ** Get the replication status of the 'master' database
159           */
160           select @log_for_rep = getdbrepstat(1)
161           if (@log_for_rep = @lt_rep_get_failed)
162           begin
163               raiserror 18409, "getdbrepstat"
164               return (1)
165           end
166   
167           /*
168           ** Convert the replication status to a boolean
169           */
170           if (@log_for_rep != @db_rep_level_none)
171               select @log_for_rep = 1
172           else
173               select @log_for_rep = 0
174   
175           /*
176           ** If we are logging this system procedure for replication, we must be in
177           ** the 'master' database to avoid creating a multi-database transaction
178           ** which could make recovery of the 'master' database impossible.
179           */
180           if (@log_for_rep = 1) and (db_name() != "master")
181           begin
182               raiserror 18388, "sp_locklogin"
183               return (1)
184           end
185   
186           if (@loginame is not NULL and @locktype = "unlock")
187           begin
188               select @single_user = count(name) from master.dbo.syslogins
189               where name like @loginame and ((status & 2) = 2)
190           end
191   
192           /*
193           ** We are only interested in checking maxfailedlogins if @login_name is a
194           ** single user. We will only get here if locktype is "unlock".
195           */
196           if (@loginame is not NULL and @single_user = 1)
197           begin
198               select @suid = suid from master.dbo.syslogins
199               where name like @loginame
200   
201               /* get the value of PASSWD_SECURITY class */
202               select @passeclass = class from master.dbo.sysattributes where
203                   object_type = "PS"
204   
205               /* Maximum failed logins */
206               select @maxfailedlogins = int_value
207               from master.dbo.sysattributes
208               where class = @passeclass AND attribute = 2
209                   AND object = @suid
210                   AND object_cinfo = "login"
211   
212               if @maxfailedlogins is NULL
213                   select @maxfailedlogins = int_value
214                   from master.dbo.sysattributes
215                   where class = 27 AND attribute = 9
216   
217               if @maxfailedlogins is NULL
218                   select @maxfailedlogins = value
219                   from master.dbo.sysconfigures
220                   where name = "maximum failed logins"
221   
222               /* Count of current failed logins */
223               select @currentfails = logincount from master.dbo.syslogins
224               where suid = @suid
225               if @currentfails >= @maxfailedlogins
226                   select @toomanyfailedlogins = 1
227           end
228   
229           /* 
230           ** If granular permissions is not enabled then sso_role is required.
231           ** If granular permissions is enabled then the permission 'manage any login' is
232           ** required.  proc_role and proc_auditperm will also do auditing
233           ** if required. Both will also print error message if required.
234           */
235   
236           select @nullarg = NULL
237           execute @status = sp_aux_checkroleperm "sso_role", "manage any login",
238               @nullarg, @gp_enabled output
239   
240           /* For Auditing */
241           if (@gp_enabled = 0)
242           begin
243               if (proc_role("sso_role") = 0)
244                   return (1)
245           end
246           else
247           begin
248               if (@status = 0)
249               begin
250                   select @has_manage_any_login = 1
251               end
252               /*
253               ** If the locktype is "unlock" then we will check to see if 
254               ** @loginame exceeded maxfailed logins.  If so, a check will
255               ** be made to determine if the current user has "change password"
256               ** permission.  Therefore auditing and printing an error message
257               ** will not be done here if locktype = "unlock".
258               */
259               if (@status != 0 and @locktype != "unlock")
260               begin
261                   select @dummy = proc_auditperm("manage any login", @status)
262                   return (1)
263               end
264           end
265   
266           if @gp_enabled = 1
267           begin
268               if @status != 0 and @locktype = "unlock" and @toomanyfailedlogins != 0
269               begin
270                   execute @status = sp_aux_checkroleperm @nullarg,
271                       "change password", @nullarg, @gp_enabled output
272                   if @status = 0
273                   begin
274                       select @has_change_password = 1
275                   end
276               end
277               if @has_manage_any_login != 0
278               begin
279                   select @dummy = proc_auditperm("manage any login", @status)
280               end
281               if @has_change_password != 0
282               begin
283                   select @dummy = proc_auditperm("change password", @status)
284               end
285               /* Audit failures and display error messages. */
286               if @has_manage_any_login = 0 and @has_change_password = 0
287               begin
288                   select @dummy = proc_auditperm("manage any login", @status)
289                   if @toomanyfailedlogins != 0
290                       select @dummy = proc_auditperm("change password",
291                               @status)
292                   return 1
293               end
294           end
295   
296           if @loginame is NULL and @locktype is NULL
297           begin
298               /*
299               ** 17913, "Locked account(s):"
300               */
301               exec sp_getmessage 17913, @msg output
302               print @msg
303               select name
304               from master.dbo.syslogins
305               where ((status & 2) = 2) /* LOGIN_LOCKED */
306                   and ((status & 512) != 512) /* not LOGIN PROFILE */
307               return (0)
308           end
309   
310           if (@loginame is not NULL)
311           begin
312               /*  Check if any such account exists */
313   
314               if not exists (select suid
315                       from master.dbo.syslogins
316                       where name like @loginame
317                           and ((status & 512) != 512)) /* not LOGIN PROFILE */
318               begin
319                   /*
320                   ** 17880, "No such account -- nothing changed."
321                   */
322                   exec sp_getmessage 17880, @msg output
323                   print @msg
324                   return (1)
325               end
326   
327           end
328   
329   
330           if ((@locktype != "unlock") and (@locktype != "lock"))
331           begin
332               /*
333               ** 17917, "Error: locktype must either be 'lock' or 'unlock'."
334               */
335               exec sp_getmessage 17917, @msg output
336               print @msg
337               /*
338               ** 17918, "Nothing changed."
339               */
340               exec sp_getmessage 17918, @msg output
341               print @msg
342               return (1)
343           end
344   
345           /* 
346           ** Check if the exception specified is a login or a role 
347           ** and set the variable @except_is_login accordingly
348           */
349   
350           if (@except is not NULL)
351           begin
352               if exists (select 1 from master.dbo.syslogins
353                       where name = @except and
354                           ((status & 512) != 512)) /* not LOGIN PROFILE */
355               begin
356                   /* @except is a login  */
357   
358                   select @except_is_login = 1
359               end
360   
361               /* Check if @except is a valid role */
362               if exists (select 1 from master.dbo.sysloginroles as srole,
363                           master.dbo.syssrvroles as rolename
364                       where srole.srid = rolename.srid
365                           and rolename.name = @except)
366               begin
367                   select @except_is_role = 1
368               end
369   
370               if (@except_is_login = 0) and (@except_is_role = 0)
371               begin
372                   /* 19395, The login or role to be excluded from locking/unlocking is invalid */
373                   exec sp_getmessage 19395, @msg output
374                   print @msg
375                   return (1)
376               end
377           end
378   
379           /*
380           ** Check if the user specified "effectively NULL" set of logins
381           ** to be locked/unlocked. If so, return doing nothing
382           ** For instance the user might have specified same set of logins
383           ** to be locked as the set to be exempted.
384           */
385   
386           if (@loginame is not NULL) and
387               (((@except_is_login = 1) and
388                       (not exists (select suid from master.dbo.syslogins
389                               where name like @loginame
390                                   and name != @except))) or
391                   ((@except_is_role = 1) and
392                       (not exists (select suid from master.dbo.syslogins
393                               where name like @loginame and
394                                   suid not in
395                                       (select suid from master.dbo.sysloginroles
396                                       where srid = role_id(@except))))))
397           begin
398   
399               /* 19394, "The set of logins specified is empty." */
400               exec sp_getmessage 19394, @msg output
401               print @msg
402   
403               return (1)
404           end
405   
406   
407   
408   
409   
410           if @locktype = "unlock"
411           begin -- {
412   
413               /* 'Sybmail' account cannot be unlocked */
414               if (@loginame = "sybmail")
415               begin
416                   /*
417                   ** 18419, "Account 'sybmail' cannot be unlocked."
418                   */
419                   exec sp_getmessage 18419, @msg output
420                   print @msg
421                   return (1)
422               end
423   
424               /* '@inactive_days' and 'unlock' cannot be specified at the same time */
425               if (@inactive_days != NULL)
426               begin
427                   /*
428                   ** 19607, "You cannot specify both
429                   ** 'inactive_days' and 'unlock' at the same time."
430                   */
431                   exec sp_getmessage 19607, @msg output
432                   print @msg
433                   return (1)
434               end
435   
436               /* Unlock any existing account */
437   
438               if ((@gp_enabled = 1) or (@log_for_rep = 1) or (@HA_CERTIFIED = 1))
439                   begin tran rs_logexec
440   
441   
442   
443               if (@loginame is NULL)
444               begin -- {
445                   if (@except_is_login = 1)
446                   begin
447                       /*
448                       ** Unlock logins except ones which match @except
449                       **
450                       ** The columns lockdate, lockreason, locksuid are
451                       ** reset with values current datetime, NULL, NULL whenever
452                       ** a login account is unlocked.
453                       **
454                       ** The columns lockdate, lockreason and locksuid
455                       ** are set to context specific values when an account
456                       ** is locked. The values reflect the datetime,
457                       ** reason code, and the suid of the login
458                       ** that is locking the account.
459                       **
460                       ** Changes to these columns should be recorded
461                       ** in the audit trail, and will do so in a future 
462                       ** ASE release when locking is done outside of 
463                       ** stored procedure.
464                       */
465                       update master.dbo.syslogins
466                       set status = status & (~ 2),
467                           logincount = 0,
468                           lockdate = @current_date,
469                           lockreason = NULL,
470                           locksuid = NULL
471                       where name != @except
472                           and ((status & 512) != 512) /*
473                   ** don't update 
474                   ** LOGIN PROFILE, if any
475                   */
476                   end
477                   else
478                   if (@except_is_role = 1)
479                   begin
480                       /* 
481                       ** @except is a role 
482                       ** Unlock all logins except ones whose role matches
483                       ** @except
484                       */
485   
486                       update master.dbo.syslogins
487                       set status = status & (~ 2),
488                           logincount = 0,
489                           lockdate = @current_date,
490                           lockreason = NULL,
491                           locksuid = NULL
492                       where suid not in
493                               (select suid from master.dbo.sysloginroles
494                               where srid = role_id(@except))
495                           and ((status & 512) != 512) /*
496                   ** don't update 
497                   ** LOGIN PROFILE, if any
498                   */
499                   end
500                   else
501                   begin
502                       /* @except is NULL */
503                       update master.dbo.syslogins
504                       set status = status & (~ 2),
505                           logincount = 0,
506                           lockdate = @current_date,
507                           lockreason = NULL,
508                           locksuid = NULL
509                       where ((status & 512) != 512) /*
510                   ** don't update 
511                   ** LOGIN PROFILE, if any
512                   */
513                   end
514               end -- }
515               else
516               begin -- {
517                   /* 
518                   ** @loginame is not NULL 
519                   ** Same cases as above but considering @loginame as well
520                   ** Unlock logins which match the specified @loginame 
521                   ** excluding the ones mentioned in @except
522                   */
523   
524                   if (@except_is_login = 1)
525                   begin
526                       update master.dbo.syslogins
527                       set status = status & (~ 2), /* LOGIN_UNLOCKED */
528                           logincount = 0,
529                           lockdate = @current_date,
530                           lockreason = NULL,
531                           locksuid = NULL
532                       where name like @loginame
533                           and name != @except
534                           and ((status & 512) != 512) /*
535                   ** don't update 
536                   ** LOGIN PROFILE, if any
537                   */
538                   end
539                   else
540                   if (@except_is_role = 1)
541                   begin
542                       update master.dbo.syslogins
543                       set status = status & (~ 2),
544                           logincount = 0,
545                           lockdate = @current_date,
546                           lockreason = NULL,
547                           locksuid = NULL
548                       where name like @loginame and
549                           suid not in
550                               (select suid from master.dbo.sysloginroles
551                               where srid = role_id(@except))
552                           and ((status & 512) != 512) /*
553                   ** don't update 
554                   ** LOGIN PROFILE, if any
555                   */
556                   end
557                   else
558                   begin
559                       update master.dbo.syslogins
560                       set status = status & (~ 2),
561                           logincount = 0,
562                           lockdate = @current_date,
563                           lockreason = NULL,
564                           locksuid = NULL
565                       where name like @loginame
566                           and ((status & 512) != 512) /*
567                   ** don't update 
568                   ** LOGIN PROFILE, if any
569                   */
570                   end
571               end -- }
572   
573   
574   
575               if (@log_for_rep = 1)
576               begin
577                   /*
578                   ** If the 'master' database is marked for replication, the
579                   ** T-SQL built-in 'logexec()' will log for replication the
580                   ** execution instance of this system procedure.  Otherwise,
581                   ** the T-SQL built-in 'logexec()' is a no-op.
582                   */
583                   if (logexec(1) != 1)
584                   begin
585                       raiserror 17756, "sp_locklogin", "master"
586                       goto clean_all
587                   end
588               end
589   
590               if ((@gp_enabled = 1) or (@log_for_rep = 1) or (@HA_CERTIFIED = 1))
591                   commit tran rs_logexec
592   
593               /*
594               ** 17914, "Account unlocked."
595               */
596               exec sp_getmessage 17914, @msg output
597               print @msg
598               return (0)
599           end -- }
600   
601           /*
602           **  When it comes down to here, it must be the complicated "lock" case.
603           */
604   
605           select @current_id = suser_id()
606           /*
607           ** Check if the set of logins to be locked (after taking exceptions into 
608           ** consideration) encompasses all the sso/sa logins in the server.
609           ** If it does, we will be locking all the sa/sso logins; so in this case 
610           ** leave out the current login and lock the rest of the logins.
611           ** If it doesn't, go ahead and lock the set of logins specified.
612           ** By encompassing all sso/sa logins, we mean there must be atleast
613           ** one login with sso privilege and at least one login with sa privilege
614           ** outside our locking set. First we check for sso logins (srid =1) and
615           ** then for sa logins (srid = 0) and set the flag @encompasses_all_logins 
616           ** to false even if one of them is satisfied.
617           */
618   
619           if (@gp_enabled = 0 and @loginame is not NULL)
620           begin -- {
621               if (@except_is_login = 1)
622               begin
623                   /* 
624                   ** SSO logins srid = 1 and SA logins srid = 0
625                   ** If there exists an sso and sa login outside the
626                   ** set of logins to be locked then it means
627                   ** the set doesn't encompass all of sso and sa logins
628                   ** Similar logic applied in the subsequent code
629                   ** with changes according as login or role specified
630                   */
631   
632                   if exists (select 1 from master.dbo.syslogins as ms,
633                               master.dbo.sysloginroles as msr
634                           where (ms.status) & 2 = 0 and
635                               ms.suid = msr.suid and
636                               msr.srid = 1 and
637                               (ms.name not like @loginame or ms.name = @except))
638                       and
639                       exists (select 1 from master.dbo.syslogins as ms,
640                               master.dbo.sysloginroles as msr
641                           where (ms.status) & 2 = 0 and
642                               ms.suid = msr.suid and
643                               msr.srid = 0 and
644                               (ms.name not like @loginame or ms.name = @except))
645                   begin
646                       select @encompasses_all_logins = 0
647                   end
648   
649               end
650               else if (@except_is_role = 1)
651               begin
652                   /*
653                   ** Taking into consideration the exception specified is a role,
654                   ** find out if the set of logins to be locked doesn't encompass
655                   ** all SA and all SSO logins
656                   */
657                   if exists (select 1 from master.dbo.syslogins as ms,
658                               master.dbo.sysloginroles as msr
659                           where (ms.status) & 2 = 0 and
660                               ms.suid = msr.suid and
661                               msr.srid = 1 and
662                               ((ms.name not like @loginame) or
663                                   ms.suid in
664                                       (select suid from master.dbo.sysloginroles
665                                       where srid = role_id(@except))))
666                       and
667                       exists (select 1 from master.dbo.syslogins as ms,
668                               master.dbo.sysloginroles as msr
669                           where (ms.status) & 2 = 0 and
670                               ms.suid = msr.suid and
671                               msr.srid = 0 and
672                               ((ms.name not like @loginame) or
673                                   ms.suid in
674                                       (select suid from master.dbo.sysloginroles
675                                       where srid = role_id(@except))))
676   
677                   begin
678                       select @encompasses_all_logins = 0
679                   end
680   
681               end
682               else
683               begin
684                   /* 
685                   ** @except is NULL
686                   ** See if the set of logins to be locked encompasses
687                   ** all SA logins or all SSO logins
688                   ** If it doesn't encompass all SA logins and all SSO
689                   ** logins set @encompasses_all_logins to 0.
690                   */
691                   if exists (select 1 from master.dbo.syslogins as ms,
692                               master.dbo.sysloginroles as msr
693                           where (ms.status) & 2 = 0 and
694                               ms.suid = msr.suid and
695                               msr.srid = 1 and
696                               ms.name not like @loginame)
697                       and
698                       exists (select 1 from master.dbo.syslogins as ms,
699                               master.dbo.sysloginroles as msr
700                           where (ms.status) & 2 = 0 and
701                               ms.suid = msr.suid and
702                               msr.srid = 0 and
703                               ms.name not like @loginame)
704                   begin
705                       select @encompasses_all_logins = 0
706                   end
707   
708               end
709           end -- }
710           else if (@gp_enabled = 0)
711           begin -- {
712               /* @loginame = NULL */
713   
714               if (@except_is_login = 1)
715               begin
716                   /*
717                   ** Set @encompasses_all_logins to 0 if there exists
718                   ** one SA and one SSO login outside the set of logins 
719                   ** to be locked.
720                   */
721                   if exists (select 1 from master.dbo.syslogins as ms,
722                               master.dbo.sysloginroles as msr
723                           where (ms.status) & 2 = 0 and
724                               ms.suid = msr.suid and
725                               msr.srid = 1 and
726                               ms.name = @except)
727                       and
728                       exists (select 1 from master.dbo.syslogins as ms,
729                               master.dbo.sysloginroles as msr
730                           where (ms.status) & 2 = 0 and
731                               ms.suid = msr.suid and
732                               msr.srid = 0 and
733                               ms.name = @except)
734                   begin
735                       select @encompasses_all_logins = 0
736                   end
737   
738               end
739               else if (@except_is_role = 1)
740               begin
741                   /* 
742                   ** The exception specified is a role. Set @encompasses_all_logins
743                   ** as above considering this @except as a role
744                   */
745                   if exists (select 1 from master.dbo.syslogins as ms,
746                               master.dbo.sysloginroles as msr
747                           where (ms.status) & 2 = 0 and
748                               ms.suid = msr.suid and
749                               msr.srid = 1 and
750                               ms.suid in
751                                   (select suid from master.dbo.sysloginroles
752                                   where srid = role_id(@except)))
753                       and
754                       exists (select 1 from master.dbo.syslogins as ms,
755                               master.dbo.sysloginroles as msr
756                           where (ms.status) & 2 = 0 and
757                               ms.suid = msr.suid and
758                               msr.srid = 0 and
759                               ms.suid in
760                                   (select suid from master.dbo.sysloginroles
761                                   where srid = role_id(@except)))
762                   begin
763                       select @encompasses_all_logins = 0
764                   end
765   
766               end
767           end -- }
768   
769           /*
770           ** check if @inactive_days is in valid range 0-32767 and
771           ** 'enable last login' is 1 in sysattributes.
772           */
773           if (@inactive_days != NULL)
774           begin
775               if ((@inactive_days < 0) or (@inactive_days > 32767))
776               begin
777                   /*
778                   ** 19444,  "The range for %1! is %2! to %3!."
779                   */
780                   raiserror 19444, 'inactive days', 0, 32767
781                   return (1)
782               end
783               /*
784               ** no of inactive days = today's date - last login date.
785               ** For calculation of inactive days, last login updates must be enabled,
786               ** so attribute row for 'enable last login updates' 
787               ** in sysattribute should not be set to 0.
788               ** Either the row should not exists OR it should be set to 1.
789               */
790               if ((select int_value from master.dbo.sysattributes
791                           where class = 32 and attribute = 0) = 0)
792               begin
793                   /*
794                   ** 19606, To lock inactive accounts, you must run
795                   ** sp_passwordpolicy 'set','enable last login updates',1
796                   */
797                   raiserror 19606
798                   return (1)
799               end
800           end
801           /*
802           **  If any of the accounts to be locked are active, go ahead and try 
803           **  to lock them, but issue a warning message.
804           **  If @encompasses_all_logins = 1 then the @current_id is not locked
805           **  but a warning message could be displayed erroneously as the account 
806           **  is active. So set @temp_current_id and use it to exclude the 
807           **  current account. If @encompasses_all_logins = 0 @current_id shouldn't
808           **  come into picture - hence invalidate it.
809           */
810   
811           if (@encompasses_all_logins = 1)
812               select @temp_current_id = @current_id
813           else
814               select @temp_current_id = - 2 /* @temp_current_id is invalidated */
815   
816           /*
817           ** SDC only, check clusterwide sysprocesses for active login
818           */
819           if (@@clustermode = "shared disk cluster")
820           begin
821               select @scope = @@system_view
822               set system_view cluster
823           end
824   
825           if (@loginame is not NULL)
826           begin
827               if (@except_is_login = 1)
828               begin
829                   /* 
830                   ** set @warning_active to 1 if the set of logins to
831                   ** be locked has any active login
832                   */
833                   if exists (select 1 from master.dbo.sysprocesses as mp,
834                               master.dbo.syslogins as ms
835                           where mp.suid = ms.suid and
836                               ms.name like @loginame and
837                               ms.name != @except and
838                               ms.suid != @temp_current_id)
839                   begin
840                       select @warning_active = 1
841                   end
842   
843               end
844               else if (@except_is_role = 1)
845               begin
846                   /*
847                   ** Similar to the above condition except that
848                   ** the exception specified is a role
849                   */
850                   if exists (select 1 from master.dbo.sysprocesses as mp,
851                               master.dbo.syslogins as ms
852                           where mp.suid = ms.suid and
853                               ms.name like @loginame and
854                               ms.suid != @temp_current_id and
855                               ms.suid not in
856                                   (select suid from master.dbo.sysloginroles
857                                   where srid = role_id(@except)))
858                   begin
859                       select @warning_active = 1
860                   end
861   
862               end
863               else
864               /* @except is NULL */
865               begin
866                   /*
867                   ** Find out if any login to be locked is active
868                   ** There is no exception specified.
869                   */
870                   if exists (select 1 from master.dbo.sysprocesses as mp,
871                               master.dbo.syslogins as ms
872                           where ms.suid = mp.suid and
873                               ms.name like @loginame and
874                               ms.suid != @temp_current_id)
875                   begin
876                       select @warning_active = 1
877                   end
878               end
879           end
880           else
881           begin
882               /* @loginame = NULL */
883               if (@except_is_login = 1)
884               begin
885                   /* 
886                   ** Find out if the set of logins to be locked has
887                   ** any active login
888                   */
889                   if exists (select 1 from master.dbo.sysprocesses as mp,
890                               master.dbo.syslogins as ms
891                           where mp.suid = ms.suid and
892                               ms.name != @except and
893                               ms.suid != @temp_current_id)
894                   begin
895                       select @warning_active = 1
896                   end
897   
898               end
899               else if (@except_is_role = 1)
900               begin
901                   /* 
902                   ** Similar to the above case 
903                   ** Here the @except specified is a role
904                   */
905                   if exists (select 1 from master.dbo.sysprocesses as mp,
906                               master.dbo.syslogins as ms
907                           where mp.suid = ms.suid and
908                               ms.suid != @temp_current_id and
909                               ms.suid not in
910                                   (select suid from master.dbo.sysloginroles
911                                   where srid = role_id(@except)))
912                   begin
913                       select @warning_active = 1
914                   end
915   
916               end
917               else
918               /* @except is NULL */
919               begin
920                   if exists (select 1 from master.dbo.sysprocesses as mp,
921                               master.dbo.syslogins as ms
922                           where ms.suid = mp.suid and
923                               ms.suid != @temp_current_id)
924                   begin
925                       select @warning_active = 1
926                   end
927   
928               end
929           end
930   
931           /*
932           ** SDC only: restore previous system_view scope
933           */
934           if (@@clustermode = "shared disk cluster")
935           begin
936               set system_view @scope
937           end
938   
939           if (@warning_active = 1)
940           begin
941               /*
942               ** 19391, "Warning: One or more accounts specified are active."
943               */
944   
945               exec sp_getmessage 19391, @msg output
946               print @msg
947   
948           /* FALL THROUGH */
949           end
950   
951   
952   
953   
954           if (@encompasses_all_logins = 1)
955           begin
956               /*
957               ** Check if the current account is already locked. If yes,
958               ** flag an error as locking all accounts excluding the 
959               ** current is not possible.
960               ** In other words, an account which is already locked at
961               ** this point of time cannot be allowed to issue any lock 
962               ** command.
963               ** However, under certain race conditions where more than
964               ** one such accounts issue lock commands simultaneously,
965               ** this check doesn't guarantee consistency. If the current
966               ** account is locked AFTER this check passes through by some
967               ** other login, we have a situation where a locked account
968               ** will be allowed to lock other accounts. This drawback
969               ** cannot be overcome without removing the check and without
970               ** doing the check as well as locking operation in a transaction.
971               ** Considering it is rare that such a situation occurs, this
972               ** approach is adopted.
973               */
974   
975               if exists (select 1 from master.dbo.syslogins
976                       where (status & 2) = 2 and
977                           suid = @current_id)
978               begin
979                   /* 19392, Permission Denied. The current account is already locked. */
980                   exec sp_getmessage 19392, @msg output
981                   print @msg
982   
983                   return (1)
984               end
985   
986           end
987   
988           /*  Feel free to lock the account */
989   
990           if ((@gp_enabled = 1) or (@log_for_rep = 1) or (@HA_CERTIFIED = 1))
991               begin tran rs_logexec
992   
993   
994   
995           /*
996           ** There is a race condition possible. Even though we unlock the current account
997           ** before quitting (see the later part of the code), if we lock the current account
998           ** here AND the set of accts encompasses all SA/SSO logins, we have a situation 
999           ** where for a brief period, all SA/SSO logins may be locked. So, if we find that
1000          ** all SA/SSO logins have been encompassed we exclude the current account(which has
1001          ** SA/SSO role) from locking in which case we set the variable @current_id to
1002          ** the suid of the current account. however if all SA/SSO logins have not been
1003          ** encompassed we need not exclude the current account from the set of logins 
1004          ** being locked, so invalidate the variable @current_id
1005          */
1006  
1007          if (@encompasses_all_logins = 0)
1008              select @current_id = - 2 /* Invalidate @current_id value */
1009  
1010          if (@loginame is not NULL)
1011          begin
1012              if (@except_is_login = 1)
1013              begin
1014                  /*
1015                  ** Lock all the accounts matching @loginame except
1016                  ** "@except" login and current account (current acct
1017                  ** may be valid or invalid)
1018                  */
1019                  if (@inactive_days != NULL)
1020                  begin
1021                      /*
1022                      ** As 'last login update' feature(securing passwords) 
1023                      ** will not be HA-ized in 15.0.2 release, we need 
1024                      ** to handle it specially on primary and companion server.
1025                      ** To keep locked accounts in sync, we compare 'lastlogindate' 
1026                      ** column on both servers, and use the most recent date 
1027                      ** to calculate inactive days.
1028                      **
1029                      ** Check if companion server is configured. and 
1030                      ** then accordingly decide the set of accounts to lock. 
1031                      */
1032                      if (@HA_CERTIFIED = 0)
1033                      begin
1034                          /*
1035                          ** Companion server is not configured, so you 
1036                          ** can use master.dbo.syslogins for lastlogindate.
1037                          ** No need to look at master.dbo.rmt_ha_syslogins
1038                          */
1039                          update master.dbo.syslogins
1040                          set status = status | 2, /* LOGIN_LOCKED */
1041                              lockreason = 1, /* locked because inactive */
1042                              lockdate = @current_date,
1043                              locksuid = suser_id()
1044                          where name like @loginame
1045                              /*
1046                              ** login is unlocked and is not exempted 
1047                              ** from locking due to inactivity.
1048                              */
1049                              and (status & 10) = 0
1050                              and suid != @current_id
1051                              and name != @except
1052                              and (sybsystemprocs.dbo.udf_inactivity_check(
1053                                      lpid, lastlogindate, pwdate,
1054                                      @current_date, @inactive_days)
1055                                  = 1)
1056                              and ((status & 512) != 512) /*
1057                      ** don't update 
1058                      ** LOGIN PROFILE, if any
1059                      */
1060                      end
1061                      else
1062                      begin
1063                          /*
1064                          ** A 'begin - end' block , that includes all statement in '##if - ##endif' only,
1065                          ** gives syntax error. The 'begin - end' block is considered as empty block.
1066                          ** following 'select' statement is included to avoid that error.
1067                          */
1068                          select @dummy = 1
1069  
1070                      /*
1071                      ** Companion server is configured.
1072                      ** If a login,
1073                      ** is inactive on primary server,
1074                      ** we check if it is inactive on companion server also.
1075                      ** If is inactive on both servers, then only it is locked.
1076                      */
1077  
1078                      end
1079                  end
1080                  else /* @inactive_days = NULL  */
1081                  begin
1082                      update master.dbo.syslogins
1083                      set status = status | 2, /* LOGIN_LOCKED */
1084                          lockreason = 0, /* locked manually using sp_locklogin */
1085                          lockdate = @current_date,
1086                          locksuid = suser_id()
1087                      where name like @loginame
1088                          and (status & 2) = 0
1089                          and suid != @current_id
1090                          and name != @except
1091                          and ((status & 512) != 512) /*
1092                  ** don't update 
1093                  ** LOGIN PROFILE, if any
1094                  */
1095                  end
1096              end
1097              else if (@except_is_role = 1)
1098              begin
1099                  /*
1100                  ** Lock all the accounts matching @loginame except
1101                  ** those which have the role @except and excluding
1102                  ** current account (which may be valid or invalid)
1103                  */
1104                  if (@inactive_days != NULL)
1105                  begin
1106                      if (@HA_CERTIFIED = 0)
1107                      begin
1108                          update master.dbo.syslogins
1109                          set status = status | 2, /* LOGIN_LOCKED */
1110                              lockreason = 1, /* locked because inactive */
1111                              lockdate = @current_date,
1112                              locksuid = suser_id()
1113                          where name like @loginame
1114                              /*
1115                              ** login is unlocked and is not exempted 
1116                              ** from locking due to inactivity.
1117                              */
1118                              and (status & 10) = 0
1119                              and suid != @current_id
1120                              and suid not in
1121                                  (select suid from master.dbo.sysloginroles
1122                                  where srid = role_id(@except))
1123                              and (sybsystemprocs.dbo.udf_inactivity_check(
1124                                      lpid, lastlogindate, pwdate,
1125                                      @current_date, @inactive_days)
1126                                  = 1)
1127                              and ((status & 512) != 512) /*
1128                      ** don't update 
1129                      ** LOGIN PROFILE, if any
1130                      */
1131                      end
1132                      else /* HA server is configured */
1133                      begin
1134                          select @dummy = 1
1135  
1136                      end
1137                  end
1138                  else /* @inactive_days = NULL  */
1139                  begin
1140                      update master.dbo.syslogins
1141                      set status = status | 2,
1142                          lockreason = 0, /* locked manually using sp_locklogin */
1143                          lockdate = @current_date,
1144                          locksuid = suser_id()
1145                      where name like @loginame
1146                          and (status & 2) = 0
1147                          and suid != @current_id
1148                          and suid not in
1149                              (select suid from master.dbo.sysloginroles
1150                              where srid = role_id(@except))
1151                          and ((status & 512) != 512) /*
1152                  ** don't update 
1153                  ** LOGIN PROFILE, if any
1154                  */
1155                  end
1156              end
1157              else
1158              /* @except is NULL */
1159              begin
1160                  /*
1161                  ** Lock all the accounts matching @loginame 
1162                  ** excluding current account (which may be valid or invalid)
1163                  */
1164                  if (@inactive_days != NULL)
1165                  begin
1166                      if (@HA_CERTIFIED = 0)
1167                      begin
1168                          update master.dbo.syslogins
1169                          set status = status | 2, /* LOGIN_LOCKED */
1170                              lockreason = 1, /* locked because inactive */
1171                              lockdate = @current_date,
1172                              locksuid = suser_id()
1173                          where name like @loginame
1174                              /*
1175                              ** login is unlocked and is not exempted 
1176                              ** from locking due to inactivity.
1177                              */
1178                              and (status & 10) = 0
1179                              and suid != @current_id
1180                              and (sybsystemprocs.dbo.udf_inactivity_check(
1181                                      lpid, lastlogindate, pwdate,
1182                                      @current_date, @inactive_days)
1183                                  = 1)
1184                              and ((status & 512) != 512) /*
1185                      ** don't update 
1186                      ** LOGIN PROFILE, if any
1187                      */
1188                      end
1189                      else /* HA server is configured */
1190                      begin
1191                          select @dummy = 1
1192  
1193                      end
1194                  end
1195                  else /* @inactive_days = NULL  */
1196                  begin
1197                      update master.dbo.syslogins
1198                      set status = status | 2,
1199                          lockreason = 0, /* locked manually using sp_locklogin */
1200                          lockdate = @current_date,
1201                          locksuid = suser_id()
1202                      where name like @loginame
1203                          and (status & 2) = 0
1204                          and suid != @current_id
1205                          and ((status & 512) != 512) /*
1206                  ** don't update 
1207                  ** LOGIN PROFILE, if any
1208                  */
1209                  end
1210              end
1211          end
1212          else
1213          /* @loginame is NULL */
1214          begin
1215              if (@except_is_login = 1)
1216              begin
1217                  /* 
1218                  ** Lock all logins except that match @except 
1219                  ** leaving out current login
1220                  */
1221                  if (@inactive_days != NULL)
1222                  begin
1223                      if (@HA_CERTIFIED = 0)
1224                      begin
1225                          update master.dbo.syslogins
1226                          set status = status | 2, /* LOGIN_LOCKED */
1227                              lockreason = 1, /* locked because inactive */
1228                              lockdate = @current_date,
1229                              locksuid = suser_id()
1230                          where suid != @current_id
1231                              /*
1232                              ** login is unlocked and is not exempted 
1233                              ** from locking due to inactivity.
1234                              */
1235                              and (status & 10) = 0
1236                              and name != @except
1237                              and (sybsystemprocs.dbo.udf_inactivity_check(
1238                                      lpid, lastlogindate, pwdate,
1239                                      @current_date, @inactive_days)
1240                                  = 1)
1241                              and ((status & 512) != 512) /*
1242                      ** don't update 
1243                      ** LOGIN PROFILE, if any
1244                      */
1245                      end
1246                      else /* HA server is configured */
1247                      begin
1248                          select @dummy = 1
1249  
1250                      end
1251                  end
1252                  else /* @inactive_days = NULL  */
1253                  begin
1254                      update master.dbo.syslogins
1255                      set status = status | 2, /* LOGIN_LOCKED */
1256                          lockreason = 0, /* locked manually using sp_locklogin */
1257                          lockdate = @current_date,
1258                          locksuid = suser_id()
1259                      where suid != @current_id
1260                          and (status & 2) = 0
1261                          and name != @except
1262                          and ((status & 512) != 512) /*
1263                  ** don't update 
1264                  ** LOGIN PROFILE, if any
1265                  */
1266                  end
1267              end
1268              else if (@except_is_role = 1)
1269              begin
1270                  /* 
1271                  ** Lock all logins except whose role matches the
1272                  ** role specified as exception 
1273                  */
1274                  if (@inactive_days != NULL)
1275                  begin
1276                      if (@HA_CERTIFIED = 0)
1277                      begin
1278                          update master.dbo.syslogins
1279                          set status = status | 2,
1280                              lockreason = 1, /* locked because inactive */
1281                              lockdate = @current_date,
1282                              locksuid = suser_id()
1283                          where suid != @current_id
1284                              /*
1285                              ** login is unlocked and is not exempted 
1286                              ** from locking due to inactivity.
1287                              */
1288                              and (status & 10) = 0
1289                              and suid not in
1290                                  (select suid from master.dbo.sysloginroles
1291                                  where srid = role_id(@except))
1292                              and (sybsystemprocs.dbo.udf_inactivity_check(
1293                                      lpid, lastlogindate, pwdate,
1294                                      @current_date, @inactive_days)
1295                                  = 1)
1296                              and ((status & 512) != 512) /*
1297                      ** don't update 
1298                      ** LOGIN PROFILE, if any
1299                      */
1300                      end
1301                      else /* HA server is configured */
1302                      begin
1303                          select @dummy = 1
1304  
1305                      end
1306                  end
1307                  else /* @inactive_days = NULL */
1308                  begin
1309                      update master.dbo.syslogins
1310                      set status = status | 2,
1311                          lockreason = 0, /* locked manually using sp_locklogin */
1312                          lockdate = @current_date,
1313                          locksuid = suser_id()
1314                      where suid != @current_id
1315                          and (status & 2) = 0
1316                          and suid not in
1317                              (select suid from master.dbo.sysloginroles
1318                              where srid = role_id(@except))
1319                          and ((status & 512) != 512) /*
1320                  ** don't update 
1321                  ** LOGIN PROFILE, if any
1322                  */
1323                  end
1324              end
1325              else
1326              begin
1327                  /* No exception is specified */
1328                  if (@inactive_days != NULL)
1329                  begin
1330                      if (@HA_CERTIFIED = 0)
1331                      begin
1332                          update master.dbo.syslogins
1333                          set status = status | 2,
1334                              lockreason = 1, /* locked because inactive */
1335                              lockdate = @current_date,
1336                              locksuid = suser_id()
1337                          where suid != @current_id
1338                              /*
1339                              ** login is unlocked and is not exempted 
1340                              ** from locking due to inactivity.
1341                              */
1342                              and (status & 10) = 0
1343                              and (sybsystemprocs.dbo.udf_inactivity_check(
1344                                      lpid, lastlogindate, pwdate,
1345                                      @current_date, @inactive_days)
1346                                  = 1)
1347                              and ((status & 512) != 512) /*
1348                      ** don't update 
1349                      ** LOGIN PROFILE, if any
1350                      */
1351                      end
1352                      else
1353                      begin
1354                          select @dummy = 1
1355  
1356                      end
1357                  end
1358                  else
1359                  begin
1360                      update master.dbo.syslogins
1361                      set status = status | 2,
1362                          lockreason = 0, /* locked manually using sp_locklogin */
1363                          lockdate = @current_date,
1364                          locksuid = suser_id()
1365                      where suid != @current_id
1366                          and (status & 2) = 0
1367                          and ((status & 512) != 512) /*
1368                  ** don't update 
1369                  ** LOGIN PROFILE, if any
1370                  */
1371                  end
1372              end
1373          end
1374  
1375          /*
1376          ** Because of some possible race-conditions unlock the account
1377          ** just before quitting to ensure the current account remains 
1378          ** unlocked. Race between different logins trying to lock a set of accounts
1379          ** The last login which executes this statement unlocks that
1380          ** account, at the end we are sure that at least one login remains unlocked
1381          */
1382  
1383          select @row_count_temp = @@rowcount
1384  
1385          if (@encompasses_all_logins = 1)
1386          begin
1387              update master.dbo.syslogins
1388              set status = status & ~ 2 /* Unlock current account before quitting */
1389              where suid = @current_id
1390          end
1391  
1392          /*
1393          **  Check @@rowcount when it works
1394          */
1395  
1396          if (@row_count_temp > 0)
1397          begin -- {
1398  
1399  
1400  
1401              if (@log_for_rep = 1)
1402              begin
1403                  /*
1404                  ** If the 'master' database is marked for replication, the
1405                  ** T-SQL built-in 'logexec()' will log for replication the
1406                  ** execution instance of this system procedure.  Otherwise,
1407                  ** the T-SQL built-in 'logexec()' is a no-op.
1408                  */
1409                  if (logexec(1) != 1)
1410                  begin
1411                      raiserror 17756, "sp_locklogin", "master"
1412                      goto clean_all
1413                  end
1414              end
1415              if (@gp_enabled = 1)
1416              begin -- {
1417                  select @uwp = users_with_privilege(
1418                          'manage security permissions', 0)
1419                  if @uwp is NULL
1420                  begin -- {
1421                      select @lockpriverr = 1
1422                      goto clean_all
1423                  end -- }
1424                  select @uwp = users_with_privilege(
1425                          'manage server permissions', 0)
1426                  if @uwp is NULL
1427                  begin -- {
1428                      select @lockpriverr = 1
1429                      goto clean_all
1430                  end -- }
1431              end -- }
1432  
1433              if ((@gp_enabled = 1) or (@log_for_rep = 1) or (@HA_CERTIFIED = 1))
1434                  commit tran rs_logexec
1435  
1436              /*
1437              ** 17919, "Account locked."
1438              */
1439              exec sp_getmessage 17919, @msg output
1440              print @msg
1441              return (0)
1442          end -- }
1443          else
1444          begin
1445              /*
1446              ** 19643, "No account(s) locked."
1447              */
1448              exec sp_getmessage 19643, @msg output
1449              print @msg
1450  
1451              /* 
1452              ** No accounts were locked, as there were no unlocked accounts to be locked.
1453              ** so rollback the transaction , but return 0 as return value.
1454              */
1455              if ((@gp_enabled = 1) or (@log_for_rep = 1) or (@HA_CERTIFIED = 1))
1456                  rollback tran rs_logexec
1457              return (0)
1458          end
1459  
1460  clean_all:
1461          if ((@gp_enabled = 1) and (@lockpriverr != 0))
1462          begin
1463              /**
1464              ** 17040, "Operation failed. There must be at least one
1465              ** unlocked login with privilege MANAGE SECRUITY PERMISSIONS 
1466              ** and at least one unlocked login with privilege MANAGE 
1467              ** SERVER PERMISSIONS after this operation.
1468              **/
1469              raiserror 17040, "sp_locklogin"
1470          end
1471          if ((@gp_enabled = 1) or (@log_for_rep = 1) or (@HA_CERTIFIED = 1))
1472              rollback tran rs_logexec
1473          return (1)
1474      end
1475  


exec sp_procxmode 'sp_locklogin', 'AnyMode'
go

Grant Execute on sp_locklogin to public
go
RESULT SETS
sp_locklogin_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 394
 QJWI 5 Join or Sarg Without Index 492
 QJWI 5 Join or Sarg Without Index 549
 QJWI 5 Join or Sarg Without Index 663
 QJWI 5 Join or Sarg Without Index 673
 QJWI 5 Join or Sarg Without Index 750
 QJWI 5 Join or Sarg Without Index 759
 QJWI 5 Join or Sarg Without Index 855
 QJWI 5 Join or Sarg Without Index 909
 QJWI 5 Join or Sarg Without Index 1120
 QJWI 5 Join or Sarg Without Index 1148
 QJWI 5 Join or Sarg Without Index 1289
 QJWI 5 Join or Sarg Without Index 1316
 MCTR 4 Conditional Begin Tran or Commit Tran 439
 MCTR 4 Conditional Begin Tran or Commit Tran 591
 MCTR 4 Conditional Begin Tran or Commit Tran 991
 MCTR 4 Conditional Begin Tran or Commit Tran 1434
 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 @scope: varchar(32) = int 821
 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}
203
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 208
 QTYP 4 Comparison type mismatch smallint = int 208
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 215
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 791
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 TNOU 4 Table with no unique index master..sysloginroles master..sysloginroles
 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..sysprocesses  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_locklogin  
 MNAC 3 Not using ANSI 'is null' 425
 MNAC 3 Not using ANSI 'is null' 773
 MNAC 3 Not using ANSI 'is null' 1019
 MNAC 3 Not using ANSI 'is null' 1104
 MNAC 3 Not using ANSI 'is null' 1164
 MNAC 3 Not using ANSI 'is null' 1221
 MNAC 3 Not using ANSI 'is null' 1274
 MNAC 3 Not using ANSI 'is null' 1328
 MNER 3 No Error Check should check return value of exec 146
 MNER 3 No Error Check should check return value of exec 301
 MNER 3 No Error Check should check return value of exec 322
 MNER 3 No Error Check should check return value of exec 335
 MNER 3 No Error Check should check return value of exec 340
 MNER 3 No Error Check should check return value of exec 373
 MNER 3 No Error Check should check return value of exec 400
 MNER 3 No Error Check should check return value of exec 419
 MNER 3 No Error Check should check return value of exec 431
 MNER 3 No Error Check should check @@error after update 465
 MNER 3 No Error Check should check @@error after update 486
 MNER 3 No Error Check should check @@error after update 503
 MNER 3 No Error Check should check @@error after update 526
 MNER 3 No Error Check should check @@error after update 542
 MNER 3 No Error Check should check @@error after update 559
 MNER 3 No Error Check should check return value of exec 596
 MNER 3 No Error Check should check return value of exec 945
 MNER 3 No Error Check should check return value of exec 980
 MNER 3 No Error Check should check @@error after update 1039
 MNER 3 No Error Check should check @@error after update 1082
 MNER 3 No Error Check should check @@error after update 1108
 MNER 3 No Error Check should check @@error after update 1140
 MNER 3 No Error Check should check @@error after update 1168
 MNER 3 No Error Check should check @@error after update 1197
 MNER 3 No Error Check should check @@error after update 1225
 MNER 3 No Error Check should check @@error after update 1254
 MNER 3 No Error Check should check @@error after update 1278
 MNER 3 No Error Check should check @@error after update 1309
 MNER 3 No Error Check should check @@error after update 1332
 MNER 3 No Error Check should check @@error after update 1360
 MNER 3 No Error Check should check @@error after update 1387
 MNER 3 No Error Check should check return value of exec 1439
 MNER 3 No Error Check should check return value of exec 1448
 MUCO 3 Useless Code Useless Begin-End Pair 66
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 196
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 243
 MUCO 3 Useless Code Useless Brackets 244
 MUCO 3 Useless Code Useless Brackets 248
 MUCO 3 Useless Code Useless Brackets 259
 MUCO 3 Useless Code Useless Brackets 262
 MUCO 3 Useless Code Useless Brackets 307
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 324
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 342
 MUCO 3 Useless Code Useless Brackets 350
 MUCO 3 Useless Code Useless Brackets 375
 MUCO 3 Useless Code Useless Brackets 403
 MUCO 3 Useless Code Useless Brackets 414
 MUCO 3 Useless Code Useless Brackets 421
 MUCO 3 Useless Code Useless Brackets 425
 MUCO 3 Useless Code Useless Brackets 433
 MUCO 3 Useless Code Useless Brackets 438
 MUCO 3 Useless Code Useless Brackets 443
 MUCO 3 Useless Code Useless Brackets 445
 MUCO 3 Useless Code Useless Brackets 466
 MUCO 3 Useless Code Useless Brackets 478
 MUCO 3 Useless Code Useless Brackets 487
 MUCO 3 Useless Code Useless Brackets 504
 MUCO 3 Useless Code Useless Brackets 509
 MUCO 3 Useless Code Useless Brackets 524
 MUCO 3 Useless Code Useless Brackets 527
 MUCO 3 Useless Code Useless Brackets 540
 MUCO 3 Useless Code Useless Brackets 543
 MUCO 3 Useless Code Useless Brackets 560
 MUCO 3 Useless Code Useless Brackets 575
 MUCO 3 Useless Code Useless Brackets 583
 MUCO 3 Useless Code Useless Brackets 590
 MUCO 3 Useless Code Useless Brackets 598
 MUCO 3 Useless Code Useless Brackets 619
 MUCO 3 Useless Code Useless Brackets 621
 MUCO 3 Useless Code Useless Brackets 634
 MUCO 3 Useless Code Useless Brackets 641
 MUCO 3 Useless Code Useless Brackets 650
 MUCO 3 Useless Code Useless Brackets 659
 MUCO 3 Useless Code Useless Brackets 669
 MUCO 3 Useless Code Useless Brackets 693
 MUCO 3 Useless Code Useless Brackets 700
 MUCO 3 Useless Code Useless Brackets 710
 MUCO 3 Useless Code Useless Brackets 714
 MUCO 3 Useless Code Useless Brackets 723
 MUCO 3 Useless Code Useless Brackets 730
 MUCO 3 Useless Code Useless Brackets 739
 MUCO 3 Useless Code Useless Brackets 747
 MUCO 3 Useless Code Useless Brackets 756
 MUCO 3 Useless Code Useless Brackets 773
 MUCO 3 Useless Code Useless Brackets 775
 MUCO 3 Useless Code Useless Brackets 781
 MUCO 3 Useless Code Useless Brackets 790
 MUCO 3 Useless Code Useless Brackets 798
 MUCO 3 Useless Code Useless Brackets 811
 MUCO 3 Useless Code Useless Brackets 819
 MUCO 3 Useless Code Useless Brackets 825
 MUCO 3 Useless Code Useless Brackets 827
 MUCO 3 Useless Code Useless Brackets 844
 MUCO 3 Useless Code Useless Brackets 883
 MUCO 3 Useless Code Useless Brackets 899
 MUCO 3 Useless Code Useless Brackets 934
 MUCO 3 Useless Code Useless Brackets 939
 MUCO 3 Useless Code Useless Brackets 954
 MUCO 3 Useless Code Useless Brackets 983
 MUCO 3 Useless Code Useless Brackets 990
 MUCO 3 Useless Code Useless Brackets 1007
 MUCO 3 Useless Code Useless Brackets 1010
 MUCO 3 Useless Code Useless Brackets 1012
 MUCO 3 Useless Code Useless Brackets 1019
 MUCO 3 Useless Code Useless Brackets 1032
 MUCO 3 Useless Code Useless Brackets 1097
 MUCO 3 Useless Code Useless Brackets 1104
 MUCO 3 Useless Code Useless Brackets 1106
 MUCO 3 Useless Code Useless Brackets 1164
 MUCO 3 Useless Code Useless Brackets 1166
 MUCO 3 Useless Code Useless Brackets 1215
 MUCO 3 Useless Code Useless Brackets 1221
 MUCO 3 Useless Code Useless Brackets 1223
 MUCO 3 Useless Code Useless Brackets 1268
 MUCO 3 Useless Code Useless Brackets 1274
 MUCO 3 Useless Code Useless Brackets 1276
 MUCO 3 Useless Code Useless Brackets 1328
 MUCO 3 Useless Code Useless Brackets 1330
 MUCO 3 Useless Code Useless Brackets 1385
 MUCO 3 Useless Code Useless Brackets 1396
 MUCO 3 Useless Code Useless Brackets 1401
 MUCO 3 Useless Code Useless Brackets 1409
 MUCO 3 Useless Code Useless Brackets 1415
 MUCO 3 Useless Code Useless Brackets 1433
 MUCO 3 Useless Code Useless Brackets 1441
 MUCO 3 Useless Code Useless Brackets 1455
 MUCO 3 Useless Code Useless Brackets 1457
 MUCO 3 Useless Code Useless Brackets 1461
 MUCO 3 Useless Code Useless Brackets 1471
 MUCO 3 Useless Code Useless Brackets 1473
 MUIN 3 Column created using implicit nullability 136
 QAFM 3 Var Assignment from potentially many rows 202
 QAFM 3 Var Assignment from potentially many rows 206
 QAFM 3 Var Assignment from potentially many rows 213
 QAFM 3 Var Assignment from potentially many rows 218
 QCRS 3 Conditional Result Set 303
 QISO 3 Set isolation level 155
 QNAJ 3 Not using ANSI Inner Join 362
 QNAJ 3 Not using ANSI Inner Join 632
 QNAJ 3 Not using ANSI Inner Join 639
 QNAJ 3 Not using ANSI Inner Join 657
 QNAJ 3 Not using ANSI Inner Join 667
 QNAJ 3 Not using ANSI Inner Join 691
 QNAJ 3 Not using ANSI Inner Join 698
 QNAJ 3 Not using ANSI Inner Join 721
 QNAJ 3 Not using ANSI Inner Join 728
 QNAJ 3 Not using ANSI Inner Join 745
 QNAJ 3 Not using ANSI Inner Join 754
 QNAJ 3 Not using ANSI Inner Join 833
 QNAJ 3 Not using ANSI Inner Join 850
 QNAJ 3 Not using ANSI Inner Join 870
 QNAJ 3 Not using ANSI Inner Join 889
 QNAJ 3 Not using ANSI Inner Join 905
 QNAJ 3 Not using ANSI Inner Join 920
 QNUA 3 Should use Alias: Table master..sysloginroles 664
 QNUA 3 Should use Alias: Table master..sysloginroles 674
 QNUA 3 Should use Alias: Table master..sysloginroles 751
 QNUA 3 Should use Alias: Table master..sysloginroles 760
 QNUA 3 Should use Alias: Table master..sysloginroles 856
 QNUA 3 Should use Alias: Table master..sysloginroles 910
 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}
208
 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}
215
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered
(name, parent, config)
Intersection: {name}
220
 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}
791
 QTJ1 3 Table only appears in inner join clause 362
 QTJ1 3 Table only appears in inner join clause 833
 QTJ1 3 Table only appears in inner join clause 850
 QTJ1 3 Table only appears in inner join clause 870
 QTJ1 3 Table only appears in inner join clause 889
 QTJ1 3 Table only appears in inner join clause 905
 QTJ1 3 Table only appears in inner join clause 920
 VNRD 3 Variable is not read @encompasses_all_rmt_ha_logins 87
 VNRD 3 Variable is not read @db_rep_level_all 117
 VNRD 3 Variable is not read @db_rep_level_l1 119
 VNRD 3 Variable is not read @dummy 1354
 MRST 2 Result Set Marker 303
 MSUB 2 Subquery Marker 314
 MSUB 2 Subquery Marker 352
 MSUB 2 Subquery Marker 362
 MSUB 2 Subquery Marker 388
 MSUB 2 Subquery Marker 392
 MSUB 2 Subquery Marker 395
 MSUB 2 Subquery Marker 493
 MSUB 2 Subquery Marker 550
 MSUB 2 Subquery Marker 632
 MSUB 2 Subquery Marker 639
 MSUB 2 Subquery Marker 657
 MSUB 2 Subquery Marker 664
 MSUB 2 Subquery Marker 667
 MSUB 2 Subquery Marker 674
 MSUB 2 Subquery Marker 691
 MSUB 2 Subquery Marker 698
 MSUB 2 Subquery Marker 721
 MSUB 2 Subquery Marker 728
 MSUB 2 Subquery Marker 745
 MSUB 2 Subquery Marker 751
 MSUB 2 Subquery Marker 754
 MSUB 2 Subquery Marker 760
 MSUB 2 Subquery Marker 790
 MSUB 2 Subquery Marker 833
 MSUB 2 Subquery Marker 850
 MSUB 2 Subquery Marker 856
 MSUB 2 Subquery Marker 870
 MSUB 2 Subquery Marker 889
 MSUB 2 Subquery Marker 905
 MSUB 2 Subquery Marker 910
 MSUB 2 Subquery Marker 920
 MSUB 2 Subquery Marker 975
 MSUB 2 Subquery Marker 1121
 MSUB 2 Subquery Marker 1149
 MSUB 2 Subquery Marker 1290
 MSUB 2 Subquery Marker 1317
 MTR1 2 Metrics: Comments Ratio Comments: 42% 60
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 179 = 197dec - 20exi + 2 60
 MTR3 2 Metrics: Query Complexity Complexity: 680 60
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, s=master..syssrvroles} 0 362
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, l2=master..syslogins} 0 632
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, l2=master..syslogins} 0 639
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, l2=master..syslogins} 0 691
 PRED_QUERY_COLLECTION 2 {l=master..syslogins, l2=master..sysloginroles} 0 698
 PRED_QUERY_COLLECTION 2 {l=master..syslogins, l2=master..sysloginroles} 0 721
 PRED_QUERY_COLLECTION 2 {l=master..sysloginroles, l2=master..syslogins} 0 728
 PRED_QUERY_COLLECTION 2 {l=master..syslogins, p=master..sysprocesses} 0 833
 PRED_QUERY_COLLECTION 2 {l=master..syslogins, p=master..sysprocesses} 0 870
 PRED_QUERY_COLLECTION 2 {l=master..syslogins, p=master..sysprocesses} 0 889
 PRED_QUERY_COLLECTION 2 {l=master..syslogins, p=master..sysprocesses} 0 920

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
writes table tempdb..#dummy_table (1) 
reads table master..syssrvroles (1)  
writes table sybsystemprocs..sp_locklogin_rset_001 
reads table master..sysattributes (1)  
reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
reads table master..sysloginroles (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
read_writes table master..syslogins (1)  
reads table master..sysprocesses (1)  

CALLERS
called by proc sybsystemprocs..sp_addlogin