DatabaseProcApplicationCreatedLinks
sybsystemprocssp_serveroption  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%M% generic/sproc/%M% %I% %G%" */
3     /*	4.8	1.1	06/14/90	sproc/src/serveroption */
4     
5     /*
6     ** Messages for "sp_serveroption"       17800
7     **
8     ** 17260, "Can't run %1! from within a transaction." 
9     ** 17800, "No such server -- run sp_helpserver to list servers."
10    ** 17801, "Usage: sp_serveroption [server, {{security mechanism, mechname} | {server cost, value} | 
11    **	{optname, {true | false}}}]"
12    ** 17802, "Server option doesn't exist or can't be set by user."
13    ** 17803, "Run sp_serveroption with no parameters to see options."
14    ** 17804, "Server option is not unique."
15    ** 17806, "Option can be set for remote servers only -- not the local server."
16    ** 17807, "Settable server options."
17    ** 17809, "Confidentiality, integrity, and mutual authentication are \
18    **	   valid with rpc security model B only."
19    ** 17431, "true"
20    ** 17432, "false"
21    ** 18076, "Could not set curwrite to object level. Set your maxwrite label correctly."
22    ** 18686, "Server option 'server logins' cannot be set when @@servername is null"
23    ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on companion server '%2!'"
24    ** 18780, "Server '%1!' is the companion server, therefore, synchronization is d
25    isallowed."
26    ** 18782, "Unable to find a server with name '%1!' and id '%2!'."
27    ** 18890, "Only the 'external engine auto start' option can be enabled for the 
28    ** ejb class of servers."
29    ** 19255, "The option '%s' can be set for '%s' server class only." 
30    ** 19661, "Server '%1!' is not a cluster instance, can not set server option."
31    ** 19662, "Server name '%1!' doesn't match with its netname '%2!', can not set server option."
32    ** 19663, "Server '%1!' is a cluster instance, can not unset server option."
33    */
34    
35    /* 
36    ** IMPORTANT: Please read the following instructions before
37    **   making changes to this stored procedure.
38    **
39    **	To make this stored procedure compatible with High Availability (HA),
40    **	changes to certain system tables must be propagated 
41    **	to the companion server under some conditions.
42    **	The tables include (but are not limited to):
43    **		syslogins, sysservers, sysattributes, systimeranges,
44    **		sysresourcelimits, sysalternates, sysdatabases,
45    **		syslanguages, sysremotelogins, sysloginroles,
46    **		sysalternates (master DB only), systypes (master DB only),
47    **		sysusers (master DB only), sysprotects (master DB only)
48    **	please refer to the HA documentation for detail.
49    **
50    **	Here is what you need to do: 
51    **	For each insert/update/delete statement, add three sections to
52    **	-- start HA transaction prior to the statement
53    **	-- add the statement
54    **	-- add HA synchronization code to propagate the change to the companion
55    **
56    **	For example, if you are adding 
57    **		insert master.dbo.syslogins ......
58    **	the code should look like:
59    **	1. Before that SQL statement:
60    **		
61    **	2. Now, the SQL statement:
62    **		insert master.dbo.syslogins ......
63    **	3. Add a HA synchronization section right after the SQL statement:
64    **		
65    **
66    **	You may need to do similar change for each built-in function you
67    **	want to add.
68    **
69    **	Finally, add a separate part at a place where it can not
70    **	be reached by the normal execution path:
71    **	clean_all:
72    **		
73    **		return (1)
74    */
75    
76    create or replace procedure sp_serveroption
77        @server varchar(255) = NULL, /* server name to change */
78        @optname varchar(30) = NULL, /* option name to turn on/off */
79        @optvalue varchar(20) = NULL /* true or false */
80    as
81    
82        declare @srvid int /* id of the server */
83        declare @inst_id int /* cluster instance id */
84        declare @netname varchar(255) /* server net name */
85        declare @srvstatus int /* value of sysservers.srvstatus */
86        declare @srvstatus2 unsigned int /* value of sysservers.srvstatus2 */
87        declare @srvclass smallint /* class of the server */
88        declare @statvalue smallint /* number of option for srvstatus */
89        declare @statvalue2 unsigned int /* number of option for srvstatus2 */
90        declare @optcount int /* number of options like @optname */
91        declare @msg varchar(1024)
92        declare @true varchar(30)
93        declare @false varchar(30)
94        declare @status int
95        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
96        declare @retstat int
97        declare @rowlimit int
98        declare @outstr varchar(255), /* for SDC dbcc set_scope */
99            @scope varchar(16) /* for SDC dbcc set_scope */
100       declare @nullarg char(1)
101       declare @dummy int
102       declare @permstatus int
103       declare @permstatus1 int
104       declare @permstatus2 int
105       declare @gp_enabled int
106       declare @cha_area varchar(10) /* for configuration history auditing */
107       declare @cha_type varchar(30)
108       declare @cha_target varchar(30)
109       declare @cha_element varchar(255)
110       declare @cha_oldvalue varchar(255)
111       declare @cha_newvalue varchar(255)
112       declare @cha_mode varchar(10)
113       declare @cha_instanceid int
114       declare @cha_ret int
115       declare @cha_fulloptname varchar(255)
116       declare @cha_oldvalue_char varchar(255)
117       declare @cha_oldvalue_int int
118       declare @cha_newvalue_int int
119   
120   
121       select @permstatus = 1
122       select @permstatus1 = 1
123       select @permstatus2 = 1
124   
125       select @HA_CERTIFIED = 0
126   
127   
128       select @status = 0
129   
130   
131   
132       /* check to see if we are using HA specific SP for a HA enabled server */
133       exec @retstat = sp_ha_check_certified 'sp_serveroption', @HA_CERTIFIED
134       if (@retstat != 0)
135           return (1)
136   
137       /*
138       **  If we're in a transaction, disallow this since it might make recovery
139       **  impossible.
140       */
141       if @@trancount > 0
142       begin
143           /* 
144           ** 17260, "Can't run %1! from within a transaction." 
145           */
146           raiserror 17260, "sp_serveroption"
147           return (1)
148       end
149   
150       if @@trancount = 0
151       begin
152           set chained off
153       end
154   
155       set transaction isolation level 1
156   
157       /*
158       **  If no @server given, just list the possible server options.
159       **  Only certain status bits may be set or cleared.  
160       **  The default is to allow timeouts (bit 0 is clear) and to
161       **  perform no network password encryption (bit 1 is clear).
162       **	   settable                	    not settable
163       **      ------------------------------  --------------------------
164       **    0    timeouts
165       **    2    net password encryption
166       **    4    readonly
167       **    32   cis hafailover
168       **    64   use message confidentiality
169       **    128  use message integrity
170       **    256  mutual authentication
171       **    512  server logins
172       **    1024 external engine auto start
173       **    4096 negotiated logins
174       **	   security mechanism
175       **	   server cost
176       **	   server principal
177       **
178       **  Following server options are obsolete.
179       **    8    rpc security model A
180       **    16   rpc security model B
181       **
182       **  srvstatus2:
183       **
184       **	   settable                	    not settable
185       **      ------------------------------  --------------------------
186       **    1    relocated joins
187       **    2    enable login redirection	
188       **    4	   cluster instance
189       **    8    incompatible sort order
190       **
191       **
192       **  Notice that options "security mechanism", "server principal" and "server cost"
193       **  are not represented by a bit of srvstatus and therefore
194       **  are no corresponding entries in spt_values for them.
195       **  Select the options from spt_values unioned with these options
196       **  into a temp table for use in this procedure.
197       */
198       select @rowlimit = @@setrowcount
199       set rowcount 0
200   
201       /*
202       ** Server options "rpc security model A" (8) and
203       ** "rpc security model B" (16) are obsolete. 
204       */
205       select name into #server_options
206       from master.dbo.spt_values where
207           (type = "A" and number not in (- 1, 1, 8, 16)) or type = "A2"
208       union
209       select "server cost"
210       union
211       select "security mechanism"
212       union
213       select "server principal"
214   
215       set rowcount @rowlimit
216   
217       if @server is null
218       begin
219           /* 17807, "Settable server options." */
220           exec sp_getmessage 17807, @msg out
221           print @msg
222           select "" = name from #server_options order by 1
223           return (0)
224       end
225   
226       /*
227       **  Verify the server name and get the @srvid
228       */
229       select @srvid = srvid
230       from master.dbo.sysservers
231       where srvname = @server
232   
233       /*
234       **  If @server not found, say so. 
235       */
236       if @srvid is NULL
237       begin
238           /*
239           ** 17800, "No such server -- run sp_helpserver to list servers."
240           */
241           raiserror 17800
242           return (1)
243       end
244   
245       /*
246       ** Make sure that none of the option could be set
247       ** if the server class is HADR grou, to prevent
248       ** overwritting of srvstatus2 field, because this
249       ** is used to save suid of the hadr login.
250       */
251       if exists (select 1 from master.dbo.sysservers where
252                   srvname = @server and srvclass = 17)
253       begin
254           raiserror 17189, "sp_serveroption", "server class HADR group"
255           return (1)
256       end
257   
258       /*
259       ** Make sure only the 'external engine auto start option' can be used if the
260       ** class of servers belong to the ASEJB class
261       */
262       if exists (select 1 from master.dbo.sysservers where
263                   srvname = @server and srvclass = 10)
264       begin
265           if @optname not like "%external engine auto%"
266           begin
267               /*
268               ** 18890, "Only the 'external engine auto start' option can 
269               ** be enabled for the ejb class of servers."
270               */
271               raiserror 18890
272               return (1)
273           end
274       end
275       else
276       begin
277           /*
278           ** The server is not an EJB Server. 
279           ** The 'external engine auto start' option can only be set for EJB Servers. For all
280           ** other classes of servers, this is considered to be an invalid option.
281           */
282           if @optname like "%external engine auto%"
283           begin
284               /*
285               ** 19255, "The option '%s' can be set for '%s' server class only."
286               */
287               raiserror 19255, "external engine auto start", "EJB"
288               return (1)
289           end
290       end
291   
292   
293   
294       /*
295       **  Check remaining parameters.
296       **  Note that "security mechanism", "server principal" and "server cost" 
297       **  take a value instead of true or false
298       */
299       /* 17431, "true" */
300       exec sp_getmessage 17431, @true out
301       /* 17432, "false" */
302       exec sp_getmessage 17432, @false out
303       if @optname is NULL or
304           (@optname not like "%security me%" and @optname not like "%server c%"
305               and @optname not like "%server pri%"
306               and lower(@optvalue) not in
307               ("true", "false", @true, @false)) or
308           (@optname not like "%security me%" and @optname not like "%server pri%" and @optvalue is null)
309       begin
310           /*
311           ** 17801, "Usage: sp_serveroption [server, {{security mechanism, mechname} | 
312           **         {server cost, value} | {server principal, principalname} | {optname, {true | false}}}]"
313           */
314           raiserror 17801
315           return (1)
316       end
317   
318       /*
319       **  Use @optname and try to find the right option.
320       **  If there isn't just one, print appropriate diagnostics and return.
321       */
322       select @optcount = count(*)
323       from #server_options
324       where name like "%" + @optname + "%"
325   
326       /*
327       **  If no option, show the user what the options are.
328       */
329       if @optcount = 0
330       begin
331           /*
332           ** 17802, "Server option doesn't exist or can't be set by user."
333           */
334           raiserror 17802
335           /*
336           ** 17803, "Run sp_serveroption with no parameters to see options."
337           */
338           exec sp_getmessage 17803, @msg output
339           print @msg
340           return (1)
341       end
342   
343       /*
344       **  If more than one option like @optname, show the duplicates and return.
345       */
346       if @optcount > 1
347       begin
348           /*
349           ** 17804, "Server option is not unique."
350           */
351           raiserror 17804
352   
353           select duplicate_option = name
354           from #server_options
355           where name like "%" + @optname + "%"
356   
357           return (1)
358       end
359   
360       /*
361       **  Currently there are two options that can be set -- no timeouts and
362       **  net password encryption -- and both only apply to remote servers.  
363       **  If the current server is local then reject it.
364       */
365       if @srvid = 0
366       begin
367           /*
368           ** 17806, "Option can be set for remote servers only -- not the local server."
369           */
370           raiserror 17806
371           return (1)
372       end
373   
374   
375   
376       /* 
377       ** If granular permissions is not enabled then sa_role is required.
378       ** If granular permissions is enabled then the permission 'manage server' is
379       ** required.  proc_role and proc_auditperm will also do auditing
380       ** if required. Both will also print error message if required.
381       */
382   
383       select @nullarg = NULL
384       execute @permstatus = sp_aux_checkroleperm "sso_role", "manage server",
385           @nullarg, @gp_enabled output
386   
387       execute @permstatus1 = sp_aux_checkroleperm "sa_role", @nullarg,
388           @nullarg, @gp_enabled output
389   
390       if (@@clustermode = "shared disk cluster")
391       begin
392           execute @permstatus2 = sp_aux_checkroleperm @nullarg,
393               "manage cluster", @nullarg, @gp_enabled output
394       end
395   
396       /*
397       ** Get the full option name for configuration history auditing.
398       */
399       select @cha_fulloptname = name from #server_options
400       where name like "%" + @optname + "%"
401   
402       if @optname like "%security me%"
403       begin
404           if (@gp_enabled = 0)
405           begin
406               if (proc_role("sso_role") < 1)
407                   return (1)
408           end
409           else
410           begin
411               select @dummy = proc_auditperm("manage server", @permstatus)
412               if (@permstatus != 0)
413                   return (1)
414           end
415   
416           /*
417           ** Get the old value of "security mechanism" for configuration history
418           ** auditing.
419           */
420           select @cha_oldvalue_char = srvsecmech
421           from master.dbo.sysservers
422           where srvid = @srvid
423   
424           update master.dbo.sysservers
425           set srvsecmech = @optvalue
426           where srvid = @srvid
427   
428   
429           /*
430           ** Update the srvdes
431           ** For SDC, update cluster-wide in-memory SRVDES with data from
432           ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc
433           ** command scope needs to be set to cluster.
434           */
435           if (@@clustermode = "shared disk cluster")
436           begin
437               if (@gp_enabled = 1)
438               begin
439                   select @dummy = proc_auditperm("manage cluster",
440                           @permstatus2)
441                   if (@permstatus2 != 0)
442                       return (1)
443   
444               end
445               select @scope = NULL
446               select @outstr = "dbcc set_scope_in_cluster('scope')"
447               if (charindex("instance", @outstr) != 0)
448               begin
449                   /* save the scope to be restored later */
450                   select @scope = "instance"
451                   dbcc set_scope_in_cluster('cluster')
452               end
453           end
454   
455           dbcc cis("srvdes", @srvid)
456   
457           /* restore dbcc command scope */
458           if (@@clustermode = "shared disk cluster")
459           begin
460               if (@scope = "instance")
461               begin
462                   dbcc set_scope_in_cluster('instance')
463               end
464           end
465   
466           /*
467           ** record the serveroption change for configuration history auditing
468           */
469           select @cha_area = "SERVER"
470           select @cha_type = "sp_serveroption"
471           select @cha_target = @server
472           select @cha_element = @cha_fulloptname
473           select @cha_oldvalue = @cha_oldvalue_char
474           select @cha_newvalue = @optvalue
475           select @cha_mode = NULL
476           select @cha_instanceid = NULL
477   
478           if (@cha_oldvalue != @cha_newvalue)
479           begin
480               select @cha_ret = audit_config_history(
481                       @cha_area,
482                       @cha_type,
483                       @cha_target,
484                       @cha_element,
485                       @cha_oldvalue,
486                       @cha_newvalue,
487                       @cha_mode,
488                       @cha_instanceid)
489           end
490           return (0)
491       end
492   
493       /*
494       ** Update the server Kerberos principal name
495       */
496       if @optname = "server principal"
497       begin
498           if (@gp_enabled = 0)
499           begin
500               if (proc_role("sso_role") < 1)
501                   return (1)
502           end
503           else
504           begin
505               select @dummy = proc_auditperm("manage server", @permstatus)
506               if (@permstatus != 0)
507                   return (1)
508           end
509   
510           /*
511           ** Get the old value of "server principal" for configuration history
512           ** auditing.
513           */
514           select @cha_oldvalue_char = srvprincipal
515           from master.dbo.sysservers
516           where srvid = @srvid
517   
518           update master.dbo.sysservers
519           set srvprincipal = @optvalue
520           where srvid = @srvid
521   
522   
523   
524           /*
525           ** Update the srvdes
526           ** For SDC, update cluster-wide in-memory SRVDES with data from
527           ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc
528           ** command scope needs to be set to cluster.
529           */
530           if (@@clustermode = "shared disk cluster")
531           begin
532               if (@gp_enabled = 1)
533               begin
534                   select @dummy = proc_auditperm("manage cluster",
535                           @permstatus2)
536                   if (@permstatus2 != 0)
537                       return (1)
538   
539               end
540               select @scope = NULL
541               select @outstr = "dbcc set_scope_in_cluster('scope')"
542               if (charindex("instance", @outstr) != 0)
543               begin
544                   /* save the scope to be restored later */
545                   select @scope = "instance"
546                   dbcc set_scope_in_cluster('cluster')
547               end
548           end
549   
550           dbcc cis("srvdes", @srvid)
551   
552           /* restore dbcc command scope */
553           if (@@clustermode = "shared disk cluster")
554           begin
555               if (@scope = "instance")
556               begin
557                   dbcc set_scope_in_cluster('instance')
558               end
559           end
560   
561           /*
562           ** record the serveroption change for configuration history auditing
563           */
564           select @cha_area = "SERVER"
565           select @cha_type = "sp_serveroption"
566           select @cha_target = @server
567           select @cha_element = @cha_fulloptname
568           select @cha_oldvalue = @cha_oldvalue_char
569           select @cha_newvalue = @optvalue
570           select @cha_mode = NULL
571           select @cha_instanceid = NULL
572   
573           if (@cha_oldvalue != @cha_newvalue)
574           begin
575               select @cha_ret = audit_config_history(
576                       @cha_area,
577                       @cha_type,
578                       @cha_target,
579                       @cha_element,
580                       @cha_oldvalue,
581                       @cha_newvalue,
582                       @cha_mode,
583                       @cha_instanceid)
584           end
585           return (0)
586       end
587   
588       /*
589       ** Update the server cost
590       */
591       if @optname like "%server c%"
592       begin
593           /*
594           ** Changing the server cost requires SA role
595           */
596           if (@gp_enabled = 0)
597           begin
598               if (proc_role("sa_role") < 1)
599                   goto clean_all
600           end
601           else
602           begin
603               select @dummy = proc_auditperm("manage server", @permstatus)
604               if (@permstatus != 0)
605                   goto clean_all
606           end
607   
608           /*
609           ** Get the old value of "server cost" for configuration history
610           ** auditing.
611           */
612           select @cha_oldvalue_char = convert(varchar(255), srvcost)
613           from master.dbo.sysservers
614           where srvid = @srvid
615   
616           update master.dbo.sysservers
617           set srvcost = convert(smallint, @optvalue)
618           where srvid = @srvid
619   
620   
621   
622           /*
623           ** Update the srvdes
624           ** For SDC, update cluster-wide in-memory SRVDES with data from
625           ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc
626           ** command scope needs to be set to cluster.
627           */
628           if (@@clustermode = "shared disk cluster")
629           begin
630               if (@gp_enabled = 1)
631               begin
632                   select @dummy = proc_auditperm("manage cluster",
633                           @permstatus2)
634                   if (@permstatus2 != 0)
635                       return (1)
636   
637               end
638               select @scope = NULL
639               select @outstr = "dbcc set_scope_in_cluster('scope')"
640               if (charindex("instance", @outstr) != 0)
641               begin
642                   /* save the scope to be restored later */
643                   select @scope = "instance"
644                   dbcc set_scope_in_cluster('cluster')
645               end
646           end
647   
648           dbcc cis("srvdes", @srvid)
649   
650           /* restore dbcc command scope */
651           if (@@clustermode = "shared disk cluster")
652           begin
653               if (@scope = "instance")
654               begin
655                   dbcc set_scope_in_cluster('instance')
656               end
657           end
658   
659           /*
660           ** record the serveroption change for configuration history auditing
661           */
662           select @cha_area = "SERVER"
663           select @cha_type = "sp_serveroption"
664           select @cha_target = @server
665           select @cha_element = @cha_fulloptname
666           select @cha_oldvalue = @cha_oldvalue_char
667           select @cha_newvalue = @optvalue
668           select @cha_mode = NULL
669           select @cha_instanceid = NULL
670   
671           if (@cha_oldvalue != @cha_newvalue)
672           begin
673               select @cha_ret = audit_config_history(
674                       @cha_area,
675                       @cha_type,
676                       @cha_target,
677                       @cha_element,
678                       @cha_oldvalue,
679                       @cha_newvalue,
680                       @cha_mode,
681                       @cha_instanceid)
682           end
683           return (0)
684       end
685   
686       /*
687       **  Get the number which is the bit value to set in srvstatus2
688       */
689       select @statvalue2 = number
690       from master.dbo.spt_values
691       where name like "%" + @optname + "%" and type = "A2"
692           and number > 0
693   
694       /*
695       ** Update serveroption in srvstatus2 field
696       **
697       ** 	statvalue2	serveroption
698       **	---------	------------
699       **	   1		
700       **	   2		enable login redirection
701       **	   4		cluster instance
702       **
703       */
704       if (@statvalue2 in (2, 4))
705       begin
706           if (@gp_enabled = 0)
707           begin
708               if (proc_role("sa_role") < 1)
709                   goto clean_all
710           end
711           else
712           begin
713               select @dummy = proc_auditperm("manage server", @permstatus)
714               if (@permstatus != 0)
715                   goto clean_all
716           end
717   
718           select @srvstatus2 = isnull(srvstatus2, 0) from master.dbo.sysservers
719           where srvid = @srvid
720           /*
721           ** Get the old value of the option for configuration history auditing.
722           */
723           select @cha_oldvalue_int = @srvstatus2 & @statvalue2
724   
725           if lower(@optvalue) in ("true", @true)
726           begin
727               if (@statvalue2 = 4)
728               begin
729                   select @inst_id = instance_id(@server)
730                   if @inst_id is NULL
731                   begin
732                       /*
733                       ** 19661, "Server '%1!' is not a cluster instance, 
734                       ** can not set server option."
735                       */
736                       exec sp_getmessage 19661, @msg output
737                       print @msg, @server
738                       goto clean_all
739                   end
740                   select @netname = srvnetname from master.dbo.sysservers
741                   where srvid = @srvid
742                   if (@server != @netname)
743                   begin
744                       /*
745                       ** 19662, "Server name '%1!' doesn't match with its netname '%2!', 
746                       ** can not set server option."
747                       */
748                       exec sp_getmessage 19662, @msg output
749                       print @msg, @server, @netname
750                       goto clean_all
751                   end
752               end
753               update master.dbo.sysservers
754               set srvstatus2 = @srvstatus2 | @statvalue2
755               where srvid = @srvid
756               /*
757               ** Get the new value of the option for configuration history
758               ** auditing.
759               */
760               select @cha_newvalue_int = @statvalue2
761   
762           end
763           else
764           begin
765               if (@statvalue2 = 4)
766               begin
767                   select @inst_id = instance_id(@server)
768                   if @inst_id is not NULL
769                   begin
770                       /*
771                       ** 19663, "Server '%1!' is a cluster instance, 
772                       ** can not unset server option."
773                       */
774                       exec sp_getmessage 19663, @msg output
775                       print @msg, @server
776                       goto clean_all
777                   end
778               end
779               update master.dbo.sysservers
780               set srvstatus2 = @srvstatus2 & ~ @statvalue2
781               where srvid = @srvid
782               /*
783               ** Get the new value of the option for configuration history
784               ** auditing.
785               */
786               select @cha_newvalue_int = 0
787   
788           end
789   
790           /*
791           ** Update the srvdes
792           ** For SDC, update cluster-wide in-memory SRVDES with data from
793           ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc
794           ** command scope needs to be set to cluster.
795           */
796           if (@@clustermode = "shared disk cluster")
797           begin
798               if (@gp_enabled = 1)
799               begin
800                   select @dummy = proc_auditperm("manage cluster",
801                           @permstatus2)
802                   if (@permstatus2 != 0)
803                       return (1)
804   
805               end
806               select @scope = NULL
807               select @outstr = "dbcc set_scope_in_cluster('scope')"
808               if (charindex("instance", @outstr) != 0)
809               begin
810                   /* save the scope to be restored later */
811                   select @scope = "instance"
812                   dbcc set_scope_in_cluster('cluster')
813               end
814           end
815   
816           dbcc cis("srvdes", @srvid)
817   
818           /* restore dbcc command scope */
819           if (@@clustermode = "shared disk cluster")
820           begin
821               if (@scope = "instance")
822               begin
823                   dbcc set_scope_in_cluster('instance')
824               end
825           end
826   
827           /*
828           ** record the serveroption change for configuration history auditing
829           */
830           select @cha_area = "SERVER"
831           select @cha_type = "sp_serveroption"
832           select @cha_target = @server
833           select @cha_element = @cha_fulloptname
834           select @cha_oldvalue = case when @cha_oldvalue_int = 0
835                   then "false" else "true" end
836           select @cha_newvalue = case when @cha_newvalue_int = 0
837                   then "false" else "true" end
838           select @cha_mode = NULL
839           select @cha_instanceid = NULL
840   
841           if (@cha_oldvalue != @cha_newvalue)
842           begin
843               select @cha_ret = audit_config_history(
844                       @cha_area,
845                       @cha_type,
846                       @cha_target,
847                       @cha_element,
848                       @cha_oldvalue,
849                       @cha_newvalue,
850                       @cha_mode,
851                       @cha_instanceid)
852           end
853           return (0)
854       end
855   
856       /*
857       ** Update srvstatus2 field values
858       **    1    relocated joins 
859       **    8    incompatible sort order
860       */
861       if (@optname like "%rel%" or @optname like "%inc%")
862       begin
863           /*
864           **  Get the number which is the bit value to set
865           */
866           select @statvalue2 = number
867           from master.dbo.spt_values
868           where name like "%" + @optname + "%" and type = "A2"
869               and number > 0
870           /*
871           ** Changing 'relocated joins' or 'incompatible sort order' requires SA role
872           */
873           if (@gp_enabled = 0)
874           begin
875               if (proc_role("sa_role") < 1)
876                   goto clean_all
877           end
878           else
879           begin
880               select @dummy = proc_auditperm("manage server", @permstatus)
881               if (@permstatus != 0)
882                   goto clean_all
883           end
884   
885           select @srvstatus2 = isnull(srvstatus2, 0) from master.dbo.sysservers
886           where srvid = @srvid
887   
888           /*
889           ** Get the old value of the option for configuration history auditing.
890           */
891           select @cha_oldvalue_int = @srvstatus2 & @statvalue2
892   
893           if lower(@optvalue) in ("true", @true)
894           begin
895               update master.dbo.sysservers
896               set srvstatus2 = @srvstatus2 | @statvalue2
897               where srvid = @srvid
898               /*
899               ** Get the new value of the option for configuration history
900               ** auditing.
901               */
902               select @cha_newvalue_int = @statvalue2
903   
904           end
905           else
906           begin
907               update master.dbo.sysservers
908               set srvstatus2 = @srvstatus2 & ~ @statvalue2
909               where srvid = @srvid
910               /*
911               ** Get the new value of the option for configuration history
912               ** auditing.
913               */
914               select @cha_newvalue_int = 0
915   
916           end
917   
918           /*
919           ** Update the srvdes
920           */
921           dbcc cis("srvdes", @srvid)
922   
923           /*
924           ** record the serveroption change for configuration history auditing
925           */
926           select @cha_area = "SERVER"
927           select @cha_type = "sp_serveroption"
928           select @cha_target = @server
929           select @cha_element = @cha_fulloptname
930           select @cha_oldvalue = case when @cha_oldvalue_int = 0
931                   then "false" else "true" end
932           select @cha_newvalue = case when @cha_newvalue_int = 0
933                   then "false" else "true" end
934           select @cha_mode = NULL
935           select @cha_instanceid = NULL
936   
937           if (@cha_oldvalue != @cha_newvalue)
938           begin
939               select @cha_ret = audit_config_history(
940                       @cha_area,
941                       @cha_type,
942                       @cha_target,
943                       @cha_element,
944                       @cha_oldvalue,
945                       @cha_newvalue,
946                       @cha_mode,
947                       @cha_instanceid)
948           end
949           return (0)
950       end
951   
952   
953       /*
954       **  Get the number which is the bit value to set
955       */
956       select @statvalue = number
957       from master.dbo.spt_values
958       where name like "%" + @optname + "%" and type = "A"
959           and number > 0
960   
961       /*
962       ** OMNI: If we're setting server logins, dissallow
963       ** when @@servername is null
964       */
965       if @statvalue = 512 and @@servername is NULL
966       begin
967           /*
968           ** 18686, "Server option 'server logins' cannot be set 
969           **	when @@servername is null"
970           */
971           exec sp_getmessage 18686, @msg output
972           print @msg
973           goto clean_all
974       end
975   
976       /* 
977       ** Check for SSO options:
978       **
979       ** value       : 2    net password encryption
980       ** value       : 64   use message confidentiality
981       ** value       : 128  use message integrity
982       ** value       : 256  mutual authentication
983       ** value       : 512  server logins
984       ** value       : 4096 negotiated logins
985       **
986       ** Following server options are obsolete.
987       ** value       : 8    rpc security model A
988       ** value       : 16   rpc security model B
989       **
990       */
991       if (@statvalue in (2, 64, 128, 256, 512, 4096))
992       begin
993           if (@gp_enabled = 0)
994           begin
995               if (proc_role("sso_role") < 1)
996                   goto clean_all
997           end
998           else
999           begin
1000              select @dummy = proc_auditperm("manage server", @permstatus)
1001              if (@permstatus != 0)
1002                  goto clean_all
1003          end
1004      end
1005  
1006  
1007      /*
1008      ** Check for HA options 
1009      **
1010      ** value       : 32   cis hafailover
1011      */
1012      else if (@statvalue in (32))
1013      begin
1014          if (proc_role("ha_role") < 1)
1015              goto clean_all
1016      end
1017  
1018      /*
1019      ** For any other option check for SA role
1020      ** 
1021      ** Currently the following are left
1022      **
1023      ** value       : 0    timeouts
1024      ** value       : 4    readonly
1025      ** value       : 1024 external engine auto start
1026      */
1027      else
1028      begin
1029          if (@gp_enabled = 0)
1030          begin
1031              if (proc_role("sa_role") < 1)
1032                  goto clean_all
1033          end
1034          else
1035          begin
1036              select @dummy = proc_auditperm("manage server", @permstatus)
1037              if (@permstatus != 0)
1038                  goto clean_all
1039          end
1040      end
1041  
1042      select @srvstatus = srvstatus from master.dbo.sysservers
1043      where srvid = @srvid
1044      /*
1045      ** Get the old value of the option for configuration history auditing.
1046      */
1047      select @cha_oldvalue_int = isnull(@srvstatus, 0) & @statvalue
1048  
1049      /*
1050      **  Now update sysservers.
1051      **  The timeouts option is handled a little strangely since the default
1052      **  is timeouts.  Therefore timeouts = true means to clear the bit
1053      **  and timeouts = false means to set the bit.
1054      */
1055  
1056      if lower(@optvalue) in ("true", @true)
1057      begin
1058          if (@statvalue = 1)
1059          begin
1060              update master.dbo.sysservers
1061              set srvstatus = srvstatus & ~ @statvalue
1062              where srvid = @srvid
1063  
1064          end
1065          else
1066          begin
1067              update master.dbo.sysservers
1068              set srvstatus = srvstatus | @statvalue
1069              where srvid = @srvid
1070  
1071          end
1072  
1073          /*
1074          ** Get the new value of the option for configuration history auditing.
1075          */
1076          select @cha_newvalue_int = @statvalue
1077      end
1078  
1079      /*
1080      **  We want to turn it off.
1081      */
1082      else
1083      begin
1084          if @statvalue = 1
1085          begin
1086              update master.dbo.sysservers
1087              set srvstatus = srvstatus | @statvalue
1088              where srvid = @srvid
1089  
1090          end
1091          else
1092          begin
1093              update master.dbo.sysservers
1094              set srvstatus = srvstatus & ~ @statvalue
1095              where srvid = @srvid
1096  
1097          end
1098  
1099          /*
1100          ** Get the new value of the option for configuration history auditing.
1101          */
1102          select @cha_newvalue_int = 0
1103      end
1104  
1105  
1106  
1107  
1108      /*
1109      ** If use message confidentiality, use message integrity or 
1110      ** mutual authentication, update the srvdes.
1111      ** OMNI: If net password encryption, readonly, hafailover, server login or
1112      ** negotiated logins option is specified, update the srvdes
1113      */
1114      if @statvalue in (2, 4, 32, 64, 128, 256, 512, 4096)
1115      begin
1116          /*
1117          ** For SDC, update cluster-wide in-memory SRVDES with data from
1118          ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc
1119          ** command scope needs to be set to cluster.
1120          */
1121          if (@@clustermode = "shared disk cluster")
1122          begin
1123              if (@gp_enabled = 1)
1124              begin
1125                  select @dummy = proc_auditperm("manage cluster",
1126                          @permstatus2)
1127                  if (@permstatus2 != 0)
1128                      return (1)
1129  
1130              end
1131              select @scope = NULL
1132              select @outstr = "dbcc set_scope_in_cluster('scope')"
1133              if (charindex("instance", @outstr) != 0)
1134              begin
1135                  /* save the scope to be restored later */
1136                  select @scope = "instance"
1137                  dbcc set_scope_in_cluster('cluster')
1138              end
1139          end
1140  
1141          dbcc cis("srvdes", @srvid)
1142  
1143          /* restore dbcc command scope */
1144          if (@@clustermode = "shared disk cluster")
1145          begin
1146              if (@scope = "instance")
1147              begin
1148                  dbcc set_scope_in_cluster('instance')
1149              end
1150          end
1151      end
1152  
1153      /*
1154      ** record the serveroption change for configuration history auditing
1155      */
1156      select @cha_area = "SERVER"
1157      select @cha_type = "sp_serveroption"
1158      select @cha_target = @server
1159      select @cha_element = @cha_fulloptname
1160      select @cha_oldvalue = case when @cha_oldvalue_int = 0
1161              then "false" else "true" end
1162      select @cha_newvalue = case when @cha_newvalue_int = 0
1163              then "false" else "true" end
1164      select @cha_mode = NULL
1165      select @cha_instanceid = NULL
1166  
1167      if (@cha_oldvalue != @cha_newvalue)
1168      begin
1169          select @cha_ret = audit_config_history(
1170                  @cha_area,
1171                  @cha_type,
1172                  @cha_target,
1173                  @cha_element,
1174                  @cha_oldvalue,
1175                  @cha_newvalue,
1176                  @cha_mode,
1177                  @cha_instanceid)
1178      end
1179      return (0)
1180  
1181  clean_all:
1182  
1183  
1184  
1185      return (1)
1186  


