Database | Proc | Application | Created | Links |
sybsystemprocs | sp_remoteoption ![]() | ![]() | 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/remoteoption */ 4 5 /* 6 ** Messages for "sp_remoteoption" 17770 7 ** 8 ** 17513, "There is no remote user '%1!' mapped to local user '%2!' from 9 ** the remote server '%3!'." 10 ** 17260, "Can't run %1! from within a transaction." 11 ** 17770, "Settable remote login options." 12 ** 17771, "There is no remote user '%1!' mapped to local user '%2!' on 13 ** remote server '%3!'." 14 ** 17772, "Usage: sp_remoteoption [remoteserver, loginame, remotename, 15 ** optname, {true | false}]" 16 ** 17773, "Remote login option doesn't exist or can't be set by user." 17 ** 17774, "Run sp_remoteoption with no parameters to see options." 18 ** 17775, "Remote login option is not unique." 19 ** 17777, "Option '%1!' turned on." 20 ** 17778, "Option '%1!' turned off." 21 ** 17431, "true" 22 ** 17432, "false" 23 ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'" 24 ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins." 25 ** 18780, "Synchronization will not occur because server '%1!' is the companion server." 26 ** 18782 "Unable to find a server with name '%1!' and id '%2!'." 27 */ 28 29 /* 30 ** IMPORTANT: Please read the following instructions before 31 ** making changes to this stored procedure. 32 ** 33 ** To make this stored procedure compatible with High Availability (HA), 34 ** changes to certain system tables must be propagated 35 ** to the companion server under some conditions. 36 ** The tables include (but are not limited to): 37 ** syslogins, sysservers, sysattributes, systimeranges, 38 ** sysresourcelimits, sysalternates, sysdatabases, 39 ** syslanguages, sysremotelogins, sysloginroles, 40 ** sysalternates (master DB only), systypes (master DB only), 41 ** sysusers (master DB only), sysprotects (master DB only) 42 ** please refer to the HA documentation for detail. 43 ** 44 ** Here is what you need to do: 45 ** For each insert/update/delete statement, add three sections to 46 ** -- start HA transaction prior to the statement 47 ** -- add the statement 48 ** -- add HA synchronization code to propagate the change to the companion 49 ** 50 ** For example, if you are adding 51 ** insert master.dbo.syslogins ...... 52 ** the code should look like: 53 ** 1. Before that SQL statement: 54 ** 55 ** 2. Now, the SQL statement: 56 ** insert master.dbo.syslogins ...... 57 ** 3. Add a HA synchronization section right after the SQL statement: 58 ** 59 ** 60 ** You may need to do similar change for each built-in function you 61 ** want to add. 62 ** 63 ** Finally, add a separate part at a place where it can not 64 ** be reached by the normal execution path: 65 ** clean_all: 66 ** 67 ** return (1) 68 */ 69 70 create procedure sp_remoteoption 71 @remoteserver varchar(255) = NULL, /* server name to change */ 72 @loginame varchar(255) = NULL, /* user's remote name */ 73 @remotename varchar(255) = NULL, /* user's local user name */ 74 @optname varchar(20) = NULL, /* option name to turn on/off */ 75 @optvalue varchar(10) = NULL /* true or false */ 76 as 77 78 declare @statvalue smallint /* number of option */ 79 declare @optcount int /* number of options like @optname */ 80 declare @msg varchar(1024) 81 declare @suid int 82 declare @rname varchar(255) 83 declare @lname varchar(255) 84 declare @true varchar(10) 85 declare @false varchar(10) 86 declare @sptlang int 87 declare @whichone int /* Which language */ 88 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 89 declare @retstat int 90 91 92 select @HA_CERTIFIED = 0 93 94 95 96 97 /* check to see if we are using HA specific SP for a HA enabled server */ 98 exec @retstat = sp_ha_check_certified 'sp_remoteoption', @HA_CERTIFIED 99 if (@retstat != 0) 100 return (1) 101 102 if @@trancount = 0 103 begin 104 set chained off 105 end 106 107 set transaction isolation level 1 108 109 /* check if user has sso role, proc_role will also do auditing 110 ** if required. proc_role will also print error message if required. 111 */ 112 113 if (proc_role("sso_role") = 0) 114 return (1) 115 116 select @sptlang = 0, @whichone = 0 117 118 if @@langid != 0 119 begin 120 if not exists ( 121 select * from master.dbo.sysmessages where error 122 between 17070 and 17079 123 and langid = @@langid) 124 select @sptlang = 0 125 end 126 127 /* 128 ** If no @server given, just list the possible remote login options. 129 ** Only certain status bits may be set or cleared. 130 ** settable not settable 131 ** ------------------------------ -------------------------- 132 ** trusted (1) 133 */ 134 if @remoteserver is null 135 begin 136 /* 137 ** 17770, "Settable remote login options." 138 */ 139 exec sp_getmessage 17770, @msg output 140 print @msg 141 if @sptlang = 0 142 exec sp_autoformat @fulltabname = "master.dbo.spt_values", 143 @selectlist = "remotelogin_option = name", 144 @whereclause = "where type = 'F' and number in (1, 1) and number > 0", 145 @orderby = "order by name" 146 else 147 begin 148 select remotelogin_option = name, description 149 into #remoption1rs 150 from master.dbo.spt_values, master.dbo.sysmessages 151 where type = "F" 152 and number in (1, 1) 153 and number > 0 154 and msgnum = error 155 and error between 17070 and 17079 156 and langid = @sptlang 157 exec sp_autoformat @fulltabname = #remoption1rs, 158 @orderby = "order by remotelogin_option" 159 drop table #remoption1rs 160 end 161 return (0) 162 end 163 164 /* 165 ** If @loginame is NULL then we want to set @suid = -1. Otherwise get 166 ** it real value. 167 */ 168 if @loginame is null 169 select @suid = - 1 170 else select @suid = suser_id(@loginame) 171 172 /* 173 ** Verify the server name, local, and remote names. 174 */ 175 if not exists (select * 176 from master.dbo.sysremotelogins r, master.dbo.sysservers s 177 where r.remoteserverid = s.srvid 178 and s.srvname = @remoteserver 179 and r.remoteusername = @remotename 180 and r.suid = @suid) 181 begin 182 /* 183 ** 17771, "There is no remote user '%1!' mapped to local user '%2!' on 184 ** remote server '%3!'." 185 */ 186 select @rname = isnull(@remotename, "NULL") 187 select @lname = isnull(@loginame, "NULL") 188 raiserror 17771, @rname, @lname, @remoteserver 189 return (1) 190 end 191 192 /* 193 ** Check remaining parameters. 194 */ 195 /* 17431, "true" */ 196 exec sp_getmessage 17431, @true out 197 /* 17432, "false" */ 198 exec sp_getmessage 17432, @false out 199 if @optname is NULL or lower(@optvalue) not in 200 ("true", "false", @true, @false) or @optvalue is null 201 begin 202 /* 203 ** 17772, "Usage: sp_remoteoption [remoteserver, loginame, remotename, 204 ** optname, {true | false}]" 205 */ 206 raiserror 17772 207 return (1) 208 end 209 210 /* 211 ** Use @optname and try to find the right option. 212 ** If there isn't just one, print appropriate diagnostics and return. 213 */ 214 select @optcount = count(*) 215 from master.dbo.spt_values 216 where name like "%" + @optname + "%" and type = "F" 217 and number in (1, 1) 218 and number > 0 219 220 /* 221 ** If option not found, and language not english, then check some more 222 */ 223 if @optcount = 0 and @sptlang != 0 224 begin 225 select @optcount = count(*) 226 from master.dbo.spt_values, master.dbo.sysmessages 227 where description like "%" + @optname + "%" and type = "F" 228 and number in (1, 1) 229 and number > 0 230 and error between 17070 and 17079 231 and msgnum = error 232 and langid = @sptlang 233 select @whichone = 1 234 end 235 236 /* 237 ** If no option, show the user what the options are. 238 */ 239 if @optcount = 0 240 begin 241 /* 242 ** 17773, "Remote login option doesn't exist or can't be set by user." 243 */ 244 raiserror 17773 245 /* 246 ** 17774, "Run sp_remoteoption with no parameters to see options." 247 */ 248 exec sp_getmessage 17774, @msg output 249 print @msg 250 return (1) 251 end 252 253 /* 254 ** If more than one option like @optname, show the duplicates and return. 255 */ 256 if @optcount > 1 257 begin 258 /* 259 ** 17775, "Remote login option is not unique." 260 */ 261 raiserror 17775 262 263 if @sptlang = 0 264 begin 265 select duplicate_option = name 266 into #remoption2rs 267 from master.dbo.spt_values 268 where name like "%" + @optname + "%" 269 and type = "F" 270 and number in (1, 1) 271 and number > 0 272 exec sp_autoformat @fulltabname = #remoption2rs 273 drop table #remoption2rs 274 end 275 else 276 begin 277 select duplicate_option = name, description 278 into #remoption3rs 279 from master.dbo.spt_values, master.dbo.sysmessages 280 where 281 (name like "%" + @optname + "%" 282 or description like "%" + @optname + "%") 283 and type = "F" 284 and number in (1, 1) 285 and number > 0 286 and error between 17070 and 17079 287 and msgnum = error 288 and langid = @sptlang 289 exec sp_autoformat @fulltabname = #remoption3rs 290 drop table #remoption3rs 291 end 292 return (1) 293 end 294 295 /* 296 ** If we're in a transaction, disallow this since it might make recovery 297 ** impossible. 298 */ 299 if @@trancount > 0 300 begin 301 /* 302 ** 17260, "Can't run %1! from within a transaction." 303 */ 304 raiserror 17260, "sp_remoteoption" 305 return (1) 306 end 307 308 /* 309 ** Get the number which is the bit value to set 310 */ 311 if @whichone = 0 312 select @statvalue = number 313 from master.dbo.spt_values 314 where name like "%" + @optname + "%" and type = "F" 315 and number in (1, 1) 316 and number > 0 317 else 318 select @statvalue = number 319 from master.dbo.spt_values, master.dbo.sysmessages 320 where description like "%" + @optname + "%" 321 and type = "F" 322 and number in (1, 1) 323 and number > 0 324 and error between 17070 and 17079 325 and msgnum = error 326 and langid = @sptlang 327 328 329 330 /* 331 ** Now update sysremotelogins. 332 */ 333 if lower(@optvalue) in ("true", @true) 334 begin 335 update master.dbo.sysremotelogins 336 set status = status | @statvalue 337 from master.dbo.sysremotelogins r, master.dbo.sysservers s 338 where r.remoteserverid = s.srvid 339 and s.srvname = @remoteserver 340 and r.remoteusername = @remotename 341 and r.suid = @suid 342 343 344 345 /* 346 ** 17777, "Option '%1!' turned on." 347 */ 348 exec sp_getmessage 17777, @msg output 349 print @msg, @optname 350 351 352 return (0) 353 end 354 355 /* 356 ** We want to turn it off. 357 */ 358 else 359 begin 360 update master.dbo.sysremotelogins 361 set status = status & ~ @statvalue 362 from master.dbo.sysremotelogins r, master.dbo.sysservers s 363 where r.remoteserverid = s.srvid 364 and s.srvname = @remoteserver 365 and r.remoteusername = @remotename 366 and r.suid = @suid 367 368 369 370 /* 371 ** 17778, "Option '%1!' turned off." 372 */ 373 exec sp_getmessage 17778, @msg output 374 print @msg, @optname 375 376 377 378 return (0) 379 end 380 381 return (0) 382 383 384
exec sp_procxmode 'sp_remoteoption', 'AnyMode' go Grant Execute on sp_remoteoption to public go
DEFECTS | |
![]() | 381 |
![]() | master..sysmessages |
![]() | master..sysremotelogins |
![]() | 312 |
![]() | 318 |
![]() | 157 |
![]() | 272 |
![]() | 289 |
![]() (remoteserverid, remoteusername) Intersection: {remoteusername} | 179 |
![]() (remoteserverid, remoteusername) Intersection: {remoteusername} | 340 |
![]() (remoteserverid, remoteusername) Intersection: {remoteusername} | 365 |
![]() | 123 |
![]() | 123 |
![]() | 156 |
![]() | 156 |
![]() | 232 |
![]() | 232 |
![]() | 288 |
![]() | 288 |
![]() | 326 |
![]() | 326 |
![]() | master..spt_values |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 139 |
![]() | 142 |
![]() | 148 |
![]() | 157 |
![]() | 196 |
![]() | 198 |
![]() | 248 |
![]() | 265 |
![]() | 272 |
![]() | 277 |
![]() | 289 |
![]() | 335 |
![]() | 348 |
![]() | 360 |
![]() | 373 |
![]() | 99 |
![]() | 100 |
![]() | 113 |
![]() | 114 |
![]() | 161 |
![]() | 189 |
![]() | 207 |
![]() | 250 |
![]() | 292 |
![]() | 305 |
![]() | 352 |
![]() | 378 |
![]() | 381 |
![]() | 360 |
![]() | 312 |
![]() | 318 |
![]() | 148 |
![]() | 265 |
![]() | 277 |
![]() | 107 |
![]() | 150 |
![]() | 176 |
![]() | 226 |
![]() | 279 |
![]() | 319 |
![]() | 337 |
![]() | 362 |
![]() | 148 |
![]() | 148 |
![]() | 150 |
![]() | 150 |
![]() | 151 |
![]() | 152 |
![]() | 153 |
![]() | 154 |
![]() | 154 |
![]() | 155 |
![]() | 156 |
![]() | 226 |
![]() | 226 |
![]() | 227 |
![]() | 227 |
![]() | 228 |
![]() | 229 |
![]() | 230 |
![]() | 231 |
![]() | 231 |
![]() | 232 |
![]() | 277 |
![]() | 277 |
![]() | 279 |
![]() | 279 |
![]() | 281 |
![]() | 282 |
![]() | 283 |
![]() | 284 |
![]() | 285 |
![]() | 286 |
![]() | 287 |
![]() | 287 |
![]() | 288 |
![]() | 318 |
![]() | 319 |
![]() | 319 |
![]() | 320 |
![]() | 321 |
![]() | 322 |
![]() | 323 |
![]() | 324 |
![]() | 325 |
![]() | 325 |
![]() | 326 |
![]() | 336 |
![]() | 361 |
![]() (error, dlevel, langid) Intersection: {error, langid} | 121 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 154 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 231 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 287 |
![]() (error, dlevel, langid) Intersection: {error} Uncovered: [dlevel] | 325 |
![]() | 120 |
![]() | 175 |
![]() | 70 |
![]() | 70 |
![]() | 70 |
![]() | 148 |
![]() | 175 |
![]() | 225 |
![]() | 277 |
![]() | 318 |
![]() | 335 |
![]() | 360 |
DEPENDENCIES |
PROCS AND TABLES USED writes table tempdb..#remoption3rs (1) reads table master..sysmessages (1) ![]() reads table master..sysservers (1) ![]() calls proc sybsystemprocs..sp_ha_check_certified ![]() reads table tempdb..sysobjects (1) ![]() writes table tempdb..#remoption1rs (1) calls proc sybsystemprocs..sp_getmessage ![]() reads table master..sysmessages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..syslanguages (1) ![]() read_writes table master..sysremotelogins (1) ![]() reads table master..spt_values (1) ![]() calls proc sybsystemprocs..sp_autoformat ![]() read_writes table tempdb..#colinfo_af (1) calls proc sybsystemprocs..sp_namecrack ![]() reads table tempdb..systypes (1) ![]() calls proc sybsystemprocs..sp_autoformat ![]() reads table master..systypes (1) ![]() reads table master..syscolumns (1) ![]() reads table tempdb..syscolumns (1) ![]() writes table tempdb..#remoption2rs (1) |