DatabaseProcApplicationCreatedLinks
sybsystemprocssp_modifylogin  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	5.0	1.0	10/22/91	sproc/src/modifylogin */
4     
5     /*
6     ** Generated by spgenmsgs.pl on Thu Feb  2 00:39:18 2006 
7     */
8     /*
9     ** raiserror Messages for modifylogin [Total 14]
10    **
11    ** 17260, "Can't run %1! from within a transaction."
12    ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record."
13    ** 17880, "No such account -- nothing changed."
14    ** 17925, "You entered an invalid option name. No change was made."
15    ** 17927, "Error in changing the value of the specified column."
16    ** 17928, "Invalid role name specified -- nothing changed."
17    ** 17929, "The specified role is not granted to the account -- nothing changed."
18    ** 17930, "Specify a value for the option to be modified."
19    ** 17932, "You entered an invalid value. No change was made."
20    ** 17933, "Specify the name of the option to be modified."
21    ** 18388, "You must be in the master database in order to run '%1!'."
22    ** 18409, "The built-in function '%1!' failed. Please see the other messages printed along with this message."
23    ** 18898, "Login script '%1!' is not valid."
24    ** 19257, "The authentication mechanism '%1!' is not valid."
25    */
26    /*
27    ** sp_getmessage Messages for modifylogin [Total 5]
28    **
29    ** 17926, "Option changed."
30    ** 17934, "All overrides for the password security option have been removed."
31    ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on the companion server '%2!'."
32    ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins."
33    ** 19259, "Warning. Authentication mechanism '%1!' is not enabled."
34    ** 19448 "An existing login mapping for user '%1!' allows only '%2!' 
35    **		authentication mechanism to be used."
36    ** 19812 "There is no login-specific '%1!' attribute set for this user."
37    ** 19813 "The login-specific '%1!' attribute has been removed."
38    */
39    /*
40    ** End spgenmsgs.pl output.
41    */
42    
43    /* 
44    ** IMPORTANT: Please read the following instructions before
45    **   making changes to this stored procedure.
46    **
47    **	To make this stored procedure compatible with High Availability (HA),
48    **	changes to certain system tables must be propagated 
49    **	to the companion server under some conditions.
50    **	The tables include (but are not limited to):
51    **		syslogins, sysservers, sysattributes, systimeranges,
52    **		sysresourcelimits, sysalternates, sysdatabases,
53    **		syslanguages, sysremotelogins, sysloginroles,
54    **		sysalternates (master DB only), systypes (master DB only),
55    **		sysusers (master DB only), sysprotects (master DB only)
56    **	please refer to the HA documentation for detail.
57    **
58    **	Here is what you need to do: 
59    **	For each insert/update/delete statement, add three sections to
60    **	-- start HA transaction prior to the statement
61    **	-- add the statement
62    **	-- add HA synchronization code to propagate the change to the companion
63    **
64    **	For example, if you are adding 
65    **		insert master.dbo.syslogins ......
66    **	the code should look like:
67    **	1. Before that SQL statement:
68    **		
69    **	2. Now, the SQL statement:
70    **		insert master.dbo.syslogins ......
71    **	3. Add a HA synchronization section right after the SQL statement:
72    **		
73    **
74    **	You may need to do similar change for each built-in function you
75    **	want to add.
76    **
77    **	Finally, add a separate part at a place where it can not
78    **	be reached by the normal execution path:
79    **	clean_all:
80    **		
81    **		return (1)
82    */
83    
84    create procedure sp_modifylogin
85        @loginame varchar(255), /* the login name of the account being modified */
86        @option varchar(30) = NULL, /* the option to be updated */
87        @value varchar(255) = NULL /* the new character value of the option */
88    as
89        declare @suid int /* suid of account to be modified   */
90        declare @msg varchar(1024) /* message text */
91        declare @retstat int /* return status from other procedures */
92        declare @enable_login_role int /* value of the status bit which is used
93        ** for enabling a role
94        */
95    
96        declare @action int /* Insert, update or delete 
97        ** Sysattributes entry 
98        */
99        declare @attrib int /* attribute id in Sysattributes */
100       declare @passeclass int /* Class id in Sysattributes */
101       declare @int_val int /* convert char input into integer */
102       declare @deleted int /* toggle to print the right return message */
103       declare @rowcount_saved int /* Store @@rowcount as "if-then" resets it to 0*/
104       declare @dummy int
105       declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
106       declare @defdb varchar(255) /* User's default database */
107       declare @dbname varchar(30) /* Parsed login script dbname */
108       declare @own varchar(30) /* Parsed login script owner */
109       declare @objname varchar(30) /* Parsed login script name */
110       declare @status int /* Output of sp_namecrack */
111       declare @procid int /* Login script id */
112       declare @curdb varchar(30) /* User's current database */
113       declare @sa_role int /* has sa_role */
114       declare @sso_role int /* has sso_role */
115       declare @sa_audited int /* proc_role(sa_role) was called */
116       declare @sso_audited int /* proc_role(sso_role) was called */
117       declare @sqltext varchar(2055) /* a string buffer for concatenated sql */
118       declare @authid int /* authe mechanism id */
119       declare @allauth int /* all auth mechanisms mask */
120       declare @map_authid int /* auth mech id for login mapping */
121       declare @map_authnm varchar(30) /* auth mech name for login mapping */
122       declare @login_class int /* sysattributes class for login mapping */
123       declare @login_attrib int /* sysattributes attribute for login mapping */
124       declare @config int
125       declare @err1 int /* temp. variable to store @@error */
126   
127       declare @log_for_rep int
128       declare @db_rep_level_all int
129       declare @db_rep_level_none int
130       declare @db_rep_level_l1 int
131       declare @lt_rep_get_failed int
132   
133       /*
134       ** Initialize some constants
135       */
136       select @db_rep_level_all = - 1,
137           @db_rep_level_none = 0,
138           @db_rep_level_l1 = 1,
139           @db_rep_level_l1 = 1,
140           @lt_rep_get_failed = - 2
141   
142       if (@loginame is null and @option = 'login script')
143       begin
144           exec @retstat = sp_logintrigger @value
145           return @retstat
146       end
147   
148       select @HA_CERTIFIED = 0
149   
150   
151   
152       /* check to see if we are using HA specific SP for a HA enabled server */
153       exec @retstat = sp_ha_check_certified 'sp_modifylogin', @HA_CERTIFIED
154       if (@retstat != 0)
155           return (1)
156   
157       /* Initialize variables */
158       select @rowcount_saved = 0,
159           @enable_login_role = 1,
160           @sa_audited = 0,
161           @sso_audited = 0,
162           @sa_role = charindex("sa_role", show_role()),
163           @sso_role = charindex("sso_role", show_role())
164   
165       /*
166       ** Do not allow this system procedure to be run from within a transaction
167       ** to avoid creating a multi-database transaction where the 'master'
168       ** database is not the co-ordinating database.
169       */
170       if @@trancount > 0
171       begin
172           /* 17260, "Can't run %1! from within a transaction." */
173           raiserror 17260, "sp_modifylogin"
174           return (1)
175       end
176       else
177       begin
178           set chained off
179       end
180   
181       set transaction isolation level 1
182   
183       /*
184       ** Get the replication status of the 'master' database
185       */
186       select @log_for_rep = getdbrepstat(1)
187       if (@log_for_rep = @lt_rep_get_failed)
188       begin
189           /*
190           ** 18409, "The built-in function getdbrepstat() failed.
191           ** Please see the other messages printed along with this message."
192           */
193           raiserror 18409, "getdbrepstat"
194           return (1)
195       end
196   
197       /*
198       ** Convert the replication status to a boolean
199       */
200       if (@log_for_rep != @db_rep_level_none)
201           select @log_for_rep = 1
202       else
203           select @log_for_rep = 0
204   
205       /*
206       ** If we are logging this system procedure for replication, we must be in
207       ** the 'master' database to avoid creating a multi-database transaction
208       ** which could make recovery of the 'master' database impossible.
209       */
210       if (@log_for_rep = 1) and (db_name() != "master")
211       begin
212           /*
213           ** 18388, "You must be in the master database in order to run '%1!'."
214           */
215           raiserror 18388, "sp_modifylogin"
216           return (1)
217       end
218   
219       if (@option is NULL)
220       begin
221           /*
222           ** 17933, "Specify the name of the option to be modified."
223           */
224           raiserror 17933
225           return (1)
226       end
227   
228       /* Allow NULL for 'login script' option when @loginame != 'all overrides' */
229       if ((@value is NULL) and ((@option != "login script") or
230                   (@loginame = "all overrides")))
231       begin
232           /*
233           ** 17930, "Specify a value for the option to be modified."
234           */
235           raiserror 17930
236           return (1)
237       end
238   
239       /* User is trying to modify user's own login */
240       if (suser_id(@loginame) = suser_id())
241       begin
242           /*
243           ** Allow only logins with sa/sso role to modify the foll. options:
244           ** o "min passwd length"
245           ** o "passwd expiration"
246           ** o "max failed_logins" 
247           ** o "login script"
248           ** o "add default role"
249           ** o "drop default role"
250           ** o "authenticate with"
251           */
252   
253   
254           if (@option = "min passwd length" or
255                   @option = "passwd expiration" or
256                   @option = "max failed_logins" or
257                   @option = "login script" or
258                   @option = "add default role" or
259                   @option = "drop default role" or
260                   @option = "authenticate with")
261           begin
262   
263               /* check if user has sa or sso role, proc_role will also do auditing
264               ** if required. proc_role will also print error message if required.
265               */
266   
267               if (@sa_role = 0 and @sso_role = 0)
268               begin
269                   select @dummy = proc_role("sa_role")
270                   raiserror 17888, "modifylogin"
271                   return (1)
272               end
273               else
274               begin
275                   if (@sa_role > 0)
276                   begin
277                       select @sa_audited = 1
278                       select @dummy = proc_role("sa_role")
279                   end
280                   if (@sso_role > 0)
281                   begin
282                       select @sso_audited = 1
283                       select @dummy = proc_role("sso_role")
284                   end
285               end
286           end
287       end
288       /*
289       ** If user is trying to modify someone else's login then
290       ** check if user has sso role, when modifying non security-related 
291       ** stuff sa role is also allowed. Proc_role will perform auditing
292       ** and print error message if required.
293       */
294       if (suser_id(@loginame) != suser_id())
295       begin
296           if (@option = "add default role" or
297                   @option = "drop default role" or
298                   @option = "passwd expiration" or
299                   @option = "min passwd length" or
300                   @option = "max failed_logins" or
301                   @option = "login script" or
302                   @option = "authenticate with")
303           begin
304               /* With or without SSO role, must be audited */
305               if (@sso_audited = 0)
306                   select @dummy = proc_role("sso_role")
307   
308               /* Cannot proceed without SSO role */
309               if (@sso_role = 0)
310                   return (1)
311           end
312           else
313           begin
314               /* Only SA and SSO can proceed */
315               if (@sa_role = 0 and @sso_role = 0)
316               begin
317                   select @dummy = proc_role("sa_role") /* to perform auditing if sa_role fails */
318                   raiserror 17888, "modifylogin"
319                   return (1)
320               end
321   
322               /* Audit whatever has not been done yet */
323               if (@sso_role > 0 and @sso_audited = 0)
324                   select @dummy = proc_role("sso_role")
325               if (@sa_role > 0 and @sa_audited = 0)
326                   select @dummy = proc_role("sa_role")
327           end
328       end
329   
330       if ((@option = "passwd expiration") or (@option = "min passwd length") or
331               (@option = "max failed_logins"))
332       begin
333           /* master.dbo.sysattributes class that stores login overrides. */
334           select @passeclass = 14
335   
336           /* With the exception of 'clear', these options expect only numbers */
337           select @value = lower(@value)
338           if @value != "clear"
339           begin
340               select @int_val = convert(int, @value)
341   
342               /*
343               ** int_val can't be less than -1 or greater than 32767
344               ** For "min passwd length", it can't be greater than 30
345               */
346               if ((@int_val < - 1) or ((@option = "min passwd length") and
347                           (@int_val > 30)) or (@int_val > 32767))
348               begin
349                   /*
350                   ** 17932, "You entered an invalid value. No 
351                   ** change was made."
352                   */
353                   raiserror 17932
354                   return (1)
355               end
356           end
357       end
358   
359   
360       /* Check if "all overrides". If so, check the new value input.
361       ** If it is -1, then all entries in Sysattributes corresponding 
362       ** to the attribute are deleted. Else, the entries in master.dbo.sysattributes 
363       ** are overwritten with the new value
364       */
365       if (@loginame = "all overrides")
366       begin
367   
368           if (@option = "passwd expiration")
369               select @attrib = 0
370           else
371           if (@option = "min passwd length")
372               select @attrib = 1
373           else
374           if (@option = "max failed_logins")
375               select @attrib = 2
376           else
377           begin
378               /*
379               ** 17925, You entered an invalid option name. No change was made."
380               */
381               raiserror 17925
382               return (1)
383           end
384   
385           if @int_val = - 1
386           /* Remove all the overrides for the option */
387           begin
388               delete from master.dbo.sysattributes
389               where class = @passeclass AND attribute = @attrib
390                   AND object_cinfo = "login"
391   
392   
393   
394               /*
395               ** 17934, "All overrides for the password security option 
396               ** have been removed."
397               */
398               exec sp_getmessage 17934, @msg output
399               print @msg
400               return (0)
401           end
402   
403           else
404           /* Modify the value of the overrides for the option */
405           begin
406               if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
407                   begin tran rs_logexec
408   
409   
410               update master.dbo.sysattributes
411               set int_value = @int_val
412               where class = @passeclass AND attribute = @attrib
413                   AND object_cinfo = "login"
414   
415               select @err1 = @@error, @rowcount_saved = @@rowcount
416   
417   
418   
419               if (@log_for_rep = 1)
420               begin
421                   /*
422                   ** If the 'master' database is marked for
423                   ** replication, the T-SQL built-in 'logexec()' will
424                   ** log for replication the execution instance of
425                   ** this system procedure.  Otherwise, the T-SQL
426                   ** built-in 'logexec()' is a no-op.
427                   */
428                   if (logexec(1) != 1)
429                   begin
430                       /*
431                       ** 17756, "The execution of the stored procedure
432                       **  '%1!' in database '%2!' was aborted because 
433                       **  there was an error in writing the replication
434                       **  log record."
435                       */
436                       raiserror 17756, "sp_modifylogin", "master"
437                       goto clean_all
438                   end
439               end
440   
441               if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
442                   commit tran rs_logexec
443           end
444       end
445   
446       else
447       /* This must be modification for a specific login. */
448       begin
449           select @suid = suid
450           from master.dbo.syslogins
451           where name = @loginame
452               and ((status & 512) != 512) /* not LOGIN PROFILE */
453   
454           /* Check if the loginame exists */
455           if (@suid is NULL)
456           begin
457               /*
458               ** 17880, "No such account -- nothing changed."
459               */
460               raiserror 17880
461               return (1)
462           end
463   
464   
465           /*
466           **  Update takes place here: option can only be fullname for C2
467           */
468           if @option = "fullname"
469           begin
470   
471               if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
472                   begin tran rs_logexec
473   
474   
475   
476               update master.dbo.syslogins set fullname = @value where suid = @suid
477               select @rowcount_saved = @@rowcount
478           end
479           else if (@option = "login script" and @loginame != "all overrides")
480           begin
481   
482               if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
483                   begin tran rs_logexec
484   
485   
486   
487               /*
488               ** If the 'login script' is provided, get the object id
489               ** from the default database verifying that it's really 
490               ** a stored procedure.
491               */
492               if (@value is not NULL)
493               begin
494                   set nocount on
495   
496                   select @defdb = dbname
497                   from master.dbo.syslogins
498                   where name = @loginame
499   
500                   exec @status = sp_namecrack @value, @db = @dbname output,
501                       @owner = @own output, @object = @objname output
502   
503                   /* If a db name is given, it has to be the user's default */
504                   if ((@status != 0) or
505                           ((@dbname is not null) and (@dbname != @defdb)))
506                   begin
507                       /*
508                       ** 18898, "Login script '%1!' is not valid."
509                       */
510                       raiserror 18898, @value
511                       goto clean_all
512                   end
513   
514                   /* Verify that the object exists and its a stored proc */
515                   if (@own is not null)
516                       exec ("declare @dummy int " +
517                       "select @dummy = 1 from " + @defdb +
518                       "..sysobjects where name='" + @objname + "'" +
519                       " and type = 'P'" +
520                       " and uid = user_id('" + @own + "')")
521                   else
522                       exec ("declare @dummy int " +
523                       "select @dummy = 1 from " + @defdb +
524                       "..sysobjects where name='" + @objname + "'" +
525                       " and type = 'P'")
526   
527                   if (@@rowcount = 0)
528                       select @procid = NULL
529                   else if (@own is not null)
530                       select @procid = object_id(@defdb + '.' + @own + '.' + @objname)
531                   else
532                       select @procid = object_id(@defdb + '..' + @objname)
533   
534                   if (@procid is NULL)
535                   begin
536                       /*
537                       ** 18898, "Login script '%1!' is not valid."
538                       */
539                       raiserror 18898, @value
540                       goto clean_all
541                   end
542               end
543               else
544               begin
545                   select @procid = NULL
546               end
547   
548               update master.dbo.syslogins set procid = @procid
549               where name = @loginame
550   
551               select @rowcount_saved = @@rowcount
552           end
553   
554           else if @option = "defdb"
555           begin
556               execute @retstat = sp_defaultdb @loginame, @value
557   
558               if (@retstat = 0)
559               begin
560                   if (@log_for_rep = 1)
561                   begin
562                       /*
563                       ** If the 'master' database is marked for
564                       ** replication, the T-SQL built-in 'logexec()' will
565                       ** log for replication the execution instance of
566                       ** this system procedure.  Otherwise, the T-SQL
567                       ** built-in 'logexec()' is a no-op.
568                       */
569                       if (logexec(1) != 1)
570                       begin
571                           /*
572                           ** 17756, "The execution of the stored procedure
573                           ** '%1!' in database '%2!' was aborted because
574                           ** there was an error in writing the replication
575                           ** log record."
576                           */
577                           raiserror 17756, "sp_modifylogin", "master"
578                           goto clean_all
579                       end
580                   end
581               end
582               return (@retstat)
583           end
584           else if @option = "deflanguage"
585           begin
586               execute @retstat = sp_defaultlanguage @loginame, @value
587   
588               if (@retstat = 0)
589               begin
590                   if (@log_for_rep = 1)
591                   begin
592                       /*
593                       ** If the 'master' database is marked for
594                       ** replication, the T-SQL built-in 'logexec()' will
595                       ** log for replication the execution instance of
596                       ** this system procedure.  Otherwise, the T-SQL
597                       ** built-in 'logexec()' is a no-op.
598                       */
599                       if (logexec(1) != 1)
600                       begin
601                           /*
602                           ** 17756, "The execution of the stored procedure
603                           ** '%1!' in database '%2!' was aborted because
604                           ** there was an error in writing the replication
605                           ** log record."
606                           */
607                           raiserror 17756, "sp_modifylogin", "master"
608                           goto clean_all
609                       end
610                   end
611               end
612               return (@retstat)
613           end
614           else if @option = "add default role" or @option = "drop default role"
615           begin
616               /* 
617               ** verify that the role exists
618               */
619               if (role_id(@value) is NULL)
620               begin
621                   /*
622                   ** Invalid role name specified -- nothing changed
623                   */
624                   raiserror 17928
625                   return (1)
626               end
627   
628               /*
629               ** verify that the role is granted to the loginame
630               */
631               if not exists (select 1 from master.dbo.sysloginroles
632                       where suid = @suid and srid = role_id(@value))
633               begin
634                   /*
635                   ** The specified role is not granted to the 
636                   ** account -- nothing changed
637                   */
638                   raiserror 17929
639                   return (1)
640               end
641   
642               /*
643               ** perform the job
644               */
645   
646               if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
647                   begin tran rs_logexec
648   
649   
650   
651               if @option = "add default role"
652               begin
653                   update master.dbo.sysloginroles set
654                       status = status | @enable_login_role
655                   where suid = @suid and srid = role_id(@value)
656               end
657               else if @option = "drop default role"
658               begin
659                   update master.dbo.sysloginroles set
660                       status = status & ~ @enable_login_role
661                   where suid = @suid and srid = role_id(@value)
662               end
663   
664               select @rowcount_saved = @@rowcount
665           end
666           else
667           if ((@option = "passwd expiration") or (@option = "min passwd length") or
668                   (@option = "max failed_logins"))
669           begin
670               if (@option = "passwd expiration")
671                   select @attrib = 0
672               else
673               if (@option = "min passwd length")
674                   select @attrib = 1
675               else
676               if (@option = "max failed_logins")
677                   select @attrib = 2
678   
679               if exists (select 1 from master.dbo.sysattributes where
680                           class = @passeclass AND attribute = @attrib AND
681                           object = @suid AND object_cinfo = "login")
682                   select @action = 2
683               else
684                   select @action = 1
685   
686               if @value = "clear"
687               begin
688                   if @action = 1
689                   begin
690                       /* There is no login-specific attr for this user */
691                       exec sp_getmessage 19812, @msg output
692                       print @msg, @option
693                       return (1)
694                   end
695                   else
696                   begin
697                       /* Remove the login-specific setting for this attr */
698                       select @action = 0
699                       if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
700                           begin tran rs_logexec
701   
702   
703   
704                       delete from master.dbo.sysattributes
705                       where class = @passeclass
706                           AND attribute = @attrib
707                           AND object = @suid
708                           AND object_cinfo = "login"
709   
710                       select @rowcount_saved = @@rowcount
711                       select @deleted = 1
712                   end
713               end
714   
715               else if attrib_valid(@passeclass, @attrib, "PS", @suid, NULL, NULL,
716                       NULL, "login", @int_val, NULL, NULL, NULL,
717                       NULL, @action) = 0
718               begin
719                   /*
720                   ** 17932, "You entered an invalid value. No
721                   ** change was made."
722                   */
723                   raiserror 17932
724                   return (1)
725               end
726   
727               else
728               begin
729                   if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
730                       begin tran rs_logexec
731   
732   
733   
734                   if @action = 1
735                   begin
736                       insert into master.dbo.sysattributes(class, attribute,
737                           object_type, object, object_cinfo, int_value)
738                       values
739                       (@passeclass, @attrib, "PS", @suid, "login", @int_val)
740                   end
741                   else
742                   begin
743                       update master.dbo.sysattributes
744                       set int_value = @int_val
745                       where class = @passeclass AND attribute = @attrib AND
746                           object = @suid AND object_cinfo = "login"
747                   end
748   
749                   select @rowcount_saved = @@rowcount
750               end
751           end
752           else if @option like "auth%with"
753           begin
754               /*
755               ** Check whether authentication mechanism specified is valid or not.
756               ** 'AUTH_DEFAULT' and 'AUTH_MASK' are new values added in spt_values
757               ** used to obtain value of default ('ANY') authmech or authentication
758               ** mask respectively. They are not valid names that the user can specify
759               ** as authentication mechanism.
760               */
761               select @authid = low, @config = number
762               from master.dbo.spt_values
763               where type = 'ua' and name = upper(@value)
764                   and upper(name) not in ('AUTH_DEFAULT', 'AUTH_MASK')
765   
766               if @@rowcount = 0
767               begin
768                   /*
769                   ** 19257, "The authentication mechanism '%1!' is not valid."
770                   */
771                   raiserror 19257, @value
772                   return (1)
773               end
774   
775               /*
776               ** Check if the authentication method is enabled.
777               ** SMP or SDC, one row is expected.
778               */
779               if (@config != 0) and not exists (select 1
780                       from master.dbo.syscurconfigs a
781   
782                       where a.config = @config and a.value != 0)
783   
784   
785               begin
786                   /*
787                   ** 19259, "Warning. Authentication mechanism '%1!' is not enabled."
788                   */
789                   exec sp_getmessage 19259, @msg output
790                   print @msg, @value
791               end
792   
793               /*
794               **  Obtain any login mapping for this login that may conflict
795               **  with the new authentication mechanism being set.  The following
796               **  query looks for the mapping in sysattributes by suid and
797               **  by comparing authid bitmask from object_info1 with the parameter
798               **  authid bitmask value. If a row is found, then prevent modifylogin
799               **  action from proceeding, thus avoiding the mapping conflict
800               **  with the new login restrictions.
801               */
802               select @login_class = 20, @login_attrib = 0
803   
804               select @map_authid = object_info1 from master.dbo.sysattributes where
805                   class = @login_class and attribute = @login_attrib and
806                   object = @suid and ((object_info1 & @authid) = 0)
807               if @@rowcount > 0
808               begin
809                   /* Lookup the name for the authid value found. */
810                   select @map_authnm = name
811                   from master.dbo.spt_values
812                   where type = 'ua' and low = @map_authid
813   
814                   /*		
815                   ** 19448 "An existing login mapping for user '%1!' 
816                   ** allows only '%2!' authentication mechanism to 
817                   ** be used."
818                   */
819                   raiserror 19448, @loginame, @map_authnm
820                   return (1)
821               end
822   
823               /* Reset any auth mechanisms bits and set this one. */
824               select @allauth = low
825               from master.dbo.spt_values
826               where type = 'ua' and upper(name) = 'AUTH_MASK'
827   
828               if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
829                   begin tran rs_logexec
830   
831   
832   
833               /*
834               ** "ANY" is the name used for default authmech in sprocs, whereas the
835               **  value is obtained from AUTH_DEFAULT.
836               */
837               if (upper(@value) = 'ANY')
838               begin
839                   select @authid = low from master.dbo.spt_values
840                   where type = 'ua' and upper(name) = 'AUTH_DEFAULT'
841               end
842   
843               update master.dbo.syslogins
844               set status = @authid | (status & ~ @allauth)
845               where suid = @suid
846   
847               select @rowcount_saved = @@rowcount
848   
849   
850   
851               if (@log_for_rep = 1)
852               begin
853                   /*
854                   ** If the 'master' database is marked for replication, the
855                   ** T-SQL built-in 'logexec()' will log for replication the
856                   ** execution instance of this system procedure.  Otherwise,
857                   ** the T-SQL built-in 'logexec()' is a no-op.
858                   */
859                   if (logexec(1) != 1)
860                   begin
861                       /*
862                       ** 17756, "The execution of the stored procedure
863                       ** '%1!' in database '%2!' was aborted because
864                       ** there was an error in writing the replication
865                       ** log record."
866                       */
867                       raiserror 17756, "sp_modifylogin", "master"
868                       goto clean_all
869                   end
870               end
871   
872               if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
873                   commit tran rs_logexec
874           end
875           else /* error */
876           begin
877               /*
878               ** 17925, "You entered an invalid option name. No change was made."
879               */
880               raiserror 17925
881               return (1)
882           end
883   
884       end
885   
886       /*
887       **  Check @rowcount_saved when it works
888       */
889       if (@rowcount_saved >= 1)
890       begin
891   
892   
893   
894           if (@log_for_rep = 1)
895           begin
896               /*
897               ** If the 'master' database is marked for replication, the
898               ** T-SQL built-in 'logexec()' will log for replication the
899               ** execution instance of this system procedure.  Otherwise,
900               ** the T-SQL built-in 'logexec()' is a no-op.
901               */
902               if (logexec(1) != 1)
903               begin
904                   /*
905                   ** 17756, "The execution of the stored procedure
906                   ** '%1!' in database '%2!' was aborted because
907                   ** there was an error in writing the replication
908                   ** log record."
909                   */
910                   raiserror 17756, "sp_modifylogin", "master"
911                   goto clean_all
912               end
913           end
914   
915           if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
916               commit tran rs_logexec
917   
918           if (@deleted = 1)
919           begin
920               exec sp_getmessage 19813, @msg output
921               print @msg, @option
922           end
923           else
924           begin
925               /*
926               ** 17926, "Option changed."
927               */
928               exec sp_getmessage 17926, @msg output
929               print @msg
930           end
931           return (0)
932       end
933       else
934       begin
935           /*
936           ** 17927, "Error in changing the value of the specified column."
937           */
938           raiserror 17927
939           goto clean_all
940       end
941   
942   clean_all:
943       if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
944           rollback tran rs_logexec
945       return (1)
946   
947   