exec sp_procxmode 'sp_serveroption', 'AnyMode'
go

Grant Execute on sp_serveroption to public
go
RESULT SETS
sp_serveroption_rset_002
sp_serveroption_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 222
 MTYP 4 Assignment type mismatch @cha_target: varchar(30) = varchar(255) 471
 MTYP 4 Assignment type mismatch @cha_target: varchar(30) = varchar(255) 566
 MTYP 4 Assignment type mismatch @cha_target: varchar(30) = varchar(255) 664
 MTYP 4 Assignment type mismatch @cha_target: varchar(30) = varchar(255) 832
 MTYP 4 Assignment type mismatch @cha_target: varchar(30) = varchar(255) 928
 MTYP 4 Assignment type mismatch @statvalue: smallint = int 956
 MTYP 4 Assignment type mismatch @cha_target: varchar(30) = varchar(255) 1158
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 252
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 263
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 422
 QTYP 4 Comparison type mismatch smallint = int 422
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 426
 QTYP 4 Comparison type mismatch smallint = int 426
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 516
 QTYP 4 Comparison type mismatch smallint = int 516
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 520
 QTYP 4 Comparison type mismatch smallint = int 520
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 614
 QTYP 4 Comparison type mismatch smallint = int 614
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 618
 QTYP 4 Comparison type mismatch smallint = int 618
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 719
 QTYP 4 Comparison type mismatch smallint = int 719
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 741
 QTYP 4 Comparison type mismatch smallint = int 741
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 755
 QTYP 4 Comparison type mismatch smallint = int 755
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 781
 QTYP 4 Comparison type mismatch smallint = int 781
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 886
 QTYP 4 Comparison type mismatch smallint = int 886
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 897
 QTYP 4 Comparison type mismatch smallint = int 897
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 909
 QTYP 4 Comparison type mismatch smallint = int 909
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1043
 QTYP 4 Comparison type mismatch smallint = int 1043
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1062
 QTYP 4 Comparison type mismatch smallint = int 1062
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1069
 QTYP 4 Comparison type mismatch smallint = int 1069
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1088
 QTYP 4 Comparison type mismatch smallint = int 1088
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1095
 QTYP 4 Comparison type mismatch smallint = int 1095
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_serveroption  
 MNER 3 No Error Check should check @@error after select into 205
 MNER 3 No Error Check should check return value of exec 220
 MNER 3 No Error Check should check return value of exec 300
 MNER 3 No Error Check should check return value of exec 302
 MNER 3 No Error Check should check return value of exec 338
 MNER 3 No Error Check should check return value of exec 384
 MNER 3 No Error Check should check return value of exec 387
 MNER 3 No Error Check should check return value of exec 392
 MNER 3 No Error Check should check @@error after update 424
 MNER 3 No Error Check should check @@error after update 518
 MNER 3 No Error Check should check @@error after update 616
 MNER 3 No Error Check should check return value of exec 736
 MNER 3 No Error Check should check return value of exec 748
 MNER 3 No Error Check should check @@error after update 753
 MNER 3 No Error Check should check return value of exec 774
 MNER 3 No Error Check should check @@error after update 779
 MNER 3 No Error Check should check @@error after update 895
 MNER 3 No Error Check should check @@error after update 907
 MNER 3 No Error Check should check return value of exec 971
 MNER 3 No Error Check should check @@error after update 1060
 MNER 3 No Error Check should check @@error after update 1067
 MNER 3 No Error Check should check @@error after update 1086
 MNER 3 No Error Check should check @@error after update 1093
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 315
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 357
 MUCO 3 Useless Code Useless Brackets 371
 MUCO 3 Useless Code Useless Brackets 390
 MUCO 3 Useless Code Useless Brackets 404
 MUCO 3 Useless Code Useless Brackets 406
 MUCO 3 Useless Code Useless Brackets 407
 MUCO 3 Useless Code Useless Brackets 412
 MUCO 3 Useless Code Useless Brackets 413
 MUCO 3 Useless Code Useless Brackets 435
 MUCO 3 Useless Code Useless Brackets 437
 MUCO 3 Useless Code Useless Brackets 441
 MUCO 3 Useless Code Useless Brackets 442
 MUCO 3 Useless Code Useless Brackets 447
 MUCO 3 Useless Code Useless Brackets 458
 MUCO 3 Useless Code Useless Brackets 460
 MUCO 3 Useless Code Useless Brackets 478
 MUCO 3 Useless Code Useless Brackets 490
 MUCO 3 Useless Code Useless Brackets 498
 MUCO 3 Useless Code Useless Brackets 500
 MUCO 3 Useless Code Useless Brackets 501
 MUCO 3 Useless Code Useless Brackets 506
 MUCO 3 Useless Code Useless Brackets 507
 MUCO 3 Useless Code Useless Brackets 530
 MUCO 3 Useless Code Useless Brackets 532
 MUCO 3 Useless Code Useless Brackets 536
 MUCO 3 Useless Code Useless Brackets 537
 MUCO 3 Useless Code Useless Brackets 542
 MUCO 3 Useless Code Useless Brackets 553
 MUCO 3 Useless Code Useless Brackets 555
 MUCO 3 Useless Code Useless Brackets 573
 MUCO 3 Useless Code Useless Brackets 585
 MUCO 3 Useless Code Useless Brackets 596
 MUCO 3 Useless Code Useless Brackets 598
 MUCO 3 Useless Code Useless Brackets 604
 MUCO 3 Useless Code Useless Brackets 628
 MUCO 3 Useless Code Useless Brackets 630
 MUCO 3 Useless Code Useless Brackets 634
 MUCO 3 Useless Code Useless Brackets 635
 MUCO 3 Useless Code Useless Brackets 640
 MUCO 3 Useless Code Useless Brackets 651
 MUCO 3 Useless Code Useless Brackets 653
 MUCO 3 Useless Code Useless Brackets 671
 MUCO 3 Useless Code Useless Brackets 683
 MUCO 3 Useless Code Useless Brackets 704
 MUCO 3 Useless Code Useless Brackets 706
 MUCO 3 Useless Code Useless Brackets 708
 MUCO 3 Useless Code Useless Brackets 714
 MUCO 3 Useless Code Useless Brackets 727
 MUCO 3 Useless Code Useless Brackets 742
 MUCO 3 Useless Code Useless Brackets 765
 MUCO 3 Useless Code Useless Brackets 796
 MUCO 3 Useless Code Useless Brackets 798
 MUCO 3 Useless Code Useless Brackets 802
 MUCO 3 Useless Code Useless Brackets 803
 MUCO 3 Useless Code Useless Brackets 808
 MUCO 3 Useless Code Useless Brackets 819
 MUCO 3 Useless Code Useless Brackets 821
 MUCO 3 Useless Code Useless Brackets 841
 MUCO 3 Useless Code Useless Brackets 853
 MUCO 3 Useless Code Useless Brackets 861
 MUCO 3 Useless Code Useless Brackets 873
 MUCO 3 Useless Code Useless Brackets 875
 MUCO 3 Useless Code Useless Brackets 881
 MUCO 3 Useless Code Useless Brackets 937
 MUCO 3 Useless Code Useless Brackets 949
 MUCO 3 Useless Code Useless Brackets 991
 MUCO 3 Useless Code Useless Brackets 993
 MUCO 3 Useless Code Useless Brackets 995
 MUCO 3 Useless Code Useless Brackets 1001
 MUCO 3 Useless Code Useless Brackets 1012
 MUCO 3 Useless Code Useless Brackets 1014
 MUCO 3 Useless Code Useless Brackets 1029
 MUCO 3 Useless Code Useless Brackets 1031
 MUCO 3 Useless Code Useless Brackets 1037
 MUCO 3 Useless Code Useless Brackets 1058
 MUCO 3 Useless Code Useless Brackets 1121
 MUCO 3 Useless Code Useless Brackets 1123
 MUCO 3 Useless Code Useless Brackets 1127
 MUCO 3 Useless Code Useless Brackets 1128
 MUCO 3 Useless Code Useless Brackets 1133
 MUCO 3 Useless Code Useless Brackets 1144
 MUCO 3 Useless Code Useless Brackets 1146
 MUCO 3 Useless Code Useless Brackets 1167
 MUCO 3 Useless Code Useless Brackets 1179
 MUCO 3 Useless Code Useless Brackets 1185
 MUOT 3 Updates outside transaction 1093
 QAFM 3 Var Assignment from potentially many rows 399
 QAFM 3 Var Assignment from potentially many rows 689
 QAFM 3 Var Assignment from potentially many rows 866
 QAFM 3 Var Assignment from potentially many rows 956
 QCRS 3 Conditional Result Set 222
 QCRS 3 Conditional Result Set 353
 QGWO 3 Group by/Distinct/Union without order by 205
 QISO 3 Set isolation level 155
 QTLO 3 Top-Level OR 207
 QUNI 3 Check Use of 'union' vs 'union all' 205
 VNRD 3 Variable is not read @status 128
 VNRD 3 Variable is not read @permstatus1 387
 VNRD 3 Variable is not read @dummy 1125
 VNRD 3 Variable is not read @cha_ret 1169
 VUNU 3 Variable is not used @srvclass 87
 MRST 2 Result Set Marker 222
 MRST 2 Result Set Marker 353
 MSUB 2 Subquery Marker 251
 MSUB 2 Subquery Marker 262
 MTR1 2 Metrics: Comments Ratio Comments: 43% 76
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 88 = 111dec - 25exi + 2 76
 MTR3 2 Metrics: Query Complexity Complexity: 518 76

DATA PROPAGATION detailed
ColumnWritten To
@optvaluesysservers.srvsecmech   °.srvcost   °.srvprincipal  

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
reads table master..spt_values (1)  
read_writes table master..sysservers (1)  
read_writes table tempdb..#server_options (1) 
writes table sybsystemprocs..sp_serveroption_rset_002 
writes table sybsystemprocs..sp_serveroption_rset_001 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)