Database | Proc | Application | Created | Links |
sybsystemprocs | sp_dropuser ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/defaultlanguage */ 4 5 /* 6 ** Messages for "sp_dropuser" 17543 7 ** 8 ** 17232, "No user with the specified name exists in the current database." 9 ** 17289, "Set your curwrite to the hurdle of current database." 10 ** 17431, "true" 11 ** 17432, "false" 12 ** 17543, "You cannot drop the 'database owner'." 13 ** 17544, "You cannot drop the 'guest' user from master database or a temporary 14 ** database." 15 ** 17545, "You cannot drop user because user '%1!' owns objects in database." 16 ** 17546, "You cannot drop user because user '%1!' owns types in database." 17 ** 17547, "The dependent aliases were also dropped." 18 ** 17548, "User has been dropped from current database." 19 ** 17549, "You cannot drop user because user '%1!' owns grantable 20 ** privileges and granted them to other users. The user 21 ** has granted the following privileges:" 22 ** 17673, "All" 23 ** 17756, "The execution of the stored procedure '%1!' in database 24 ** '%2!' was aborted because there was an error in writing the 25 ** replication log record." 26 ** 18033, "You cannot drop user because user '%1!' owns thresholds in database." 27 ** 18053, "User '%1!' was granted grantable privileges by the following users:" 28 ** 18075, "Set your maxwrite label correctly." 29 ** 18554, "You cannot drop user '%1!' because stored proc '%2!' owned by it 30 ** is bound to an execution class. Use sp_unbindexeclass before 31 ** dropping user." 32 ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'" 33 ** 18797, "Unable to find a user with name '%1!' and login id '%2!' in sysusers." 34 ** 18815, "You cannot drop user '%1!' because it still owns objects, types, thresholds, privileges, and/or execution classes on the companion server '%2!'. Issue stored procedure '%3!' on server '%4!' for details." 35 ** 19941, "You cannot drop the user because the suid value for user '%1!' is 36 ** not unique in the database." 37 */ 38 39 /* 40 ** IMPORTANT: Please read the following instructions before 41 ** making changes to this stored procedure. 42 ** 43 ** To make this stored procedure compatible with High Availability (HA), 44 ** changes to certain system tables must be propagated 45 ** to the companion server under some conditions. 46 ** The tables include (but are not limited to): 47 ** syslogins, sysservers, sysattributes, systimeranges, 48 ** sysresourcelimits, sysalternates, sysdatabases, 49 ** syslanguages, sysremotelogins, sysloginroles, 50 ** sysalternates (master DB only), systypes (master DB only), 51 ** sysusers (master DB only), sysprotects (master DB only) 52 ** please refer to the HA documentation for detail. 53 ** 54 ** Here is what you need to do: 55 ** For each insert/update/delete statement, add three sections to 56 ** -- start HA transaction prior to the statement 57 ** -- add the statement 58 ** -- add HA synchronization code to propagate the change to the companion 59 ** 60 ** For example, if you are adding 61 ** insert master.dbo.syslogins ...... 62 ** the code should look like: 63 ** 1. Before that SQL statement: 64 ** 65 ** 2. Now, the SQL statement: 66 ** insert master.dbo.syslogins ...... 67 ** 3. Add a HA synchronization section right after the SQL statement: 68 ** 69 ** 70 ** You may need to do similar change for each built-in function you 71 ** want to add. 72 ** 73 ** After that, you need to add a separate part at a place where it can not 74 ** be reached by the normal execution path: 75 ** clean_all: 76 ** 77 ** return (1) 78 */ 79 80 create procedure sp_dropuser 81 @name_in_db varchar(255) /* user name to drop */ 82 as 83 84 declare @suid int /* suid of the user */ 85 declare @uid int /* uid of the user */ 86 declare @objectcount int /* count of objects user owns */ 87 declare @typecount int /* count of types user owns */ 88 declare @grantcount int /* count of grants user made */ 89 declare @userdropped int /* flag to indicate user was dropped */ 90 declare @threshcount int /* count of thresholds bound by user */ 91 declare @spexebndcount int /* cnt of user owned sp exe bindings */ 92 declare @sp_name varchar(255) /* name of stored proc */ 93 declare @msg varchar(1024) 94 declare @msg_all varchar(30) /* msg for "all" equivalent */ 95 declare @msg_true varchar(30) /* msg for "true" equivalent */ 96 declare @msg_false varchar(30) /* msg for "false" equivalent */ 97 declare @dummy int 98 declare @dbname varchar(255) 99 declare @tempdb_mask int /* All database status bit for a tempdb */ 100 declare @isatempdb int /* is database a temporary database */ 101 declare @err1 int /* temp. variable to store @@error */ 102 103 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 104 declare @retstat int 105 106 107 select @HA_CERTIFIED = 0 108 109 110 111 /* check to see if we are using HA specific SP for a HA enabled server */ 112 exec @retstat = sp_ha_check_certified 'sp_dropuser', @HA_CERTIFIED 113 if (@retstat != 0) 114 return (1) 115 116 if @@trancount = 0 117 begin 118 set chained off 119 end 120 121 set transaction isolation level 1 122 123 /* 124 ** Only the Database Owner (DBO) or 125 ** Accounts with SA or SSO role can execute it. 126 ** Call proc_role() with the required SA role. 127 */ 128 if (user_id() != 1) 129 begin 130 if (charindex("sa_role", show_role()) = 0 and 131 charindex("sso_role", show_role()) = 0) 132 begin 133 select @dummy = proc_role("sa_role") 134 select @dummy = proc_role("sso_role") 135 return (1) 136 end 137 end 138 139 if (charindex("sa_role", show_role()) > 0) 140 select @dummy = proc_role("sa_role") 141 142 if (charindex("sso_role", show_role()) > 0) 143 select @dummy = proc_role("sso_role") 144 145 /* 146 ** See if the user exists in the database. 147 */ 148 select @uid = uid, @suid = suid from sysusers 149 where name = @name_in_db and 150 ((uid < @@mingroupid and uid != 0) or (uid > @@maxgroupid)) 151 152 /* 153 ** No such user so return. 154 */ 155 if @uid is NULL 156 begin 157 /* 158 ** 17232, "No user with the specified name exists in the current database." 159 */ 160 raiserror 17232 161 return (1) 162 end 163 164 165 166 out_of_HA_checking: 167 168 /* 169 ** Big trouble if dbo (uid = 1) is dropped so check. 170 */ 171 if @uid = 1 172 begin 173 /* 174 ** 17543, "You cannot drop the 'database owner'." 175 */ 176 raiserror 17543 177 return (1) 178 end 179 180 /* 181 ** Check to see if we are dealing with a temporary database. 182 */ 183 select @tempdb_mask = number 184 from master.dbo.spt_values 185 where type = "D3" and name = "TEMPDB STATUS MASK" 186 187 if (db_id() = 2) or exists (select * from master..sysdatabases 188 where dbid = db_id() 189 and (status3 & @tempdb_mask) != 0) 190 begin 191 select @isatempdb = 1 192 end 193 194 /* 195 ** Trouble if guest gets dropped from master or tempdb, so check. 196 */ 197 if (@name_in_db = "guest" and (db_id() = 1 or @isatempdb = 1)) 198 begin 199 /* 200 ** 17544, "You cannot drop the 'guest' user from master or tempdb." 201 */ 202 raiserror 17544 203 return (1) 204 end 205 206 /* 207 ** If the user owns any objects or datatypes and we're not 208 ** forcing the drop, return without doing anything. 209 */ 210 select @objectcount = count(*) 211 from sysobjects 212 where uid = @uid 213 select @typecount = count(*) 214 from systypes 215 where uid = @uid 216 select @grantcount = count(*) 217 from sysprotects 218 where grantor = @uid 219 and id not in (select id from sysobjects 220 where uid = @uid) 221 select @threshcount = count(*) 222 from systhresholds 223 where suid = @suid 224 225 select @spexebndcount = count(*) 226 from sysattributes 227 where (class = 6 and object_type = 'PR' and object_info1 = @uid) 228 229 if @objectcount > 0 or @typecount > 0 or @grantcount > 0 or @threshcount > 0 or 230 @spexebndcount > 0 231 begin 232 if @objectcount > 0 233 begin 234 /* 235 ** 17545, "You cannot drop user because user '%1!' owns objects in database." 236 */ 237 raiserror 17545, @name_in_db 238 select name, type 239 from sysobjects 240 where uid = @uid 241 end 242 243 if @typecount > 0 244 begin 245 /* 246 ** 17546, "You cannot drop user because user '%1!' owns types in database." 247 */ 248 raiserror 17546, @name_in_db 249 select user_type = a.name, physical_type = b.name 250 from systypes a, systypes b 251 where a.uid = @uid 252 and a.type = b.type 253 and b.usertype < 100 254 end 255 256 if @grantcount > 0 257 begin 258 259 /* 260 ** 17431, "true" 261 ** 17432, "false" 262 */ 263 exec sp_getmessage 17431, @msg_true output 264 exec sp_getmessage 17432, @msg_false output 265 /* 17673, "All" */ 266 exec sp_getmessage 17673, @msg_all out 267 268 /* create and populate temp tables so that we can map 269 ** column bit map to names 270 */ 271 create table #sysprotects1(id int, uid int, 272 action smallint, 273 protecttype tinyint, 274 grantor int, number int) 275 276 create table #sysprotects2(id int, col_count smallint) 277 278 insert into #sysprotects2(id, col_count) 279 select id, count(*) 280 from syscolumns 281 group by id 282 283 insert into #sysprotects1(id, uid, action, protecttype, 284 grantor, number) 285 select distinct 286 p.id, p.uid, p.action, p.protecttype, 287 p.grantor, c.number 288 from sysprotects p, master.dbo.spt_values c 289 where (~ isnull(convert(tinyint, substring(p.columns, c.low, 1)), 0) & c.high != 0 290 and c.number <= (select col_count from #sysprotects2 where id = p.id)) 291 and c.type = "P" 292 and c.number <= 1024 293 and p.columns is not null 294 and convert(tinyint, substring(p.columns, 1, 1)) & 0x1 != 0 295 and (convert(tinyint, substring(p.columns, 1, 1)) & 0xfe != 0 296 or substring(p.columns, 2, 1) is not null) 297 298 insert into #sysprotects1(id, uid, action, protecttype, 299 grantor, number) 300 select distinct 301 p.id, p.uid, p.action, p.protecttype, 302 p.grantor, c.number 303 from sysprotects p, master.dbo.spt_values c 304 where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1)) 305 & c.high != 0 306 and c.type = "P" 307 and c.number <= 1024 308 and (p.columns is null 309 or convert(tinyint, substring(p.columns, 1, 1)) & 0x1 = 0 310 or (convert(tinyint, substring(p.columns, 1, 1)) & 0xfe = 0 311 and substring(p.columns, 2, 1) is null)) 312 313 /* Set nocount on to avoid intermingling of output */ 314 set nocount on 315 316 /* 317 ** 17549, "You cannot drop user because user '%1!' owns grantable 318 ** privileges and granted them to other users. The user 319 ** has granted the following privileges:" 320 */ 321 raiserror 17549, @name_in_db 322 print " " 323 324 select grantee = u.name, 325 object = o.name, 326 column = substring(isnull(col_name(p.id, p.number), 327 @msg_all), 1, 10), 328 privilege = s.name 329 from sysusers u, sysobjects o, #sysprotects1 p, 330 master.dbo.spt_values s 331 where p.grantor = @uid and p.uid = u.uid and p.protecttype < 2 332 and p.id = o.id and s.number = p.action 333 and s.name is not NULL 334 and s.type = 'T' 335 order by grantee, object, privilege 336 337 /* 338 ** 18053, "User '%1!' was granted grantable privileges by the 339 ** following users:" 340 */ 341 exec sp_getmessage 18053, @msg output 342 print " " 343 print @msg, @name_in_db 344 345 select distinct grantor = u.name 346 from sysusers u, sysprotects p 347 where p.uid = @uid and u.uid = p.grantor and p.protecttype = 0 348 order by grantor 349 end 350 351 if @threshcount > 0 352 begin 353 /* 354 ** 18033, "You cannot drop user because user '%1!' owns thresholds in database." 355 */ 356 raiserror 18033, @name_in_db 357 select "Segment name" = g.name, "Free pages" = t.free_space 358 from syssegments g, systhresholds t 359 where t.suid = @suid 360 and t.segment = g.segment 361 end 362 363 if @spexebndcount > 0 364 begin 365 select @sp_name = object_cinfo from sysattributes 366 where (class = 6 and object_type = 'PR' and object_info1 = @uid) 367 /* 368 ** 18554, "You cannot drop user '%1!' because stored proc '%2!' 369 ** owned by it is bound to an execution class. Use 370 ** sp_unbindexeclass before dropping user." 371 */ 372 raiserror 18554, @name_in_db, @sp_name 373 end 374 return (1) 375 end 376 377 /* 378 ** Check if userid appears in sysprocesses. In that case somebody 379 ** is still using it so we can not remove it. 380 */ 381 if exists (select * from master.dbo.sysprocesses where uid = @uid 382 and dbid = db_id()) 383 begin 384 /* 385 ** 17915, "Warning: the specified account is currently active." 386 */ 387 exec sp_getmessage 17915, @msg output 388 print @msg 389 390 /* 391 ** 17918, "Nothing changed." 392 */ 393 exec sp_getmessage 17918, @msg output 394 print @msg 395 return (1) 396 end 397 398 /* 399 ** Drop the user. 400 ** Also drop any references to the user in the sysprotects table. 401 ** If anyone is aliased to the user, drop them also. 402 ** 403 ** IMPORTANT: The name rs_logexec is significant. It is used by 404 ** Replication Server. 405 */ 406 407 408 409 begin transaction rs_logexec 410 411 412 413 delete from sysusers 414 where suid = @suid 415 select @err1 = @@error, @userdropped = @@rowcount 416 417 418 419 delete from sysprotects 420 where uid = @uid 421 422 423 424 /* 425 ** Drop any dependent aliases. 426 */ 427 if exists (select * 428 from sysalternates 429 where altsuid = @suid) 430 begin 431 delete from sysalternates 432 where altsuid = @suid 433 434 435 /* 436 ** 17547, "The dependent aliases were also dropped." 437 */ 438 exec sp_getmessage 17547, @msg output 439 print @msg 440 end 441 442 /* 443 ** Delete entries from sysattributes 444 */ 445 delete from sysattributes 446 where object_type = "U" 447 and object = @uid 448 449 450 451 /* Delete entries from sysencryptkeys */ 452 453 if exists (select * from sysencryptkeys 454 where uid = @uid) 455 begin 456 delete from sysencryptkeys 457 where uid = @uid 458 end 459 460 461 462 if @userdropped != 1 463 begin 464 /* 465 ** 19941, "You cannot drop the user because the suid value for 466 ** user '%1!' is not unique in the database." 467 */ 468 raiserror 19941, @name_in_db 469 470 goto clean_all 471 end 472 473 /* 474 ** Write the log record to replicate this invocation 475 ** of the stored procedure. 476 */ 477 if (logexec() != 1) 478 begin 479 /* 480 ** 17756, "The execution of the stored procedure '%1!' 481 ** in database '%2!' was aborted because there 482 ** was an error in writing the replication log 483 ** record." 484 */ 485 select @dbname = db_name() 486 raiserror 17756, "sp_dropuser", @dbname 487 488 rollback transaction rs_logexec 489 return (1) 490 end 491 492 493 494 commit transaction rs_logexec 495 496 /* Update protection timestamp in Resource */ 497 grant all to null 498 499 print_msg: 500 501 /* 502 ** 17548, "User has been dropped from current database." 503 */ 504 exec sp_getmessage 17548, @msg output 505 print @msg 506 507 return (0) 508 509 clean_all: 510 rollback transaction rs_logexec 511 return (1) 512
exec sp_procxmode 'sp_dropuser', 'AnyMode' go Grant Execute on sp_dropuser to public go
RESULT SETS | |
sp_dropuser_rset_005 | |
sp_dropuser_rset_004 | |
sp_dropuser_rset_003 | |
sp_dropuser_rset_002 | |
sp_dropuser_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED reads table sybsystemprocs..syssegments ![]() calls proc sybsystemprocs..sp_ha_check_certified ![]() reads table tempdb..sysobjects (1) ![]() read_writes table sybsystemprocs..sysencryptkeys ![]() reads table master..spt_values (1) ![]() read_writes table sybsystemprocs..sysalternates ![]() reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..syscolumns ![]() reads table master..sysprocesses (1) ![]() reads table sybsystemprocs..systypes ![]() read_writes table tempdb..#sysprotects2 (1) read_writes table sybsystemprocs..sysusers ![]() reads table master..sysdatabases (1) ![]() read_writes table sybsystemprocs..sysattributes ![]() read_writes table tempdb..#sysprotects1 (1) read_writes table sybsystemprocs..sysprotects ![]() reads table sybsystemprocs..systhresholds ![]() calls proc sybsystemprocs..sp_getmessage ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() reads table master..syslanguages (1) ![]() |