| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_droplogin | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** IMPORTANT: Please read the following instructions before 4 ** making changes to this stored procedure. 5 ** 6 ** To make this stored procedure compatible with High Availability (HA), 7 ** changes to certain system tables must be propagated 8 ** to the companion server under some conditions. 9 ** The tables include (but are not limited to): 10 ** syslogins, sysservers, sysattributes, systimeranges, 11 ** sysresourcelimits, sysalternates, sysdatabases, 12 ** syslanguages, sysremotelogins, sysloginroles, 13 ** sysalternates (master DB only), systypes (master DB only), 14 ** sysusers (master DB only), sysprotects (master DB only) 15 ** please refer to the HA documentation for detail. 16 ** 17 ** Here is what you need to do: 18 ** For each insert/update/delete statement, add three sections to 19 ** -- start HA transaction prior to the statement 20 ** -- add the statement 21 ** -- add HA synchronization code to propagate the change to the companion 22 ** 23 ** For example, if you are adding 24 ** insert master.dbo.syslogins ...... 25 ** the code should look like: 26 ** 1. Before that SQL statement: 27 ** 28 ** 2. Now, the SQL statement: 29 ** insert master.dbo.syslogins ...... 30 ** 3. Add a HA synchronization section right after the SQL statement: 31 ** 32 ** 33 ** You may need to do similar change for each built-in function you 34 ** want to add. 35 ** 36 ** Finally, add a separate part at a place where it can not 37 ** be reached by the normal execution path: 38 ** clean_all: 39 ** 40 ** return (1) 41 */ 42 43 create procedure sp_droplogin 44 @loginame varchar(255), /* name of login account to drop */ 45 @with_override int = 0 /* If 1, drop login even if database(s) 46 ** are not available to check for 47 ** login references. 48 */ 49 as 50 declare @msg varchar(1024) 51 , @suid int /* suid of person to change pw on */ 52 , @returncode int 53 , @dummy int 54 , @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 55 , @retstat int 56 , @ldapcfg int /* LDAP User Auth config level */ 57 , @log_for_rep int 58 , @db_rep_level_all int 59 , @db_rep_level_none int 60 , @db_rep_level_l1 int 61 , @lt_rep_get_failed int 62 , @auxproc varchar(1024) 63 , @objectcount int 64 , @dbname varchar(255) /* Variable to store database name */ 65 , @valid_user int /* Stores if user is valid in any database */ 66 , @scope varchar(32) /* SDC only, command execution scope */ 67 , @instanceid int /* SDC only, ID of owning instance of database */ 68 , @dbid int /* Variable to store database id */ 69 , @status int /* Variable to store database status */ 70 , @status2 int /* Variable to store database status2 */ 71 , @status3 int /* Variable to store database status3 */ 72 , @dbo_suid int /* suid of the real dbo */ 73 /* 74 ** "raiserror" truncates the arguments which are 75 ** more than MAXPRINT_ARGLEN = 1023 76 */ 77 , @valid_dbnames varchar(1023) /* Stores all database names in 78 ** which user is valid. 79 */ 80 , @offline_dbnames varchar(1023) /* Stores all database names 81 ** which are offline 82 */ 83 , @offline_db int /* Stores if any database is offline */ 84 , @dbo_use_only_dbnames varchar(1023) 85 /* Stores all database names 86 ** which are 'dbo use only' and the 87 ** caller is not dbo. 88 */ 89 , @dbo_use_only int /* Stores if any database is not 90 ** accessible due to 'dbo use only' 91 */ 92 , @ret int 93 94 /* 95 ** Initialize some constants 96 */ 97 select @db_rep_level_all = - 1, 98 @db_rep_level_none = 0, 99 @db_rep_level_l1 = 1, 100 @lt_rep_get_failed = - 2 101 select @valid_user = 0, 102 @offline_db = 0, 103 @dbo_use_only = 0, 104 @status = 0, 105 @status2 = 0, 106 @status3 = 0 107 108 /* 109 ** Get the replication status of the 'master' database 110 */ 111 select @log_for_rep = getdbrepstat(1) 112 if (@log_for_rep = @lt_rep_get_failed) 113 begin 114 raiserror 18409, "getdbrepstat" 115 return (1) 116 end 117 118 /* 119 ** Convert the replication status to a boolean 120 */ 121 if (@log_for_rep != @db_rep_level_none) 122 select @log_for_rep = 1 123 else 124 select @log_for_rep = 0 125 126 /* 127 ** If we are logging this system procedure for replication, we must be in 128 ** the 'master' database to avoid creating a multi-database transaction 129 ** which could make recovery of the 'master' database impossible. 130 */ 131 if (@log_for_rep = 1) and (db_name() != "master") 132 begin 133 raiserror 18388, "sp_droplogin" 134 return (1) 135 end 136 137 select @HA_CERTIFIED = 0 138 139 140 141 /* check to see if we are using HA specific SP for a HA enabled server */ 142 exec @retstat = sp_ha_check_certified 'sp_droplogin', @HA_CERTIFIED 143 if (@retstat != 0) 144 return (1) 145 146 /* 147 ** This procedure makes a weak attempt to check for any dependencies. 148 ** It looks in the current database to see if the suid is used in 149 ** sysusers or sysalternates. If so, a diagnostic is issued and the 150 ** login is not dropped. 151 ** 152 ** Ideally, this procedure should check each database to see if the login 153 ** is a user. However, this is not yet possible since procedures do not 154 ** allow parameters to be used for database or table names. 155 */ 156 157 /* 158 ** Do not allow this system procedure to be run from within a transaction 159 ** to avoid creating a multi-database transaction where the 'master' 160 ** database is not the co-ordinating database. 161 */ 162 if @@trancount > 0 163 begin 164 /* 165 ** 17260, "Can't run %1! from within a transaction." 166 */ 167 raiserror 17260, "sp_droplogin" 168 return (1) 169 end 170 else 171 begin 172 set chained off 173 end 174 175 set transaction isolation level 1 176 177 /* check if user has sso role, proc_role will also do auditing 178 ** if required. proc_role will also print error message if required. 179 */ 180 if (proc_role("sso_role") = 0) 181 return (1) 182 183 /* 184 ** Check if the account exists 185 */ 186 select @suid = suid 187 from master.dbo.syslogins 188 where name = @loginame 189 if @suid is NULL 190 begin 191 /* 192 ** 17880, "No such account -- nothing changed." 193 */ 194 raiserror 17880 195 return (1) 196 end 197 198 /* 199 ** Declare a temp table to hold information of objects 200 ** which are concretely owned by the login. 201 */ 202 create table #object_info( 203 Db_name varchar(30) null, 204 Object_name varchar(255) null, 205 Object_type varchar(255) null, 206 Object_owner varchar(255) null, 207 Object_loginame varchar(255) null) 208 209 /* 210 ** Declare a read only cursor to select all the database names 211 ** in master.dbo.sysdatabases 212 */ 213 declare drop_login cursor for 214 select name, dbid, status, status2, status3, suid from master.dbo.sysdatabases 215 216 for read only 217 218 /* Open the cusor to fetch the content in local variable */ 219 open drop_login 220 221 fetch drop_login into @dbname, @dbid, @status, @status2, @status3, @dbo_suid 222 223 while @@sqlstatus <> 2 224 begin 225 if @@sqlstatus = 1 226 begin 227 /* 228 ** 18999, "An error occurred while fetching data from a temporary 229 ** table. If there are no other error messages and this error 230 ** persists, please contact Sybase Technical Support." 231 */ 232 raiserror 18999 233 close drop_login 234 deallocate cursor drop_login 235 return (1) 236 end 237 238 /* 239 ** SDC only, skip the local temporary database belonging to other nodes 240 */ 241 if (@@clustermode = "shared disk cluster") 242 begin 243 /* 244 ** Get the ID of the owning instance if the specified database 245 ** is a local temporary database 246 */ 247 select @instanceid = db_instanceid(@dbname) 248 if ((@instanceid is not null) and (@instanceid <> @@instanceid)) 249 begin 250 fetch drop_login into @dbname, @dbid, @status, @status2, @status3, @dbo_suid 251 continue 252 end 253 end 254 255 /* Check for non available database */ 256 if (((@status3 & 4194304) = 4194304) /* archived */ 257 or ((@status & 256) = 256) /* suspect */ 258 or ((@status & 64) = 64) /* to be recovered */ 259 or ((@status & 32) = 32) /* in load */ 260 or ((@status2 & 16) = 16)) /* offline */ 261 begin 262 select @offline_dbnames = 263 @offline_dbnames + "'" + @dbname + "'" + " " 264 select @offline_db = 1 265 end 266 else 267 begin 268 /* Check for valid user in database */ 269 if (valid_user(@suid, @dbid) = 1) 270 begin 271 select @valid_dbnames = @valid_dbnames + "'" + @dbname + "'" + " " 272 select @valid_user = 1 273 end 274 275 select @auxproc = @dbname + ".dbo.sp_aux_get_concrtlyownedobj" 276 277 /* 278 ** Since sp_aux_get_concrtlyownedobj is a system 279 ** stored procedure, it will execute in the 280 ** context of dbname if it is called as 281 ** dbname..sp_aux_get_concrtlyownedobj, regardless of 282 ** which db it is called from. 283 ** 284 ** This auxilliary procedure extracts 285 ** the information about objects concretely owned 286 ** by the login, and put into temp table #object_info 287 */ 288 /* 289 ** 'dbo_use_only' database will give access error if the 290 ** caller is not DBO of the database. Record such databases 291 ** and issues a warning later. Otherwise populate any found 292 ** concretely owned objects by the login into #object_info. 293 */ 294 if ((@status & 2048) = 2048 and 295 charindex("sa_role", show_role()) = 0 and 296 @dbo_suid != suser_id()) 297 begin 298 select @dbo_use_only_dbnames = @dbo_use_only_dbnames 299 + "'" + @dbname + "'" + " " 300 select @dbo_use_only = 1 301 end 302 else 303 begin 304 exec @auxproc @dbname, @loginame 305 end 306 end 307 308 fetch drop_login into @dbname, @dbid, @status, @status2, @status3, @dbo_suid 309 end 310 close drop_login 311 deallocate cursor drop_login 312 313 select @ret = 0 314 /* return if user is valid in any database */ 315 if @valid_user = 1 316 begin 317 /* 19587, "User exists or is an alias or is a database owner in %1! database(s)." */ 318 raiserror 19587, @valid_dbnames 319 select @ret = 1 320 end 321 if @with_override = 1 322 begin 323 /* display warning message */ 324 if @offline_db = 1 325 begin 326 /* 327 ** 17017, "Warning: The database(s) %1! is (are) currently not 328 ** available and cannot be checked for login references. 329 ** The request to ignore this error may leave users for 330 ** this login in the database(s). 331 */ 332 raiserror 17017, @offline_dbnames 333 334 /* Do not return (1). Proceed further */ 335 end 336 end 337 else 338 begin 339 /* return if any database is not available for login references */ 340 if @offline_db = 1 341 begin 342 /* 343 ** 17018, "The database(s) %1! is (are) currently not available and 344 ** cannot be checked for login references. 345 ** The command has been aborted. 346 */ 347 raiserror 17018, @offline_dbnames 348 select @ret = 1 349 end 350 end 351 if @ret = 1 352 begin 353 return (1) 354 end 355 356 select @objectcount = count(*) 357 from #object_info 358 359 /* 360 ** Disallow dropping if the login concretely owns 361 ** object in any database. 362 */ 363 if @objectcount > 0 364 begin 365 /* 366 ** 19876, "User '%1!' concretely owns object(s) in at least 367 ** one database. Alter the object ownership before dropping 368 ** the login." 369 */ 370 raiserror 19876, @loginame 371 print " " 372 exec sp_autoformat @fulltabname = #object_info, 373 @orderby = "order by 1, 2" 374 drop table #object_info 375 return (1) 376 end 377 drop table #object_info 378 379 if @dbo_use_only = 1 380 begin 381 /* 382 ** 17021, "Warning: The database(s) %1! is (are) 'dbo use only' and 383 ** cannot be checked for objects whose concrete ownership is 384 ** associated with this login. Ask the database owner(s) to check 385 ** for orphaned references in sysobjects.loginame in %2!. 386 */ 387 raiserror 17021, @dbo_use_only_dbnames, @dbo_use_only_dbnames 388 389 /* Do not return (1). Proceed further */ 390 end 391 392 /* 393 ** SDC only, check clusterwide sysprocesses for active login 394 */ 395 if (@@clustermode = "shared disk cluster") 396 begin 397 select @scope = @@system_view 398 set system_view cluster 399 end 400 401 /* 402 ** Disallow dropping an account who has already logged in. 403 ** Note that it eliminates the race condition between two processes 404 ** for checking the last remaining unlocked SSO account. 405 */ 406 if exists (select * from master.dbo.sysprocesses where suid = @suid) 407 begin 408 /* 409 ** SDC only, restore previous system_view scope 410 */ 411 if (@@clustermode = "shared disk cluster") 412 begin 413 set system_view @scope 414 end 415 416 /* 17915, 417 ** "Warning: the specified account is currently active." 418 */ 419 exec sp_getmessage 17915, @msg output 420 print @msg 421 /* 422 ** 17918, "Nothing changed." 423 */ 424 exec sp_getmessage 17918, @msg output 425 print @msg 426 return (1) 427 end 428 /* 429 ** SDC only, restore previous system_view scope 430 */ 431 if (@@clustermode = "shared disk cluster") 432 begin 433 set system_view @scope 434 end 435 436 437 438 439 440 /* 441 ** Lock the account to prevent it from begin active. 442 */ 443 execute @returncode = sp_locklogin @loginame, "lock" 444 445 if (@returncode != 0) 446 return (@returncode) 447 448 if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1)) 449 begin tran rs_logexec 450 451 452 453 /* remove all resource limits associated with this login */ 454 delete from master.dbo.sysresourcelimits where name = @loginame 455 456 457 /* 458 ** Delete the login. 459 */ 460 delete from master.dbo.syslogins 461 where name = @loginame 462 463 /* 464 ** Check @@rowcount when it works 465 */ 466 if (@@rowcount > 0) 467 begin 468 /* remove all roles related information from sysloginroles */ 469 delete from master.dbo.sysloginroles 470 where suid = @suid 471 472 473 /* 474 ** delete entries from sysattributes for this login. 475 ** login entries are type 'PS'; login entries are 476 ** type 'L' or external login entries are type 'EL'; 477 ** login entries for local mapped logins 478 ** are type 'LM' 479 */ 480 delete from master.dbo.sysattributes 481 where object = @suid 482 and object_type in ("EL", "L", "LM") 483 484 485 delete from master.dbo.sysattributes 486 where object_type = "PS" and object_cinfo = "login" 487 and object = @suid 488 489 490 /* 491 ** Delete any bindings associated with the login. 492 */ 493 delete from master.dbo.sysattributes 494 where ((class = 6 or class = 16) 495 and ((object_type = "LG" 496 and object = @suid) 497 or (object_type = "AP" 498 and object_info1 = @suid))) 499 500 501 /* delete entries in sysremotelogins for this login */ 502 delete from master.dbo.sysremotelogins 503 where suid = @suid 504 505 506 507 if (@log_for_rep = 1) 508 begin 509 /* 510 ** If the 'master' database is marked for replication, the 511 ** T-SQL built-in 'logexec()' will log for replication the 512 ** execution instance of this system procedure. Otherwise, 513 ** the T-SQL built-in 'logexec()' is a no-op. 514 */ 515 if (logexec(1) != 1) 516 begin 517 raiserror 17756, "sp_droplogin", "master" 518 goto clean_all 519 end 520 end 521 522 if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1)) 523 commit tran rs_logexec 524 525 /* 526 ** Run the custom clean up procedure 527 */ 528 if exists (select 1 from master.dbo.sysobjects where name = 529 'sp_cleanpwdchecks') 530 begin 531 exec ("exec master.dbo.sp_cleanpwdchecks @loginame") 532 end 533 534 /* 535 ** 17511, "Login dropped." 536 */ 537 exec sp_getmessage 17511, @msg output 538 print @msg 539 return (0) 540 end 541 else 542 begin 543 /* 544 ** 17231, "No login with the specified name exists." 545 */ 546 raiserror 17231 547 goto clean_all 548 end 549 550 clean_all: 551 if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1)) 552 rollback tran rs_logexec 553 return (1) 554
exec sp_procxmode 'sp_droplogin', 'AnyMode' go Grant Execute on sp_droplogin to public go
| DEFECTS | |
 MCTR 4 Conditional Begin Tran or Commit Tran | 449 |
