| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_addexternlogin | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 4 /* 5 ** Omni only 6 ** 7 ** Messages for "sp_addexternlogin" 18294 8 ** 9 ** 17240, "'%1!' is not a valid name." 10 ** 17260, "Can't run '%1!' from within a transaction." 11 ** 17270, "There is not a server named '%1!'." 12 ** 17271, "'%1!' is the local server - remote login not applicable." 13 ** 17067 "Unable to encrypt password for '%1!'. See prior error message for reason." 14 ** 18294, "User '%1!' is not a local user -- request denied." 15 ** 18295, "Only the 'sa' may update another's external login." 16 ** 18296, "External login updated." 17 ** 18297, "User '%1!' will be known as '%2!' in remote server '%3!'." 18 ** 18342, "Invalid name '%1!'. This role or user does not exist in this SQL Server." 19 ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'" 20 ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins." 21 ** 18780, "Synchronization will not occur because server '%1!' is the companion server." 22 ** 18782 "Unable to find a server with name '%1!' and id '%2!'." 23 ** 18886, "Users with role '%1!' will be known as '%2!' in remote server '%3!'." 24 ** 19403, "IBM MQ servers cannot have a registered login" 25 ** 19404, "IBM MQ servers cannot have a default login" 26 */ 27 28 /* 29 ** IMPORTANT: Please read the following instructions before 30 ** making changes to this stored procedure. 31 ** 32 ** To make this stored procedure compatible with High Availability (HA), 33 ** changes to certain system tables must be propagated 34 ** to the companion server under some conditions. 35 ** The tables include (but are not limited to): 36 ** syslogins, sysservers, sysattributes, systimeranges, 37 ** sysresourcelimits, sysalternates, sysdatabases, 38 ** syslanguages, sysremotelogins, sysloginroles, 39 ** sysalternates (master DB only), systypes (master DB only), 40 ** sysusers (master DB only), sysprotects (master DB only) 41 ** please refer to the HA documentation for detail. 42 ** 43 ** Here is what you need to do: 44 ** For each insert/update/delete statement, add three sections to 45 ** -- start HA transaction prior to the statement 46 ** -- add the statement 47 ** -- add HA synchronization code to propagate the change to the companion 48 ** 49 ** For example, if you are adding 50 ** insert master.dbo.syslogins ...... 51 ** the code should look like: 52 ** 1. Before that SQL statement: 53 ** 54 ** 2. Now, the SQL statement: 55 ** insert master.dbo.syslogins ...... 56 ** 3. Add a HA synchronization section right after the SQL statement: 57 ** 58 ** 59 ** You may need to do similar change for each built-in function you 60 ** want to add. 61 ** 62 ** Finally, add a separate part at a place where it can not 63 ** be reached by the normal execution path: 64 ** clean_all: 65 ** 66 ** return (1) 67 */ 68 69 create procedure sp_addexternlogin 70 @server varchar(255), /* name of remote server */ 71 @loginame varchar(255), /* user's local name */ 72 @externname varchar(255), /* user's remote name */ 73 @externpasswd varchar(2000) = null, /* user's remote password */ 74 @rolename varchar(255) = null, /* name of roll to map */ 75 @passwd_vers int = 0 /* for replication */ 76 as 77 begin 78 79 declare @msg varchar(1024), 80 @encrpasswd varchar(2000), 81 @srvid smallint, 82 @dbname sysname, 83 @suid int, 84 @srid int, 85 @action smallint, 86 @dso_class smallint, 87 @attrib smallint, 88 @dummy int, 89 @maxlen int, 90 @HA_CERTIFIED tinyint, 91 @retstat int, 92 @srvclass smallint, 93 @prokey int, 94 @svckey int, 95 @stakey int, 96 @cfgoption int, 97 @replicate int, 98 @master_is_rep int, 99 @license int 100 101 102 select @HA_CERTIFIED = 0, 103 @retstat = 0, 104 @srid = - 1, 105 @prokey = 1, 106 @svckey = 53, 107 @stakey = 54, 108 @cfgoption = 442, 109 @encrpasswd = null, 110 @replicate = 0, 111 @master_is_rep = 0 112 113 select @license = license_enabled("ASE_ENCRYPTION") 114 115 /* 116 ** The variable @passwd_vers is only non-zero in the replicated site. 117 ** It's not a general purpose variable. It can be used only to 118 ** indicate that the procedure is being executed by the replication 119 ** server and some manipulation of the password is needed. 120 */ 121 if (@passwd_vers = 0) 122 begin 123 /* This is the replicate (primary) site */ 124 select @replicate = 1 125 126 /* 127 ** master is replicated if this is the primary site 128 ** and the database master is marked for replication. 129 ** We will consider master as not being replicated 130 ** if this procedure is being executed by the 131 ** replication server (@passwd_vers != 0). 132 */ 133 select @master_is_rep = getdbrepstat(1) 134 135 if (@master_is_rep < 0) 136 begin 137 raiserror 18409, "getdbrepstat" 138 return (1) 139 end 140 141 /* 142 ** We are not interested on the level of replication, 143 ** only if it's replicated or not. 144 */ 145 if (@master_is_rep != 0) 146 begin 147 select @master_is_rep = 1 148 end 149 150 /* 151 ** If we are logging this system procedure for replication, 152 ** we must be in the 'master' database to avoid creating a 153 ** multi-database transaction which could make recovery of 154 ** the 'master' database impossible. 155 */ 156 if (@master_is_rep = 1) and (db_name() != "master") 157 begin 158 /* 159 ** 18388, "You must be in the master database in order 160 ** to run '%1!'." 161 */ 162 raiserror 18388, "sp_addexternlogin" 163 return (1) 164 end 165 end 166 167 if (@externname is not null) 168 begin 169 select @maxlen = length from master.dbo.syscolumns 170 where id = object_id("master.dbo.syslogins") 171 and name = "name" 172 173 if (char_length(@externname) > @maxlen) 174 begin 175 /* 176 ** 17240, "'%1!' is not a valid name." 177 */ 178 raiserror 17240, @externname 179 return (1) 180 end 181 end 182 183 /* 184 ** Check the password length if it is plaintext or 185 ** encrypted with Sybase proprietary key 186 */ 187 if ((@passwd_vers <= @prokey) and (@externpasswd is not null)) 188 begin 189 select @maxlen = length from master.dbo.syscolumns 190 where id = object_id("master.dbo.syslogins") 191 and name = "password" 192 193 if (char_length(@externpasswd) > @maxlen) 194 begin 195 /* 196 ** 17240, "'%1!' is not a valid name." 197 */ 198 raiserror 17240, @externpasswd 199 return (1) 200 end 201 end 202 203 204 205 /* Check to see if we are using HA specific SP for a HA enabled server. */ 206 exec @retstat = sp_ha_check_certified 'sp_addexternlogin', @HA_CERTIFIED 207 if (@retstat != 0) 208 return (1) 209 210 /* 211 ** If we're in a transaction, disallow this since it might make recovery 212 ** impossible. 213 */ 214 if (@@trancount > 0) 215 begin 216 /* 217 ** 17260 Can't run '%1!' from within a transaction. 218 */ 219 raiserror 17260, "sp_addexternlogin" 220 return 1 221 end 222 223 set chained off 224 225 set transaction isolation level 1 226 227 /* 228 ** Check that the server name is valid. 229 */ 230 select @srvid = srvid, @srvclass = srvclass 231 from master.dbo.sysservers 232 where srvname = @server 233 234 if (@@rowcount = 0) 235 begin 236 /* 237 ** 17270 "There is not a server named %1!" 238 */ 239 raiserror 17270, @server 240 return (1) 241 end 242 243 /* 244 ** If it's the local server don't bother. 245 */ 246 if (@srvid = 0) 247 begin 248 /* 249 ** 17271 "'%1!' is the local server - remote login not applicable." 250 */ 251 raiserror 17271, @server 252 return (1) 253 end 254 255 /* 256 ** If @rolename was specified, ignore @loginame. 257 */ 258 if (@rolename is not null) 259 begin 260 select @loginame = null 261 262 /* 263 ** Check that the @rolename is valid. 264 */ 265 select @srid = srid from master.dbo.syssrvroles 266 where name = @rolename 267 268 if (@@rowcount != 1) 269 begin 270 /* 271 ** 18342, "Invalid name '%1!'. This role or user does 272 ** not exist in this SQL Server." 273 */ 274 raiserror 18342, @rolename 275 return (1) 276 end 277 end 278 279 if (@loginame is null) 280 begin 281 select @suid = - 1 282 end 283 else 284 begin 285 select @suid = suser_id(@loginame) 286 287 /* 288 ** Check that the @loginame is valid. 289 */ 290 if (@suid is null) 291 begin 292 /* 293 ** 18294 "User '%1!' is not a local user -- request denied." 294 */ 295 raiserror 18294, @loginame 296 return (1) 297 end 298 end 299 300 /* 301 ** Only a user with sa_role or sso_role can add extern logins for another 302 ** user or for a role. 303 */ 304 if (@suid != suser_id()) 305 begin 306 /* 307 ** Check if user has sa role, proc_role will 308 ** also do auditing if required. proc_role will also 309 ** print error message if required. 310 */ 311 if (charindex("sa_role", show_role()) = 0 and 312 charindex("sso_role", show_role()) = 0) 313 begin 314 select @dummy = proc_role("sa_role") 315 select @dummy = proc_role("sso_role") 316 return (1) 317 end 318 end 319 320 /* 321 ** We don't allow IBM_MQ servers to have extern logins. 322 */ 323 if (@srvclass = 13) 324 begin 325 if @loginame is null 326 begin 327 /* 328 ** 19404, "IBM MQ servers cannot have a default login" 329 */ 330 raiserror 19404 331 end 332 else 333 begin 334 /* 335 ** 19403, "IBM MQ servers cannot have a registered login" 336 */ 337 raiserror 19403 338 339 end 340 return (1) 341 end 342 343 /* 344 ** If there is already a user for this server defined, we'll update 345 ** it, else we'll insert a new row. 346 */ 347 if ((@srvclass != 12) and (@srvclass != 14) and (@srvclass != 15)) 348 select @dso_class = 9 349 else 350 select @dso_class = 21 351 352 select @attrib = 0 353 354 if exists (select * from master.dbo.sysattributes where 355 class = @dso_class and attribute = @attrib and 356 object_info1 = @srvid and object = @suid and 357 (object_info2 = @srid or object_info2 is null)) 358 select @action = 2 /* attribute change */ 359 else 360 select @action = 1 /* attribute add */ 361 362 /* 363 ** First validate the row. 364 */ 365 if (attrib_valid(@dso_class, @attrib, "EL", @suid, @srvid, @srid, null, 366 @externname, null, null, null, @externpasswd, null, @action) = 0) 367 begin 368 return (1) 369 end 370 371 372 373 /* 374 ** This transaction also writes a log record for replicating the 375 ** invocation of this procedure. If logexec() fails, the transaction 376 ** is aborted. 377 ** 378 ** IMPORTANT: The name rs_logexec is significant and is used by 379 ** the Adaptive Server. 380 */ 381 begin tran rs_logexec 382 383 384 385 /* Try encrypting using service key if available. */ 386 if ((@license = 1) 387 and object_id("master.dbo.syb_extpasswdkey") is not null) 388 begin 389 select @encrpasswd = 390 internal_encrypt(@externpasswd, @svckey, @passwd_vers) 391 392 if (@encrpasswd is null) 393 begin 394 /* 395 ** 17067 "Unable to encrypt password for '%1!'. 396 ** See prior error message for reason." 397 */ 398 raiserror 17067, @externname 399 select @retstat = 1 400 goto clean_all 401 end 402 end 403 404 /* 405 ** If the password could not be encrypted using the service key, 406 ** in the primary site, use sybase proprietary algorithm, in the 407 ** replicated, if any, just copy the password as it has been 408 ** received. 409 */ 410 if (@encrpasswd is null and @externpasswd is not null) 411 begin 412 if (@replicate = 0) 413 begin 414 select @encrpasswd = @externpasswd 415 end 416 else 417 begin 418 select @encrpasswd = internal_encrypt(@externpasswd, 419 @prokey, @passwd_vers) 420 end 421 end 422 423 /* 424 ** @externpasswd has to be encrypted with the static key if master 425 ** is replicated so the the value is picked up by logexec(). 426 */ 427 if (@master_is_rep = 1) 428 begin 429 select @externpasswd = internal_encrypt(@externpasswd, 430 @stakey, @passwd_vers) 431 432 /* 433 ** Even if the plaintext password was null, strong encryption 434 ** only returns NULL in case of an error. 435 */ 436 if (@externpasswd is null) 437 begin 438 /* 439 ** 17067 "Unable to encrypt password for '%1!'. 440 ** See prior error message for reason." 441 */ 442 raiserror 17067, @externname 443 select @retstat = 1 444 goto clean_all 445 end 446 end 447 448 /* 449 ** Now update/insert the row 450 */ 451 if (@action = 2) 452 begin 453 454 update master.dbo.sysattributes 455 set object_cinfo = @externname, 456 image_value = @encrpasswd 457 where class = @dso_class and attribute = @attrib and 458 object_info1 = @srvid and object = @suid and 459 (object_info2 = @srid or object_info2 is null) 460 461 end 462 else 463 begin 464 insert into master.dbo.sysattributes(class, attribute, 465 object_type, object_info1, object, object_cinfo, 466 image_value, object_info2, object_info3, 467 object_cinfo2, object_datetime) 468 values (@dso_class, @attrib, "EL", @srvid, @suid, @externname, 469 @encrpasswd, @srid, user_id(), 470 suser_name(), getdate()) 471 end 472 473 if (@@error != 0) 474 begin 475 select @retstat = 1 476 goto clean_all 477 end 478 479 480 481 select @passwd_vers = @stakey 482 if (logexec(1) != 1) 483 begin 484 /* 485 ** 17756, "The execution of the stored procedure '%1!' 486 ** in database '%2!' was aborted because there 487 ** was an error in writing the replication log 488 ** record." 489 */ 490 select @dbname = db_name() 491 raiserror 17756, "sp_addexternlogin", @dbname 492 493 select @retstat = 1 494 goto clean_all 495 end 496 497 commit tran rs_logexec 498 499 if (@@error != 0) 500 goto raise_error 501 502 /* 503 ** We are all set successfully. Inform the user if needed if this 504 ** is not the primary site. 505 */ 506 if (@replicate = 0) 507 begin 508 if (@action = 2) 509 begin 510 /* 511 ** 18296 "External login updated." 512 */ 513 exec sp_getmessage 18296, @msg output 514 print @msg 515 end 516 else 517 begin 518 if (@rolename is not null) 519 begin 520 /* 521 ** 18886 "Users with role '%1!' will be 522 ** known as '%2!' in remote server '%3!'." 523 */ 524 exec sp_getmessage 18886, @msg output 525 print @msg, @rolename, @externname, @server 526 end 527 else 528 begin 529 /* 530 ** 18297 "User '%1!' will be known as '%2!' 531 ** in remote server '%3!'." 532 */ 533 exec sp_getmessage 18297, @msg output 534 print @msg, @loginame, @externname, @server 535 end 536 end 537 end 538 539 /* 540 ** Sync the in-memory structures with the new values 541 ** in sysattributes. The procedure will return 1, 542 ** but the catalog changes have succeeded. 543 */ 544 if (attrib_notify(@dso_class, @attrib, "EL", @suid, @srvid, @srid, 545 null, null, null, null, null, null, null, 546 @action) = 0) 547 begin 548 select @retstat = 1 549 end 550 551 552 553 return (@retstat) 554 555 clean_all: 556 rollback tran rs_logexec 557 558 raise_error: 559 560 561 return (1) 562 end 563 564
exec sp_procxmode 'sp_addexternlogin', 'AnyMode' go Grant Execute on sp_addexternlogin to public go
| DEFECTS | |
 MINU 4 Unique Index with nullable columns master..sysattributes | master..sysattributes |