exec sp_procxmode 'sp_modifylogin', 'AnyMode'
go

Grant Execute on sp_modifylogin to public
go
DEFECTS
 MRIT 5 Return in Transaction trancount is 2 582
 MRIT 5 Return in Transaction trancount is 2 612
 MRIT 5 Return in Transaction trancount is 2 625
 MRIT 5 Return in Transaction trancount is 2 639
 MRIT 5 Return in Transaction trancount is 3 693
 MRIT 5 Return in Transaction trancount is 4 724
 MRIT 5 Return in Transaction trancount is 5 772
 MRIT 5 Return in Transaction trancount is 5 820
 MRIT 5 Return in Transaction trancount is 5 881
 MRIT 5 Return in Transaction trancount is 4 931
 MRIT 5 Return in Transaction trancount is 4 945
 MCTR 4 Conditional Begin Tran or Commit Tran 407
 MCTR 4 Conditional Begin Tran or Commit Tran 442
 MCTR 4 Conditional Begin Tran or Commit Tran 472
 MCTR 4 Conditional Begin Tran or Commit Tran 483
 MCTR 4 Conditional Begin Tran or Commit Tran 647
 MCTR 4 Conditional Begin Tran or Commit Tran 700
 MCTR 4 Conditional Begin Tran or Commit Tran 730
 MCTR 4 Conditional Begin Tran or Commit Tran 829
 MCTR 4 Conditional Begin Tran or Commit Tran 873
 MCTR 4 Conditional Begin Tran or Commit Tran 916
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch fullname: varchar(30) = varchar(255) 476
 MTYP 4 Assignment type mismatch @defdb: varchar(30) = varchar(255) 556
 MTYP 4 Assignment type mismatch @loginame: varchar(30) = varchar(255) 556
 MTYP 4 Assignment type mismatch @language: varchar(30) = varchar(255) 586
 MTYP 4 Assignment type mismatch @loginame: varchar(30) = varchar(255) 586
 MTYP 4 Assignment type mismatch attribute: smallint = int 739
 MTYP 4 Assignment type mismatch class: smallint = int 739
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
763
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
812
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
826
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
840
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 389
 QTYP 4 Comparison type mismatch smallint = int 389
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 412
 QTYP 4 Comparison type mismatch smallint = int 412
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 680
 QTYP 4 Comparison type mismatch smallint = int 680
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 705
 QTYP 4 Comparison type mismatch smallint = int 705
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 706
 QTYP 4 Comparison type mismatch smallint = int 706
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 745
 QTYP 4 Comparison type mismatch smallint = int 745
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 782
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 805
 QTYP 4 Comparison type mismatch smallint = int 805
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 TNOU 4 Table with no unique index master..sysloginroles master..sysloginroles
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 84
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public sybsystemprocs..sp_modifylogin  
 MNER 3 No Error Check should check return value of exec 144
 MNER 3 No Error Check should check @@error after delete 388
 MNER 3 No Error Check should check return value of exec 398
 MNER 3 No Error Check should check @@error after update 476
 MNER 3 No Error Check should check @@error after update 548
 MNER 3 No Error Check should check @@error after update 653
 MNER 3 No Error Check should check @@error after update 659
 MNER 3 No Error Check should check return value of exec 691
 MNER 3 No Error Check should check @@error after delete 704
 MNER 3 No Error Check should check @@error after insert 736
 MNER 3 No Error Check should check @@error after update 743
 MNER 3 No Error Check should check return value of exec 789
 MNER 3 No Error Check should check @@error after update 843
 MNER 3 No Error Check should check return value of exec 920
 MNER 3 No Error Check should check return value of exec 928
 MUBC 3 Unbalanced begin tran/commit tran 916
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 174
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 194
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 236
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 254
 MUCO 3 Useless Code Useless Brackets 267
 MUCO 3 Useless Code Useless Brackets 271
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 280
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 309
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 315
 MUCO 3 Useless Code Useless Brackets 319
 MUCO 3 Useless Code Useless Brackets 323
 MUCO 3 Useless Code Useless Brackets 325
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 346
 MUCO 3 Useless Code Useless Brackets 354
 MUCO 3 Useless Code Useless Brackets 365
 MUCO 3 Useless Code Useless Brackets 368
 MUCO 3 Useless Code Useless Brackets 371
 MUCO 3 Useless Code Useless Brackets 374
 MUCO 3 Useless Code Useless Brackets 382
 MUCO 3 Useless Code Useless Brackets 400
 MUCO 3 Useless Code Useless Brackets 406
 MUCO 3 Useless Code Useless Brackets 419
 MUCO 3 Useless Code Useless Brackets 428
 MUCO 3 Useless Code Useless Brackets 441
 MUCO 3 Useless Code Useless Brackets 455
 MUCO 3 Useless Code Useless Brackets 461
 MUCO 3 Useless Code Useless Brackets 471
 MUCO 3 Useless Code Useless Brackets 479
 MUCO 3 Useless Code Useless Brackets 482
 MUCO 3 Useless Code Useless Brackets 492
 MUCO 3 Useless Code Useless Brackets 504
 MUCO 3 Useless Code Useless Brackets 515
 MUCO 3 Useless Code Useless Brackets 527
 MUCO 3 Useless Code Useless Brackets 529
 MUCO 3 Useless Code Useless Brackets 534
 MUCO 3 Useless Code Useless Brackets 558
 MUCO 3 Useless Code Useless Brackets 560
 MUCO 3 Useless Code Useless Brackets 569
 MUCO 3 Useless Code Useless Brackets 582
 MUCO 3 Useless Code Useless Brackets 588
 MUCO 3 Useless Code Useless Brackets 590
 MUCO 3 Useless Code Useless Brackets 599
 MUCO 3 Useless Code Useless Brackets 612
 MUCO 3 Useless Code Useless Brackets 619
 MUCO 3 Useless Code Useless Brackets 625
 MUCO 3 Useless Code Useless Brackets 639
 MUCO 3 Useless Code Useless Brackets 646
 MUCO 3 Useless Code Useless Brackets 667
 MUCO 3 Useless Code Useless Brackets 670
 MUCO 3 Useless Code Useless Brackets 673
 MUCO 3 Useless Code Useless Brackets 676
 MUCO 3 Useless Code Useless Brackets 693
 MUCO 3 Useless Code Useless Brackets 699
 MUCO 3 Useless Code Useless Brackets 724
 MUCO 3 Useless Code Useless Brackets 729
 MUCO 3 Useless Code Useless Brackets 772
 MUCO 3 Useless Code Useless Brackets 820
 MUCO 3 Useless Code Useless Brackets 828
 MUCO 3 Useless Code Useless Brackets 837
 MUCO 3 Useless Code Useless Brackets 851
 MUCO 3 Useless Code Useless Brackets 859
 MUCO 3 Useless Code Useless Brackets 872
 MUCO 3 Useless Code Useless Brackets 881
 MUCO 3 Useless Code Useless Brackets 889
 MUCO 3 Useless Code Useless Brackets 894
 MUCO 3 Useless Code Useless Brackets 902
 MUCO 3 Useless Code Useless Brackets 915
 MUCO 3 Useless Code Useless Brackets 918
 MUCO 3 Useless Code Useless Brackets 931
 MUCO 3 Useless Code Useless Brackets 943
 MUCO 3 Useless Code Useless Brackets 945
 QAFM 3 Var Assignment from potentially many rows 761
 QAFM 3 Var Assignment from potentially many rows 804
 QAFM 3 Var Assignment from potentially many rows 810
 QAFM 3 Var Assignment from potentially many rows 824
 QAFM 3 Var Assignment from potentially many rows 839
 QISO 3 Set isolation level 181
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 736
 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, object_cinfo}