| MCTR 4 Conditional Begin Tran or Commit Tran | 523 |
| MINU 4 Unique Index with nullable columns master..sysattributes | master..sysattributes |
| MINU 4 Unique Index with nullable columns master..sysremotelogins | master..sysremotelogins |
| MTYP 4 Assignment type mismatch @scope: varchar(32) = int | 397 |
| MTYP 4 Assignment type mismatch @loginame: varchar(30) = varchar(255) | 443 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname | 372 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object} | 481 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object, object_type, object_cinfo} | 486 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 494 |
| TNOI 4 Table with no index master..sysprocesses | master..sysprocesses |
| TNOU 4 Table with no unique index master..sysloginroles | master..sysloginroles |
| TNOU 4 Table with no unique index master..sysresourcelimits | master..sysresourcelimits |
| VRUN 4 Variable is read and not initialized @offline_dbnames | 263 |
| VRUN 4 Variable is read and not initialized @valid_dbnames | 271 |
| VRUN 4 Variable is read and not initialized @dbo_use_only_dbnames | 298 |
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain | 43 |
| MGTP 3 Grant to public master..sysattributes | |
| MGTP 3 Grant to public master..sysdatabases | |
| MGTP 3 Grant to public master..syslogins | |
| MGTP 3 Grant to public master..sysobjects | |
| MGTP 3 Grant to public master..sysprocesses | |
| MGTP 3 Grant to public master..sysremotelogins | |
| MGTP 3 Grant to public master..sysresourcelimits | |
| MGTP 3 Grant to public sybsystemprocs..sp_droplogin | |
| MNER 3 No Error Check should check return value of exec | 372 |
| MNER 3 No Error Check should check return value of exec | 419 |
| MNER 3 No Error Check should check return value of exec | 424 |
| MNER 3 No Error Check should check @@error after delete | 454 |
| MNER 3 No Error Check should check @@error after delete | 460 |
| MNER 3 No Error Check should check @@error after delete | 469 |
| MNER 3 No Error Check should check @@error after delete | 480 |
| MNER 3 No Error Check should check @@error after delete | 485 |
| MNER 3 No Error Check should check @@error after delete | 493 |
| MNER 3 No Error Check should check @@error after delete | 502 |
| MNER 3 No Error Check should check return value of exec | 537 |
| MUCO 3 Useless Code Useless Brackets | 112 |
| MUCO 3 Useless Code Useless Brackets | 115 |
| MUCO 3 Useless Code Useless Brackets | 121 |
| MUCO 3 Useless Code Useless Brackets | 134 |
| MUCO 3 Useless Code Useless Brackets | 143 |
| MUCO 3 Useless Code Useless Brackets | 144 |
| MUCO 3 Useless Code Useless Brackets | 168 |
| MUCO 3 Useless Code Useless Brackets | 180 |
| MUCO 3 Useless Code Useless Brackets | 181 |
| MUCO 3 Useless Code Useless Brackets | 195 |
| MUCO 3 Useless Code Useless Brackets | 235 |
| MUCO 3 Useless Code Useless Brackets | 241 |
| MUCO 3 Useless Code Useless Brackets | 248 |
| MUCO 3 Useless Code Useless Brackets | 256 |
| MUCO 3 Useless Code Useless Brackets | 269 |
| MUCO 3 Useless Code Useless Brackets | 294 |
| MUCO 3 Useless Code Useless Brackets | 353 |
| MUCO 3 Useless Code Useless Brackets | 375 |
| MUCO 3 Useless Code Useless Brackets | 395 |
| MUCO 3 Useless Code Useless Brackets | 411 |
| MUCO 3 Useless Code Useless Brackets | 426 |
| MUCO 3 Useless Code Useless Brackets | 431 |
| MUCO 3 Useless Code Useless Brackets | 445 |
| MUCO 3 Useless Code Useless Brackets | 446 |
| MUCO 3 Useless Code Useless Brackets | 448 |
| MUCO 3 Useless Code Useless Brackets | 466 |
| MUCO 3 Useless Code Useless Brackets | 494 |
| MUCO 3 Useless Code Useless Brackets | 507 |
| MUCO 3 Useless Code Useless Brackets | 515 |
| MUCO 3 Useless Code Useless Brackets | 522 |
| MUCO 3 Useless Code Useless Brackets | 539 |
| MUCO 3 Useless Code Useless Brackets | 551 |
| MUCO 3 Useless Code Useless Brackets | 553 |
| QISO 3 Set isolation level | 175 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysresourcelimits.csysresourcelimits clustered (name, appname) Intersection: {name} | 454 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_info1, object, class} | 494 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique (name, uid) Intersection: {name} | 528 |
| QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F | 470 |
| VNRD 3 Variable is not read @db_rep_level_all | 97 |
| VNRD 3 Variable is not read @db_rep_level_l1 | 99 |
| VUNU 3 Variable is not used @dummy | 53 |
| VUNU 3 Variable is not used @ldapcfg | 56 |
 CRDO 2 Read Only Cursor Marker (has for read only clause) | 214 |
| MDYE 2 Dynamic Exec Marker exec @auxproc | 304 |
| MDYS 2 Dynamic SQL Marker | 531 |
| MSUB 2 Subquery Marker | 406 |
| MSUB 2 Subquery Marker | 528 |
| MTR1 2 Metrics: Comments Ratio Comments: 54% | 43 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 36 = 44dec - 10exi + 2 | 43 |
| MTR3 2 Metrics: Query Complexity Complexity: 198 | 43 |