| MTYP 4 Assignment type mismatch image_value: image = varchar(2000) | 456 |
| MTYP 4 Assignment type mismatch image_value: image = varchar(2000) | 469 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint | 356 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint | 458 |
 MGTP 3 Grant to public master..sysattributes | |
| MGTP 3 Grant to public master..syscolumns | |
| MGTP 3 Grant to public master..sysservers | |
| MGTP 3 Grant to public master..syssrvroles | |
| MGTP 3 Grant to public sybsystemprocs..sp_addexternlogin | |
| MNER 3 No Error Check should check @@error after update | 454 |
| MNER 3 No Error Check should check @@error after insert | 464 |
| MNER 3 No Error Check should check return value of exec | 513 |
| MNER 3 No Error Check should check return value of exec | 524 |
| MNER 3 No Error Check should check return value of exec | 533 |
| MUCO 3 Useless Code Useless Begin-End Pair | 77 |
| MUCO 3 Useless Code Useless Brackets | 121 |
| MUCO 3 Useless Code Useless Brackets | 135 |
| MUCO 3 Useless Code Useless Brackets | 138 |
| MUCO 3 Useless Code Useless Brackets | 145 |
| MUCO 3 Useless Code Useless Brackets | 163 |
| MUCO 3 Useless Code Useless Brackets | 167 |
| MUCO 3 Useless Code Useless Brackets | 173 |
| MUCO 3 Useless Code Useless Brackets | 179 |
| MUCO 3 Useless Code Useless Brackets | 187 |
| MUCO 3 Useless Code Useless Brackets | 193 |
| MUCO 3 Useless Code Useless Brackets | 199 |
| MUCO 3 Useless Code Useless Brackets | 207 |
| MUCO 3 Useless Code Useless Brackets | 208 |
| MUCO 3 Useless Code Useless Brackets | 214 |
| MUCO 3 Useless Code Useless Brackets | 234 |
| MUCO 3 Useless Code Useless Brackets | 240 |
| MUCO 3 Useless Code Useless Brackets | 246 |
| MUCO 3 Useless Code Useless Brackets | 252 |
| MUCO 3 Useless Code Useless Brackets | 258 |
| MUCO 3 Useless Code Useless Brackets | 268 |
| MUCO 3 Useless Code Useless Brackets | 275 |
| MUCO 3 Useless Code Useless Brackets | 279 |
| MUCO 3 Useless Code Useless Brackets | 290 |
| MUCO 3 Useless Code Useless Brackets | 296 |
| MUCO 3 Useless Code Useless Brackets | 304 |
| MUCO 3 Useless Code Useless Brackets | 311 |
| MUCO 3 Useless Code Useless Brackets | 316 |
| MUCO 3 Useless Code Useless Brackets | 323 |
| MUCO 3 Useless Code Useless Brackets | 340 |
| MUCO 3 Useless Code Useless Brackets | 347 |
| MUCO 3 Useless Code Useless Brackets | 365 |
| MUCO 3 Useless Code Useless Brackets | 368 |
| MUCO 3 Useless Code Useless Brackets | 386 |
| MUCO 3 Useless Code Useless Brackets | 392 |
| MUCO 3 Useless Code Useless Brackets | 410 |
| MUCO 3 Useless Code Useless Brackets | 412 |
| MUCO 3 Useless Code Useless Brackets | 427 |
| MUCO 3 Useless Code Useless Brackets | 436 |
| MUCO 3 Useless Code Useless Brackets | 451 |
| MUCO 3 Useless Code Useless Brackets | 473 |
| MUCO 3 Useless Code Useless Brackets | 482 |
| MUCO 3 Useless Code Useless Brackets | 499 |
| MUCO 3 Useless Code Useless Brackets | 506 |
| MUCO 3 Useless Code Useless Brackets | 508 |
| MUCO 3 Useless Code Useless Brackets | 518 |
| MUCO 3 Useless Code Useless Brackets | 544 |
| MUCO 3 Useless Code Useless Brackets | 553 |
| MUCO 3 Useless Code Useless Brackets | 561 |
| MUPK 3 Update column which is part of a PK or unique index object_cinfo | 455 |
| QAFM 3 Var Assignment from potentially many rows | 169 |
| QAFM 3 Var Assignment from potentially many rows | 189 |
| QAFM 3 Var Assignment from potentially many rows | 265 |
| QISO 3 Set isolation level | 225 |
| QIWC 3 Insert with not all columns specified missing 4 columns out of 15 | 464 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id} | 170 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id} | 190 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {attribute, object_info2, object_info1, object, class} | 355 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {attribute, object_info2, object_info1, object, class} | 457 |
| VNRD 3 Variable is not read @cfgoption | 108 |
| VNRD 3 Variable is not read @dummy | 315 |
| VNRD 3 Variable is not read @passwd_vers | 481 |
 MSUB 2 Subquery Marker | 354 |
| MTR1 2 Metrics: Comments Ratio Comments: 52% | 69 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 40 = 51dec - 13exi + 2 | 69 |
| MTR3 2 Metrics: Query Complexity Complexity: 188 | 69 |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysservers (1)  calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  reads table master..syscolumns (1) reads table master..syssrvroles (1) calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) read_writes table master..sysattributes (1) |