389
 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, object_cinfo}
412
 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}
680
 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}
705
 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}
745
 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_info1, object, attribute, class}
805
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 632
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 655
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) S 661
 QSWV 3 Sarg with variable @map_authid, Candidate Index: spt_values.spt_valuesclust clustered(number, type) U 812
 VNRD 3 Variable is not read @db_rep_level_all 136
 VNRD 3 Variable is not read @db_rep_level_l1 139
 VNRD 3 Variable is not read @dummy 326
 VNRD 3 Variable is not read @err1 415
 VUNU 3 Variable is not used @curdb 112
 VUNU 3 Variable is not used @sqltext 117
 MDYS 2 Dynamic SQL Marker 516
 MDYS 2 Dynamic SQL Marker 522
 MSUB 2 Subquery Marker 631
 MSUB 2 Subquery Marker 679
 MSUB 2 Subquery Marker 779
 MTR1 2 Metrics: Comments Ratio Comments: 48% 84
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 118 = 140dec - 24exi + 2 84
 MTR3 2 Metrics: Query Complexity Complexity: 458 84

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_logintrigger  
   calls proc sybsystemprocs..sp_ha_check_certified  
      reads table tempdb..sysobjects (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)  
   writes table tempdb..#tmp (1) 
   read_writes table master..sysattributes (1)  
   calls proc sybsystemprocs..sp_autoformat  
      calls proc sybsystemprocs..sp_namecrack  
      reads table master..syscolumns (1)  
      reads table tempdb..syscolumns (1)  
      calls proc sybsystemprocs..sp_autoformat  
      reads table tempdb..systypes (1)  
      reads table master..systypes (1)  
      read_writes table tempdb..#colinfo_af (1) 
calls proc sybsystemprocs..sp_defaultlanguage  
   calls proc sybsystemprocs..sp_ha_check_certified  
   read_writes table master..syslogins (1)  
   calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
read_writes table master..syslogins (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
read_writes table master..sysloginroles (1)  
reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_namecrack  
reads table master..spt_values (1)  
read_writes table master..sysattributes (1)  
calls proc sybsystemprocs..sp_getmessage  
calls proc sybsystemprocs..sp_defaultdb  
   calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_ha_check_certified  
   reads table master..sysdatabases (1)  
   read_writes table master..syslogins (1)