| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_dropmessage | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "@(#) generic/sproc/src/%M% %I% %G%" */ 3 /* generic/sproc/dropmessage 14.2 11/12/91 */ 4 5 /* 6 ** Messages from sysmessages 7 ** 8 ** 17201, "'%1!' is not an official language name from Syslanguages." 9 ** 17202, "Message number %1! does not exist in the %2! language." 10 ** 17210, "Message number must be at least 20000." 11 ** 17220, "Message number %1! does not exist." 12 ** 17221, "Message deleted." 13 ** 17222, "User '%1!' does not have permission to drop message number %2!." 14 ** 17223, "User '%1!' does not have permission to drop message number %2! in the %3! language." 15 ** 17224, "Drop failed. Your curwrite label must match the security label of the message. Check sysusermessages." 16 ** 17756, "The execution of the stored procedure '%1!' in database 17 ** '%2!' was aborted because there was an error in writing the 18 ** replication log record." 19 ** 18293, "Auditing for '%1!' event has failed due to internal error. Contact a user with System Security Officer (SSO) role." 20 */ 21 22 create procedure sp_dropmessage 23 @message_num int, 24 @language varchar(30) = NULL 25 as 26 27 declare @msg varchar(1024) 28 declare @msg2 varchar(255) 29 declare @lang_id smallint 30 declare @privileged smallint 31 declare @returncode smallint 32 declare @procval int 33 34 declare @eventnum int /* event number for add message auditing */ 35 declare @mod_ok_fail int /* successful add message auditing */ 36 declare @uid int /* successful add message auditing */ 37 38 39 select @eventnum = 32 /* event number for add message */ 40 declare @dbname varchar(255) 41 42 43 if @@trancount = 0 44 begin 45 set chained off 46 end 47 48 set transaction isolation level 1 49 50 /* 51 ** Checking "OWN"ership of the message by the user is on by default by 52 ** resetting the "priveleged" flag. This is bypassed if needed by setting it 53 ** to 1 54 */ 55 select @privileged = 0 56 57 /* 58 ** Only the Database Owner (DBO) or 59 ** Accounts with SA role can execute it. 60 ** Call proc_role() with the required SA role. 61 ** Check sa_role first as it cleans up the code. 62 */ 63 if charindex("sa_role", show_role()) > 0 64 begin 65 /* 66 ** If user has sa_role, wether by explicit granting or by being 67 ** SA or DBO we assume that "sa_role" is being used to drop the 68 ** message & thus we want to send an audit record by calling 69 ** proc_role("sa_role") & we set the privileged flag to say that 70 ** this does not have to be the owner of the message being dropped 71 */ 72 select @procval = proc_role("sa_role") 73 select @privileged = 1 74 end 75 else 76 begin 77 /* 78 ** If this is the DBO then set privileged flag to check that 79 ** ownership of the message is not to be checked 80 */ 81 if user_id() = 1 82 begin 83 select @privileged = 1 84 end 85 end 86 87 /* 88 ** Check that language is valid. 89 */ 90 if @language is null 91 select @lang_id = - 1 /* (all) */ 92 else 93 BEGIN 94 execute @returncode = sp_validlang @language 95 if @returncode != 0 96 begin 97 /* Us_english is always valid */ 98 if @language != "us_english" 99 BEGIN 100 /* 101 ** 17201, "'%1!' is not an official language 102 ** name from Syslanguages." 103 */ 104 raiserror 17201, @language 105 return @returncode 106 END 107 108 /* set to us_english */ 109 select @lang_id = NULL 110 end 111 112 else 113 select @lang_id = langid from master.dbo.syslanguages 114 where @language = name 115 END 116 117 if @message_num < 20000 118 begin 119 /* 17210, "Message number must be at least 20000." */ 120 raiserror 17210 121 return (1) 122 end 123 124 /* 125 ** Check that message exists 126 */ 127 if not exists (select * from sysusermessages 128 where error = @message_num) 129 begin 130 /* 17220, "Message number %1! does not exist." */ 131 raiserror 17220, @message_num 132 return (1) 133 end 134 135 136 137 /* 138 ** ... In the Proper language 139 */ 140 if @lang_id != - 1 and not exists (select * from sysusermessages 141 where error = @message_num and langid = @lang_id) 142 begin 143 /* 17202, "Message number %1! does not exist in the %2! language." */ 144 raiserror 17202, @message_num, @language 145 return (1) 146 end 147 148 if (@privileged = 1) 149 BEGIN 150 /* get the owner id of the object */ 151 select @uid = uid from sysusermessages where error = @message_num 152 153 154 155 select @mod_ok_fail = 1 156 /* 157 ** Generate successful audit record. 158 */ 159 select @returncode = ad_hoc_audit(@eventnum, @mod_ok_fail, 160 str(@message_num), db_name(), NULL, 161 suser_name(@uid), 0, NULL 162 163 ) 164 if (@returncode != 0) 165 begin 166 /* 167 ** 18293, "Auditing for '%1!' event has failed due to 168 ** internal error. Contact a user with System Security 169 ** Officer (SSO) role." 170 */ 171 raiserror 18293, @eventnum 172 end 173 174 /* Start the transaction to log the execution of this procedure. 175 ** 176 ** IMPORTANT: The name "rs_logexec is significant and is used by 177 ** Replication Server 178 */ 179 begin transaction rs_logexec 180 181 /* 182 ** Do they want to delete all the messages? 183 */ 184 if @lang_id = - 1 185 begin 186 delete sysusermessages 187 where error = @message_num 188 end 189 /* 190 ** If not, delete only the message with the 191 ** specific language 192 */ 193 else 194 begin 195 delete sysusermessages 196 where error = @message_num and 197 langid = @lang_id 198 end 199 200 /* 201 ** Write the log record to replicate this invocation 202 ** of the stored procedure. 203 */ 204 if (logexec() != 1) 205 begin 206 /* 207 ** 17756, "The execution of the stored procedure '%1!' 208 ** in database '%2!' was aborted because there 209 ** was an error in writing the replication log 210 ** record." 211 */ 212 select @dbname = db_name() 213 raiserror 17756, "sp_dropmessage", @dbname 214 215 rollback transaction rs_logexec 216 return (1) 217 end 218 219 commit transaction 220 221 /* 17221, "Message deleted." */ 222 exec sp_getmessage 17221, @msg output 223 print @msg 224 225 END 226 else 227 /* Only delete the user's specific messages */ 228 BEGIN 229 /* 230 ** Do they want to delete all the messages? 231 */ 232 if @lang_id = - 1 233 begin 234 /* get the owner id of the object */ 235 select @uid = uid from sysusermessages 236 where error = @message_num 237 if exists (select * from sysusermessages 238 where error = @message_num 239 and uid = suser_id()) 240 begin /* the user owns the message */ 241 242 243 select @mod_ok_fail = 1 244 end 245 else /* the use does not own the message */ 246 begin 247 248 select @mod_ok_fail = 2 249 end 250 /* 251 ** Generate successful/fail audit record. 252 */ 253 select @returncode = ad_hoc_audit(@eventnum, 254 @mod_ok_fail, str(@message_num), 255 db_name(), NULL, suser_name(@uid), 256 0, NULL 257 258 ) 259 if (@returncode != 0) 260 begin 261 /* 262 ** 18293, "Auditing for '%1!' event has failed 263 ** due to internal error. Contact a user with 264 ** System Security Officer (SSO) role." 265 */ 266 raiserror 18293, @eventnum 267 end 268 if exists (select * from sysusermessages 269 where error = @message_num 270 and uid = suser_id()) 271 begin 272 273 /* Start the transaction to log the execution 274 ** of this procedure. 275 ** 276 ** IMPORTANT: The name "rs_logexec is 277 ** significant and is used by Replication Server 278 */ 279 begin transaction rs_logexec 280 281 delete sysusermessages 282 where error = @message_num 283 and uid = suser_id() 284 /* 285 ** Write the log record to replicate this invocation 286 ** of the stored procedure. 287 */ 288 if (logexec() != 1) 289 begin 290 /* 291 ** 17756, "The execution of the stored 292 ** procedure '%1!' in database 293 ** '%2!' was aborted because 294 ** there was an error in writing 295 ** the replication log record." 296 */ 297 select @dbname = db_name() 298 raiserror 17756, "sp_dropmessage", @dbname 299 300 rollback transaction rs_logexec 301 return (1) 302 end 303 304 commit transaction 305 306 /* 17221, "Message deleted." */ 307 exec sp_getmessage 17221, @msg output 308 print @msg 309 310 end 311 else 312 begin 313 /* 314 ** 17222 "User '%1!' does not have permission to 315 ** drop message number %2!" 316 */ 317 select @msg2 = user_name() 318 raiserror 17222, @msg2, @message_num 319 end 320 end 321 /* 322 ** If not, delete only the message with the 323 ** specific language 324 */ 325 else 326 begin 327 /* get the owner id of the object */ 328 select @uid = uid from sysusermessages 329 where error = @message_num 330 and langid = @lang_id 331 if exists (select * from sysusermessages 332 where error = @message_num 333 and langid = @lang_id 334 and uid = suser_id()) 335 begin 336 337 select @mod_ok_fail = 1 338 end 339 else 340 begin 341 342 select @mod_ok_fail = 2 343 end 344 /* 345 ** Generate successful/fail audit record. 346 */ 347 select @returncode = ad_hoc_audit(@eventnum, 348 @mod_ok_fail, str(@message_num), 349 db_name(), NULL, suser_name(@uid), 350 0, NULL 351 352 ) 353 if (@returncode != 0) 354 begin 355 /* 356 ** 18293, "Auditing for '%1!' event has failed 357 ** due to internal error. Contact a user with 358 ** System Security Officer (SSO) role." 359 */ 360 raiserror 18293, @eventnum 361 end 362 if exists (select * from sysusermessages 363 where error = @message_num 364 and langid = @lang_id 365 and uid = suser_id()) 366 begin 367 /* Start the transaction to log the execution 368 ** of this procedure. 369 ** 370 ** IMPORTANT: The name "rs_logexec is 371 ** significant and is used by 372 ** Replication Server 373 */ 374 begin transaction rs_logexec 375 376 delete sysusermessages 377 where error = @message_num 378 and langid = @lang_id 379 and uid = suser_id() 380 /* 381 ** Write the log record to replicate this invocation 382 ** of the stored procedure. 383 */ 384 if (logexec() != 1) 385 begin 386 /* 387 ** 17756, "The execution of the stored 388 ** procedure '%1!' in database 389 ** '%2!' was aborted because 390 ** there was an error in writing 391 ** the replication log record." 392 */ 393 select @dbname = db_name() 394 raiserror 17756, "sp_dropmessage", @dbname 395 396 rollback transaction rs_logexec 397 return (1) 398 end 399 400 commit transaction 401 402 /* 17221, "Message deleted." */ 403 exec sp_getmessage 17221, @msg output 404 print @msg 405 406 end 407 else 408 begin 409 /* 410 ** 17223 "User '%1!' does not have permission to 411 ** drop message number %2! in the %3! language." 412 */ 413 select @msg2 = user_name() 414 raiserror 17223, @msg2, @message_num, @language 415 return (1) 416 end 417 end 418 END 419 return (0) 420 421
exec sp_procxmode 'sp_dropmessage', 'AnyMode' go Grant Execute on sp_dropmessage to public go
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_getmessage  reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1)  reads table master..syslanguages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) calls proc sybsystemprocs..sp_validlang read_writes table sybsystemprocs..sysusermessages |
