Database	Proc	Application	Created	Links
sybsystemprocs	sp_dropexternlogin		14 déc. 14	Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** Omni only
6     **
7     ** Messages for "sp_dropexternlogin"	18319
8     **
9     ** 17260, "Can't run '%1!' from within a transaction."
10    ** 18307, "Server name '%1!' does not exist in sysservers."
11    ** 18319, "There is no external login for server '%1!'."
12    ** 18320, "Only the 'sa' may drop another's external login."
13    ** 18321, "Remote login/alias dropped."
14    ** 17271, "'%1!' is the local server - remote login not applicable."
15    ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'"
16    ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins."
17    ** 18780, "Synchronization will not occur because server '%1!' is the companion server."
18    ** 18782 "Unable to find a server with name '%1!' and id '%2!'."
19    */
20    
21    /* 
22    ** IMPORTANT: Please read the following instructions before
23    **   making changes to this stored procedure.
24    **
25    **	To make this stored procedure compatible with High Availability (HA),
26    **	changes to certain system tables must be propagated 
27    **	to the companion server under some conditions.
28    **	The tables include (but are not limited to):
29    **		syslogins, sysservers, sysattributes, systimeranges,
30    **		sysresourcelimits, sysalternates, sysdatabases,
31    **		syslanguages, sysremotelogins, sysloginroles,
32    **		sysalternates (master DB only), systypes (master DB only),
33    **		sysusers (master DB only), sysprotects (master DB only)
34    **	please refer to the HA documentation for detail.
35    **
36    **	Here is what you need to do: 
37    **	For each insert/update/delete statement, add three sections to
38    **	-- start HA transaction prior to the statement
39    **	-- add the statement
40    **	-- add HA synchronization code to propagate the change to the companion
41    **
42    **	For example, if you are adding 
43    **		insert master.dbo.syslogins ......
44    **	the code should look like:
45    **	1. Before that SQL statement:
46    **		
47    **	2. Now, the SQL statement:
48    **		insert master.dbo.syslogins ......
49    **	3. Add a HA synchronization section right after the SQL statement:
50    **		
51    **
52    **	You may need to do similar change for each built-in function you
53    **	want to add.
54    **
55    **	Finally, add a separate part at a place where it can not
56    **	be reached by the normal execution path:
57    **	clean_all:
58    **		
59    **		return (1)
60    */
61    
62    create or replace procedure sp_dropexternlogin
63        @server varchar(255), /* name of remote server */
64        @loginame varchar(255) = null, /* user's local login */
65        @rolename varchar(255) = null /* role name */
66    as
67        begin
68    
69            declare @msg varchar(1024),
70                @suid int,
71                @srid int,
72                @srvid smallint,
73                @dso_class smallint,
74                @attrib smallint,
75                @action smallint,
76                @dummy int,
77                @HA_CERTIFIED tinyint,
78                @retstat int,
79                @srvclass smallint,
80                @dbname sysname,
81                @log_for_rep int,
82                @nullarg char(1),
83                @status1 int,
84                @status2 int,
85                @gp_enabled int
86    
87    
88            select @srid = - 1, @retstat = 0, @HA_CERTIFIED = 0
89            select @status1 = 1
90            select @status2 = 1
91    
92    
93    
94            /* 
95            ** Check to see if we are using HA specific SP for a HA enabled 
96            ** server. 
97            */
98            exec @retstat = sp_ha_check_certified 'sp_dropexternlogin',
99                @HA_CERTIFIED
100           if (@retstat != 0)
101               return (1)
102   
103           /*
104           ** If we're in a transaction, disallow this since it might make 
105           ** recovery impossible.
106           */
107           if (@@trancount > 0)
108           begin
109               /*
110               ** 17260 Can't run '%1!' from within a transaction.
111               */
112               raiserror 17260, "sp_dropexternlogin"
113               goto raise_error
114           end
115   
116           set chained off
117   
118           set transaction isolation level 1
119   
120           /*
121           ** Validate the server name.
122           */
123           select @srvid = srvid, @srvclass = srvclass
124           from master.dbo.sysservers
125           where srvname = @server
126   
127           if (@@rowcount = 0)
128           begin
129               /*
130               ** 18307, "Server name '%1!' does not exist in sysservers."
131               */
132               raiserror 18307, @server
133               goto raise_error
134           end
135   
136           /*
137           ** This stored procedure should not be replicated when the server is 
138           ** an HADR member.
139           */
140           if exists (select * from master.dbo.sysservers
141                   where (srvname = @server or srvname = @server + "DR")
142                       and srvclass = (select number from master.dbo.spt_values
143                           where type = 'X' and
144                               lower(name) = lower("HADR_MEMBER")))
145           begin
146               select @log_for_rep = 0
147           end
148           else
149           begin
150               select @log_for_rep = 1
151           end
152   
153           /*
154           **  If @rolename was specified, ignore @loginame.
155           */
156           if (@rolename is not null)
157           begin
158               select @loginame = null
159   
160               /*
161               ** Check that the @rolename is valid.
162               */
163               select @srid = srid from master.dbo.syssrvroles
164               where name = @rolename
165   
166               if (@@rowcount = 0)
167               begin
168                   /* 
169                   ** 18342, "Invalid name '%1!'. This role or user does 
170                   ** not exist in this SQL Server."  
171                   */
172                   raiserror 18342, @rolename
173                   goto raise_error
174               end
175           end
176   
177           if (@loginame is null)
178           begin
179               select @suid = - 1
180           end
181           else
182           begin
183               select @suid = suser_id(@loginame)
184   
185               /*
186               ** Check that the @loginame is valid.
187               */
188               if (@suid is null)
189               begin
190                   /*		
191                   ** 18294 "User '%1!' is not a local user -- request 
192                   ** denied."
193                   */
194                   raiserror 18294, @loginame
195                   goto raise_error
196               end
197           end
198   
199           /*
200           ** If granular permissions is not enabled then a user with sa_role 
201           ** or sso_role can add extern logins for another user or for a role.
202           ** If granular permissions is enabled then the permission 
203           ** 'manage any login' is required.  proc_role and proc_auditperm will 
204           ** also do auditing if required. Both will also print error message if 
205           ** required.  
206           */
207   
208           if (@suid != suser_id())
209           begin -- {
210               select @nullarg = NULL
211               execute @status1 = sp_aux_checkroleperm "sa_role",
212                   "manage any remote login", @nullarg,
213                   @gp_enabled output
214               if (@status1 != 0)
215               begin
216                   if (@gp_enabled = 0)
217                   begin
218                       execute @status2 = sp_aux_checkroleperm
219                           "sso_role", @nullarg, @nullarg,
220                           @gp_enabled output
221                       if (@status2 != 0)
222                       begin
223                           select @dummy = proc_role("sa_role")
224                           select @dummy = proc_role("sso_role")
225                           return (1)
226                       end
227                   end
228                   else
229                   begin
230                       select @dummy = proc_auditperm(
231                               "manage any remote login", @status1)
232                       return (1)
233                   end
234               end
235               /* For Auditing */
236               if (@gp_enabled = 0)
237               begin
238                   if (@status1 = 0)
239                   begin
240                       if (proc_role("sa_role") = 0)
241                           return (1)
242                   end
243                   if (@status2 = 0)
244                   begin
245                       if (proc_role("sso_role") = 0)
246                           return (1)
247                   end
248               end
249               else
250               begin
251                   /* send audit record to the audit trail. */
252                   select @dummy = proc_auditperm(
253                           "manage any remote login", @status1)
254               end
255   
256           end -- }
257   
258   
259   
260           begin tran rs_logexec
261   
262   
263   
264           /*
265           ** Delete the remote login.
266           */
267           if ((@srvclass != 12) and (@srvclass != 14) and (@srvclass != 15))
268               select @dso_class = 9
269           else
270               select @dso_class = 21
271   
272           select @attrib = 0
273   
274           delete master.dbo.sysattributes
275           where class = @dso_class and attribute = @attrib and
276               object_info1 = @srvid and object = @suid and
277               (object_info2 = @srid or object_info2 is null)
278           /*
279           ** Check @@rowcount when it works
280           */
281           if (@@rowcount > 0)
282           begin
283               select @action = 3 /* attribute drop */
284   
285   
286               if (@log_for_rep = 1)
287               begin --{
288                   if (logexec(1) != 1)
289                   begin
290                       /*
291                       ** 17756, "The execution of the stored procedure
292                       ** '%1!' in database '%2!' was aborted because 
293                       ** there was an error in writing the replication
294                       ** log record."
295                       */
296                       select @dbname = db_name()
297                       raiserror 17756, "sp_dropexternlogin", @dbname
298   
299                       select @retstat = 1
300                       goto clean_all
301                   end
302               end --}
303   
304               commit tran rs_logexec
305   
306               if (@@error != 0)
307                   goto raise_error
308   
309               /*
310               ** 18321, "Remote login/alias dropped"
311               */
312               exec sp_getmessage 18321, @msg output
313               print @msg
314   
315               /*
316               ** Sync the in-memory RDES with the new values
317               ** in sysattributes.
318               */
319               if (attrib_notify(@dso_class, @attrib, "EL", @suid, @srvid,
320                           @srid, null, null, null, null, null, null, null,
321                           @action) = 0)
322               begin
323                   select @retstat = 1
324               end
325   
326   
327   
328           end
329           else
330           begin
331               /*
332               ** 18319, "There is no external login for server '%1!'."
333               */
334               exec sp_getmessage 18319, @msg output
335               print @msg, @server
336               goto clean_all
337           end
338   
339           return (@retstat)
340   
341   clean_all:
342           rollback tran rs_logexec
343   
344   raise_error:
345   
346   
347           return (1)
348       end
349



