| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_modifylogin | | 31 Aug 14 | Defects 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
