DatabaseProcApplicationCreatedLinks
sybsystemprocssp_remoteoption  31 Aug 14Defects 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
 MURC 6 Unreachable Code 381
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MINU 4 Unique Index with nullable columns master..sysremotelogins master..sysremotelogins
 MTYP 4 Assignment type mismatch @statvalue: smallint = int 312
 MTYP 4 Assignment type mismatch @statvalue: smallint = int 318
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 157
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 272
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 289
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
179
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
340
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysremotelogins.csysremotelogins unique clustered
(remoteserverid, remoteusername)
Intersection: {remoteusername}
365
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 123
 QTYP 4 Comparison type mismatch smallint = int 123
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 156
 QTYP 4 Comparison type mismatch smallint = int 156
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 232
 QTYP 4 Comparison type mismatch smallint = int 232
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 288
 QTYP 4 Comparison type mismatch smallint = int 288
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 326
 QTYP 4 Comparison type mismatch smallint = int 326
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public master..sysremotelogins  
 MGTP 3 Grant to public master..sysservers  
 MGTP 3 Grant to public sybsystemprocs..sp_remoteoption  
 MNER 3 No Error Check should check return value of exec 139
 MNER 3 No Error Check should check return value of exec 142
 MNER 3 No Error Check should check @@error after select into 148
 MNER 3 No Error Check should check return value of exec 157
 MNER 3 No Error Check should check return value of exec 196
 MNER 3 No Error Check should check return value of exec 198
 MNER 3 No Error Check should check return value of exec 248
 MNER 3 No Error Check should check @@error after select into 265
 MNER 3 No Error Check should check return value of exec 272
 MNER 3 No Error Check should check @@error after select into 277
 MNER 3 No Error Check should check return value of exec 289
 MNER 3 No Error Check should check @@error after update 335
 MNER 3 No Error Check should check return value of exec 348
 MNER 3 No Error Check should check @@error after update 360
 MNER 3 No Error Check should check return value of exec 373
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 250
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 352
 MUCO 3 Useless Code Useless Brackets 378
 MUCO 3 Useless Code Useless Brackets 381
 MUOT 3 Updates outside transaction 360
 QAFM 3 Var Assignment from potentially many rows 312
 QAFM 3 Var Assignment from potentially many rows 318
 QCTC 3 Conditional Table Creation 148
 QCTC 3 Conditional Table Creation 265
 QCTC 3 Conditional Table Creation 277
 QISO 3 Set isolation level 107
 QNAJ 3 Not using ANSI Inner Join 150
 QNAJ 3 Not using ANSI Inner Join 176
 QNAJ 3 Not using ANSI Inner Join 226
 QNAJ 3 Not using ANSI Inner Join 279
 QNAJ 3 Not using ANSI Inner Join 319
 QNAJ 3 Not using ANSI Inner Join 337
 QNAJ 3 Not using ANSI Inner Join 362
 QNUA 3 Should use Alias: Column description should use alias sysmessages 148
 QNUA 3 Should use Alias: Column name should use alias spt_values 148
 QNUA 3 Should use Alias: Table master..spt_values 150
 QNUA 3 Should use Alias: Table master..sysmessages 150
 QNUA 3 Should use Alias: Column type should use alias spt_values 151
 QNUA 3 Should use Alias: Column number should use alias spt_values 152
 QNUA 3 Should use Alias: Column number should use alias spt_values 153
 QNUA 3 Should use Alias: Column error should use alias sysmessages 154
 QNUA 3 Should use Alias: Column msgnum should use alias spt_values 154
 QNUA 3 Should use Alias: Column error should use alias sysmessages 155
 QNUA 3 Should use Alias: Column langid should use alias sysmessages 156
 QNUA 3 Should use Alias: Table master..spt_values 226
 QNUA 3 Should use Alias: Table master..sysmessages 226
 QNUA 3 Should use Alias: Column description should use alias sysmessages 227
 QNUA 3 Should use Alias: Column type should use alias spt_values 227
 QNUA 3 Should use Alias: Column number should use alias spt_values 228
 QNUA 3 Should use Alias: Column number should use alias spt_values 229
 QNUA 3 Should use Alias: Column error should use alias sysmessages 230
 QNUA 3 Should use Alias: Column error should use alias sysmessages 231
 QNUA 3 Should use Alias: Column msgnum should use alias spt_values 231
 QNUA 3 Should use Alias: Column langid should use alias sysmessages 232
 QNUA 3 Should use Alias: Column description should use alias sysmessages 277
 QNUA 3 Should use Alias: Column name should use alias spt_values 277
 QNUA 3 Should use Alias: Table master..spt_values 279
 QNUA 3 Should use Alias: Table master..sysmessages 279
 QNUA 3 Should use Alias: Column name should use alias spt_values 281
 QNUA 3 Should use Alias: Column description should use alias sysmessages 282
 QNUA 3 Should use Alias: Column type should use alias spt_values 283
 QNUA 3 Should use Alias: Column number should use alias spt_values 284
 QNUA 3 Should use Alias: Column number should use alias spt_values 285
 QNUA 3 Should use Alias: Column error should use alias sysmessages 286
 QNUA 3 Should use Alias: Column error should use alias sysmessages 287
 QNUA 3 Should use Alias: Column msgnum should use alias spt_values 287
 QNUA 3 Should use Alias: Column langid should use alias sysmessages 288
 QNUA 3 Should use Alias: Column number should use alias spt_values 318
 QNUA 3 Should use Alias: Table master..spt_values 319
 QNUA 3 Should use Alias: Table master..sysmessages 319
 QNUA 3 Should use Alias: Column description should use alias sysmessages 320
 QNUA 3 Should use Alias: Column type should use alias spt_values 321
 QNUA 3 Should use Alias: Column number should use alias spt_values 322
 QNUA 3 Should use Alias: Column number should use alias spt_values 323
 QNUA 3 Should use Alias: Column error should use alias sysmessages 324
 QNUA 3 Should use Alias: Column error should use alias sysmessages 325
 QNUA 3 Should use Alias: Column msgnum should use alias spt_values 325
 QNUA 3 Should use Alias: Column langid should use alias sysmessages 326
 QNUA 3 Should use Alias: Column status should use alias r 336
 QNUA 3 Should use Alias: Column status should use alias r 361
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
121
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
154
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
231
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
287
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
325
 MSUB 2 Subquery Marker 120
 MSUB 2 Subquery Marker 175
 MTR1 2 Metrics: Comments Ratio Comments: 46% 70
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 55 = 62dec - 9exi + 2 70
 MTR3 2 Metrics: Query Complexity Complexity: 180 70
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, sv=master..spt_values} 0 148
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 175
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, sv=master..spt_values} 0 225
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, sv=master..spt_values} 0 277
 PRED_QUERY_COLLECTION 2 {m=master..sysmessages, sv=master..spt_values} 0 318
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 335
 PRED_QUERY_COLLECTION 2 {r=master..sysremotelogins, s=master..sysservers} 0 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)