exec sp_procxmode 'sp_dropexternlogin', 'AnyMode'
go

Grant Execute on sp_dropexternlogin to public
go

DEFECTS
MCTR 4 Conditional Begin Tran or Commit Tran	304
MINU 4 Unique Index with nullable columns master..sysattributes	master..sysattributes
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type}	143
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	142
QTYP 4 Comparison type mismatch smallint = int	142
QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint	276
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..sysattributes
MGTP 3 Grant to public master..sysservers
MGTP 3 Grant to public master..syssrvroles
MGTP 3 Grant to public sybsystemprocs..sp_dropexternlogin
MNER 3 No Error Check should check return value of exec	312
MNER 3 No Error Check should check return value of exec	334
MUCO 3 Useless Code Useless Begin-End Pair	67
MUCO 3 Useless Code Useless Brackets	100
MUCO 3 Useless Code Useless Brackets	101
MUCO 3 Useless Code Useless Brackets	107
MUCO 3 Useless Code Useless Brackets	127
MUCO 3 Useless Code Useless Brackets	156
MUCO 3 Useless Code Useless Brackets	166
MUCO 3 Useless Code Useless Brackets	177
MUCO 3 Useless Code Useless Brackets	188
MUCO 3 Useless Code Useless Brackets	208
MUCO 3 Useless Code Useless Brackets	214
MUCO 3 Useless Code Useless Brackets	216
MUCO 3 Useless Code Useless Brackets	221
MUCO 3 Useless Code Useless Brackets	225
MUCO 3 Useless Code Useless Brackets	232
MUCO 3 Useless Code Useless Brackets	236
MUCO 3 Useless Code Useless Brackets	238
MUCO 3 Useless Code Useless Brackets	240
MUCO 3 Useless Code Useless Brackets	241
MUCO 3 Useless Code Useless Brackets	243
MUCO 3 Useless Code Useless Brackets	245
MUCO 3 Useless Code Useless Brackets	246
MUCO 3 Useless Code Useless Brackets	267
MUCO 3 Useless Code Useless Brackets	281
MUCO 3 Useless Code Useless Brackets	286
MUCO 3 Useless Code Useless Brackets	288
MUCO 3 Useless Code Useless Brackets	306
MUCO 3 Useless Code Useless Brackets	319
MUCO 3 Useless Code Useless Brackets	339
MUCO 3 Useless Code Useless Brackets	347
QAFM 3 Var Assignment from potentially many rows	163
QISO 3 Set isolation level	118
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: {attribute, object_info2, object_info1, object, class}	275
VNRD 3 Variable is not read @dummy	252
MSUB 2 Subquery Marker	140
MSUB 2 Subquery Marker	142
MTR1 2 Metrics: Comments Ratio Comments: 48%	62
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 26 = 29dec - 5exi + 2	62
MTR3 2 Metrics: Query Complexity Complexity: 124	62