| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_serveroption | | 31 Aug 14 | Defects 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 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 101 select @HA_CERTIFIED = 0 102 103 104 select @status = 0 105 106 107 108 /* check to see if we are using HA specific SP for a HA enabled server */ 109 exec @retstat = sp_ha_check_certified 'sp_serveroption', @HA_CERTIFIED 110 if (@retstat != 0) 111 return (1) 112 113 /* 114 ** If we're in a transaction, disallow this since it might make recovery 115 ** impossible. 116 */ 117 if @@trancount > 0 118 begin 119 /* 120 ** 17260, "Can't run %1! from within a transaction." 121 */ 122 raiserror 17260, "sp_serveroption" 123 return (1) 124 end 125 126 if @@trancount = 0 127 begin 128 set chained off 129 end 130 131 set transaction isolation level 1 132 133 /* 134 ** If no @server given, just list the possible server options. 135 ** Only certain status bits may be set or cleared. 136 ** The default is to allow timeouts (bit 0 is clear) and to 137 ** perform no network password encryption (bit 1 is clear). 138 ** settable not settable 139 ** ------------------------------ -------------------------- 140 ** 0 timeouts 141 ** 2 net password encryption 142 ** 4 readonly 143 ** 32 cis hafailover 144 ** 64 use message confidentiality 145 ** 128 use message integrity 146 ** 256 mutual authentication 147 ** 512 server logins 148 ** 1024 external engine auto start 149 ** 4096 negotiated logins 150 ** security mechanism 151 ** server cost 152 ** server principal 153 ** 154 ** Following server options are obsolete. 155 ** 8 rpc security model A 156 ** 16 rpc security model B 157 ** 158 ** srvstatus2: 159 ** 160 ** settable not settable 161 ** ------------------------------ -------------------------- 162 ** 1 relocated joins 163 ** 2 enable login redirection 164 ** 4 cluster instance 165 ** 8 incompatible sort order 166 ** 167 ** 168 ** Notice that options "security mechanism", "server principal" and "server cost" 169 ** are not represented by a bit of srvstatus and therefore 170 ** are no corresponding entries in spt_values for them. 171 ** Select the options from spt_values unioned with these options 172 ** into a temp table for use in this procedure. 173 */ 174 select @rowlimit = @@setrowcount 175 set rowcount 0 176 177 /* 178 ** Server options "rpc security model A" (8) and 179 ** "rpc security model B" (16) are obsolete. 180 */ 181 select name into #server_options 182 from master.dbo.spt_values where 183 (type = "A" and number not in (- 1, 1, 8, 16)) or type = "A2" 184 union 185 select "server cost" 186 union 187 select "security mechanism" 188 union 189 select "server principal" 190 191 set rowcount @rowlimit 192 193 if @server is null 194 begin 195 /* 17807, "Settable server options." */ 196 exec sp_getmessage 17807, @msg out 197 print @msg 198 select "" = name from #server_options order by 1 199 return (0) 200 end 201 202 /* 203 ** Verify the server name and get the @srvid 204 */ 205 select @srvid = srvid 206 from master.dbo.sysservers 207 where srvname = @server 208 209 /* 210 ** If @server not found, say so. 211 */ 212 if @srvid is NULL 213 begin 214 /* 215 ** 17800, "No such server -- run sp_helpserver to list servers." 216 */ 217 raiserror 17800 218 return (1) 219 end 220 221 /* 222 ** Make sure only the 'external engine auto start option' can be used if the 223 ** class of servers belong to the ASEJB class 224 */ 225 if exists (select 1 from master.dbo.sysservers where 226 srvname = @server and srvclass = 10) 227 begin 228 if @optname not like "%external engine auto%" 229 begin 230 /* 231 ** 18890, "Only the 'external engine auto start' option can 232 ** be enabled for the ejb class of servers." 233 */ 234 raiserror 18890 235 return (1) 236 end 237 end 238 else 239 begin 240 /* 241 ** The server is not an EJB Server. 242 ** The 'external engine auto start' option can only be set for EJB Servers. For all 243 ** other classes of servers, this is considered to be an invalid option. 244 */ 245 if @optname like "%external engine auto%" 246 begin 247 /* 248 ** 19255, "The option '%s' can be set for '%s' server class only." 249 */ 250 raiserror 19255, "external engine auto start", "EJB" 251 return (1) 252 end 253 end 254 255 256 257 /* 258 ** Check remaining parameters. 259 ** Note that "security mechanism", "server principal" and "server cost" 260 ** take a value instead of true or false 261 */ 262 /* 17431, "true" */ 263 exec sp_getmessage 17431, @true out 264 /* 17432, "false" */ 265 exec sp_getmessage 17432, @false out 266 if @optname is NULL or 267 (@optname not like "%security me%" and @optname not like "%server c%" 268 and @optname not like "%server pri%" 269 and lower(@optvalue) not in 270 ("true", "false", @true, @false)) or 271 (@optname not like "%security me%" and @optname not like "%server pri%" and @optvalue is null) 272 begin 273 /* 274 ** 17801, "Usage: sp_serveroption [server, {{security mechanism, mechname} | 275 ** {server cost, value} | {server principal, principalname} | {optname, {true | false}}}]" 276 */ 277 raiserror 17801 278 return (1) 279 end 280 281 /* 282 ** Use @optname and try to find the right option. 283 ** If there isn't just one, print appropriate diagnostics and return. 284 */ 285 select @optcount = count(*) 286 from #server_options 287 where name like "%" + @optname + "%" 288 289 /* 290 ** If no option, show the user what the options are. 291 */ 292 if @optcount = 0 293 begin 294 /* 295 ** 17802, "Server option doesn't exist or can't be set by user." 296 */ 297 raiserror 17802 298 /* 299 ** 17803, "Run sp_serveroption with no parameters to see options." 300 */ 301 exec sp_getmessage 17803, @msg output 302 print @msg 303 return (1) 304 end 305 306 /* 307 ** If more than one option like @optname, show the duplicates and return. 308 */ 309 if @optcount > 1 310 begin 311 /* 312 ** 17804, "Server option is not unique." 313 */ 314 raiserror 17804 315 316 select duplicate_option = name 317 from #server_options 318 where name like "%" + @optname + "%" 319 320 return (1) 321 end 322 323 /* 324 ** Currently there are two options that can be set -- no timeouts and 325 ** net password encryption -- and both only apply to remote servers. 326 ** If the current server is local then reject it. 327 */ 328 if @srvid = 0 329 begin 330 /* 331 ** 17806, "Option can be set for remote servers only -- not the local server." 332 */ 333 raiserror 17806 334 return (1) 335 end 336 337 338 339 if @optname like "%security me%" 340 begin 341 if (proc_role("sso_role") < 1) 342 return (1) 343 344 update master.dbo.sysservers 345 set srvsecmech = @optvalue 346 where srvid = @srvid 347 348 349 /* 350 ** Update the srvdes 351 ** For SDC, update cluster-wide in-memory SRVDES with data from 352 ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc 353 ** command scope needs to be set to cluster. 354 */ 355 if (@@clustermode = "shared disk cluster") 356 begin 357 select @scope = NULL 358 select @outstr = "dbcc set_scope_in_cluster('scope')" 359 if (charindex("instance", @outstr) != 0) 360 begin 361 /* save the scope to be restored later */ 362 select @scope = "instance" 363 dbcc set_scope_in_cluster('cluster') 364 end 365 end 366 367 dbcc cis("srvdes", @srvid) 368 369 /* restore dbcc command scope */ 370 if (@@clustermode = "shared disk cluster") 371 begin 372 if (@scope = "instance") 373 begin 374 dbcc set_scope_in_cluster('instance') 375 end 376 end 377 378 return (0) 379 end 380 381 /* 382 ** Update the server Kerberos principal name 383 */ 384 if @optname = "server principal" 385 begin 386 if (proc_role("sso_role") < 1) 387 return (1) 388 389 update master.dbo.sysservers 390 set srvprincipal = @optvalue 391 where srvid = @srvid 392 393 394 395 /* 396 ** Update the srvdes 397 ** For SDC, update cluster-wide in-memory SRVDES with data from 398 ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc 399 ** command scope needs to be set to cluster. 400 */ 401 if (@@clustermode = "shared disk cluster") 402 begin 403 select @scope = NULL 404 select @outstr = "dbcc set_scope_in_cluster('scope')" 405 if (charindex("instance", @outstr) != 0) 406 begin 407 /* save the scope to be restored later */ 408 select @scope = "instance" 409 dbcc set_scope_in_cluster('cluster') 410 end 411 end 412 413 dbcc cis("srvdes", @srvid) 414 415 /* restore dbcc command scope */ 416 if (@@clustermode = "shared disk cluster") 417 begin 418 if (@scope = "instance") 419 begin 420 dbcc set_scope_in_cluster('instance') 421 end 422 end 423 424 return (0) 425 end 426 427 /* 428 ** Update the server cost 429 */ 430 if @optname like "%server c%" 431 begin 432 /* 433 ** Changing the server cost requires SA role 434 */ 435 if (proc_role("sa_role") < 1) 436 goto clean_all 437 438 update master.dbo.sysservers 439 set srvcost = convert(smallint, @optvalue) 440 where srvid = @srvid 441 442 443 444 /* 445 ** Update the srvdes 446 ** For SDC, update cluster-wide in-memory SRVDES with data from 447 ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc 448 ** command scope needs to be set to cluster. 449 */ 450 if (@@clustermode = "shared disk cluster") 451 begin 452 select @scope = NULL 453 select @outstr = "dbcc set_scope_in_cluster('scope')" 454 if (charindex("instance", @outstr) != 0) 455 begin 456 /* save the scope to be restored later */ 457 select @scope = "instance" 458 dbcc set_scope_in_cluster('cluster') 459 end 460 end 461 462 dbcc cis("srvdes", @srvid) 463 464 /* restore dbcc command scope */ 465 if (@@clustermode = "shared disk cluster") 466 begin 467 if (@scope = "instance") 468 begin 469 dbcc set_scope_in_cluster('instance') 470 end 471 end 472 473 return (0) 474 end 475 476 /* 477 ** Get the number which is the bit value to set in srvstatus2 478 */ 479 select @statvalue2 = number 480 from master.dbo.spt_values 481 where name like "%" + @optname + "%" and type = "A2" 482 and number > 0 483 484 /* 485 ** Update serveroption in srvstatus2 field 486 ** 487 ** statvalue2 serveroption 488 ** --------- ------------ 489 ** 1490 ** 2 enable login redirection 491 ** 4 cluster instance 492 ** 493 */ 494 if (@statvalue2 in (2, 4)) 495 begin 496 if (proc_role("sa_role") < 1) 497 goto clean_all 498 499 select @srvstatus2 = isnull(srvstatus2, 0) from master.dbo.sysservers 500 where srvid = @srvid 501 502 if lower(@optvalue) in ("true", @true) 503 begin 504 if (@statvalue2 = 4) 505 begin 506 select @inst_id = instance_id(@server) 507 if @inst_id is NULL 508 begin 509 /* 510 ** 19661, "Server '%1!' is not a cluster instance, 511 ** can not set server option." 512 */ 513 exec sp_getmessage 19661, @msg output 514 print @msg, @server 515 goto clean_all 516 end 517 select @netname = srvnetname from master.dbo.sysservers 518 where srvid = @srvid 519 if (@server != @netname) 520 begin 521 /* 522 ** 19662, "Server name '%1!' doesn't match with its netname '%2!', 523 ** can not set server option." 524 */ 525 exec sp_getmessage 19662, @msg output 526 print @msg, @server, @netname 527 goto clean_all 528 end 529 end 530 update master.dbo.sysservers 531 set srvstatus2 = @srvstatus2 | @statvalue2 532 where srvid = @srvid 533 534 end 535 else 536 begin 537 if (@statvalue2 = 4) 538 begin 539 select @inst_id = instance_id(@server) 540 if @inst_id is not NULL 541 begin 542 /* 543 ** 19663, "Server '%1!' is a cluster instance, 544 ** can not unset server option." 545 */ 546 exec sp_getmessage 19663, @msg output 547 print @msg, @server 548 goto clean_all 549 end 550 end 551 update master.dbo.sysservers 552 set srvstatus2 = @srvstatus2 & ~ @statvalue2 553 where srvid = @srvid 554 555 end 556 557 /* 558 ** Update the srvdes 559 ** For SDC, update cluster-wide in-memory SRVDES with data from 560 ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc 561 ** command scope needs to be set to cluster. 562 */ 563 if (@@clustermode = "shared disk cluster") 564 begin 565 select @scope = NULL 566 select @outstr = "dbcc set_scope_in_cluster('scope')" 567 if (charindex("instance", @outstr) != 0) 568 begin 569 /* save the scope to be restored later */ 570 select @scope = "instance" 571 dbcc set_scope_in_cluster('cluster') 572 end 573 end 574 575 dbcc cis("srvdes", @srvid) 576 577 /* restore dbcc command scope */ 578 if (@@clustermode = "shared disk cluster") 579 begin 580 if (@scope = "instance") 581 begin 582 dbcc set_scope_in_cluster('instance') 583 end 584 end 585 586 return (0) 587 end 588 589 /* 590 ** Update srvstatus2 field values 591 ** 1 relocated joins 592 ** 8 incompatible sort order 593 */ 594 if (@optname like "%rel%" or @optname like "%inc%") 595 begin 596 /* 597 ** Get the number which is the bit value to set 598 */ 599 select @statvalue2 = number 600 from master.dbo.spt_values 601 where name like "%" + @optname + "%" and type = "A2" 602 and number > 0 603 /* 604 ** Changing 'relocated joins' or 'incompatible sort order' requires SA role 605 */ 606 if (proc_role("sa_role") < 1) 607 goto clean_all 608 609 select @srvstatus2 = isnull(srvstatus2, 0) from master.dbo.sysservers 610 where srvid = @srvid 611 612 if lower(@optvalue) in ("true", @true) 613 begin 614 update master.dbo.sysservers 615 set srvstatus2 = @srvstatus2 | @statvalue2 616 where srvid = @srvid 617 618 end 619 else 620 begin 621 update master.dbo.sysservers 622 set srvstatus2 = @srvstatus2 & ~ @statvalue2 623 where srvid = @srvid 624 625 end 626 627 /* 628 ** Update the srvdes 629 */ 630 dbcc cis("srvdes", @srvid) 631 632 return (0) 633 end 634 635 636 /* 637 ** Get the number which is the bit value to set 638 */ 639 select @statvalue = number 640 from master.dbo.spt_values 641 where name like "%" + @optname + "%" and type = "A" 642 and number > 0 643 644 /* 645 ** OMNI: If we're setting server logins, dissallow 646 ** when @@servername is null 647 */ 648 if @statvalue = 512 and @@servername is NULL 649 begin 650 /* 651 ** 18686, "Server option 'server logins' cannot be set 652 ** when @@servername is null" 653 */ 654 exec sp_getmessage 18686, @msg output 655 print @msg 656 goto clean_all 657 end 658 659 /* 660 ** Check for SSO options: 661 ** 662 ** value : 2 net password encryption 663 ** value : 64 use message confidentiality 664 ** value : 128 use message integrity 665 ** value : 256 mutual authentication 666 ** value : 512 server logins 667 ** value : 4096 negotiated logins 668 ** 669 ** Following server options are obsolete. 670 ** value : 8 rpc security model A 671 ** value : 16 rpc security model B 672 ** 673 */ 674 if (@statvalue in (2, 64, 128, 256, 512, 4096)) 675 begin 676 if (proc_role("sso_role") < 1) 677 goto clean_all 678 end 679 680 681 /* 682 ** Check for HA options 683 ** 684 ** value : 32 cis hafailover 685 */ 686 else if (@statvalue in (32)) 687 begin 688 if (proc_role("ha_role") < 1) 689 goto clean_all 690 end 691 692 /* 693 ** For any other option check for SA role 694 ** 695 ** Currently the following are left 696 ** 697 ** value : 0 timeouts 698 ** value : 4 readonly 699 ** value : 1024 external engine auto start 700 */ 701 else 702 begin 703 if (proc_role("sa_role") < 1) 704 goto clean_all 705 end 706 707 select @srvstatus = srvstatus from master.dbo.sysservers 708 where srvid = @srvid 709 710 /* 711 ** Now update sysservers. 712 ** The timeouts option is handled a little strangely since the default 713 ** is timeouts. Therefore timeouts = true means to clear the bit 714 ** and timeouts = false means to set the bit. 715 */ 716 717 718 719 if lower(@optvalue) in ("true", @true) 720 begin 721 if (@statvalue = 1) 722 begin 723 update master.dbo.sysservers 724 set srvstatus = srvstatus & ~ @statvalue 725 where srvid = @srvid 726 727 end 728 else 729 begin 730 update master.dbo.sysservers 731 set srvstatus = srvstatus | @statvalue 732 where srvid = @srvid 733 734 end 735 end 736 737 /* 738 ** We want to turn it off. 739 */ 740 else 741 begin 742 if @statvalue = 1 743 begin 744 update master.dbo.sysservers 745 set srvstatus = srvstatus | @statvalue 746 where srvid = @srvid 747 748 end 749 else 750 begin 751 update master.dbo.sysservers 752 set srvstatus = srvstatus & ~ @statvalue 753 where srvid = @srvid 754 755 end 756 end 757 758 759 760 761 /* 762 ** If use message confidentiality, use message integrity or 763 ** mutual authentication, update the srvdes. 764 ** OMNI: If net password encryption, readonly, hafailover, server login or 765 ** negotiated logins option is specified, update the srvdes 766 */ 767 if @statvalue in (2, 4, 32, 64, 128, 256, 512, 4096) 768 begin 769 /* 770 ** For SDC, update cluster-wide in-memory SRVDES with data from 771 ** just-updated SYSSERVERS table. Before dbcc cis, the dbcc 772 ** command scope needs to be set to cluster. 773 */ 774 if (@@clustermode = "shared disk cluster") 775 begin 776 select @scope = NULL 777 select @outstr = "dbcc set_scope_in_cluster('scope')" 778 if (charindex("instance", @outstr) != 0) 779 begin 780 /* save the scope to be restored later */ 781 select @scope = "instance" 782 dbcc set_scope_in_cluster('cluster') 783 end 784 end 785 786 dbcc cis("srvdes", @srvid) 787 788 /* restore dbcc command scope */ 789 if (@@clustermode = "shared disk cluster") 790 begin 791 if (@scope = "instance") 792 begin 793 dbcc set_scope_in_cluster('instance') 794 end 795 end 796 end 797 798 return (0) 799 800 clean_all: 801 802 803 804 return (1) 805
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 | |
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  calls proc sybsystemprocs..sp_getmessage  reads table master..sysmessages (1)  reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) read_writes table master..sysservers (1) read_writes table tempdb..#server_options (1) reads table master..spt_values (1) |
