DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropalias  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_dropalias"          17480
6     **
7     ** 17231, "No login with the specified name exists."
8     ** 17480, "Alias user dropped."
9     ** 17481, "No alias for specified user exists."
10    ** 17756, "The execution of the stored procedure '%1!' in database
11    **         '%2!' was aborted because there was an error in writing the
12    **         replication log record."
13    ** 18337, "Setting curwrite label to data_low for deleting from sysalternates
14    **	   table failed."
15    ** 18790, "You cannot drop the alias for login '%1!' because '%2!' owns
16    **	   objects in the database."
17    ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'"
18    ** 18778, "A login with Login name '%1!' AND login id '%2!' could not be found in syslogins."
19    ** 18806, "You cannot drop the alias for login '%1!' because '%2!' owns
20    **	   thresholds in the database."
21    ** 18935, "Warning: You have forced the drop of the alias for login '%1!'
22    **	   which owns objects in the database. This may result in errors when
23    **	   those objects are accessed from or contain references to another
24    **	   database."
25    */
26    
27    /* 
28    ** IMPORTANT: Please read the following instructions before
29    **   making changes to this stored procedure.
30    **
31    **	To make this stored procedure compatible with High Availability (HA),
32    **	changes to certain system tables must be propagated 
33    **	to the companion server under some conditions.
34    **	The tables include (but are not limited to):
35    **		syslogins, sysservers, sysattributes, systimeranges,
36    **		sysresourcelimits, sysalternates, sysdatabases,
37    **		syslanguages, sysremotelogins, sysloginroles,
38    **		sysalternates (master DB only), systypes (master DB only),
39    **		sysusers (master DB only), sysprotects (master DB only)
40    **	please refer to the HA documentation for detail.
41    **
42    **	Here is what you need to do: 
43    **	For each insert/update/delete statement, add three sections to
44    **	-- start HA transaction prior to the statement
45    **	-- add the statement
46    **	-- add HA synchronization code to propagate the change to the companion
47    **
48    **	For example, if you are adding 
49    **		insert master.dbo.syslogins ......
50    **	the code should look like:
51    **	1. Before that SQL statement:
52    **		
53    **	2. Now, the SQL statement:
54    **		insert master.dbo.syslogins ......
55    **	3. Add a HA synchronization section right after the SQL statement:
56    **		
57    **
58    **	You may need to do similar change for each built-in function you
59    **	want to add.
60    **
61    **	After that, you need to add a separate part at a place where it can not
62    **	be reached by the normal execution path:
63    **	clean_all:
64    **		
65    **		return (1)
66    ** 18790, "You cannot drop the alias for login '%1!' because '%2!' owns objects in the database."
67    */
68    
69    create or replace procedure sp_dropalias
70        @loginame varchar(255), /* account name of the user with the alias */
71        @option varchar(30) = NULL /* 'force' to force dropping the alias, even if
72    				** there are objects created by the alias.
73    				*/
74    as
75    
76        declare @msg varchar(1024)
77        declare @dbname varchar(255)
78        declare @suid int /* suid of the user */
79        declare @objectcount int /* count of objects owned by login */
80        declare @threshcount int /* count of thresholds owned by login */
81        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
82        declare @retstat int
83        declare @dummy int
84        declare @status1 int
85        declare @status2 int
86        declare @nullarg varchar(1)
87        declare @gp_enabled int
88    
89        select @HA_CERTIFIED = 0
90        select @status1 = 1
91        select @status2 = 1
92        select @gp_enabled = 0
93    
94    
95    
96        /* check to see if we are using HA specific SP for a HA enabled server */
97        exec @retstat = sp_ha_check_certified 'sp_dropalias', @HA_CERTIFIED
98        if (@retstat != 0)
99            return (1)
100   
101       if @@trancount = 0
102       begin
103           set chained off
104       end
105   
106       set transaction isolation level 1
107   
108       /*
109       **  Only the Database Owner (DBO) or
110       **  Accounts with SA or SSO role can execute it.
111       **  If the user has SA role, it's uid will
112       **  be DBO uid (1). If granular permissions is enabled then
113       **  users with 'manage any user' permission can execute it.
114       */
115       select @dbname = db_name()
116       select @nullarg = NULL
117   
118       execute @retstat = sp_aux_checkroleperm "dbo",
119           "manage any user", @dbname, @gp_enabled output
120       if (@retstat != 0)
121       begin
122           if (@gp_enabled = 0)
123           begin
124               execute @status1 = sp_aux_checkroleperm "sso_role",
125                   @nullarg, @dbname, @gp_enabled output
126   
127               if (@status1 != 0)
128                   execute @status2 = sp_aux_checkroleperm "sa_role",
129                       @nullarg, @dbname, @gp_enabled output
130               if (@status1 != 0 and @status2 != 0)
131               begin
132                   /* 
133                   ** proc_role() will raise permission errors
134                   ** and send audit records to the audit trail
135                   */
136                   select @dummy = proc_role("sa_role")
137                   select @dummy = proc_role("sso_role")
138                   return (1)
139               end
140           end
141           else
142           begin
143               /* Call proc_perm here to do auditing and error message. */
144               select @dummy = proc_auditperm("manage any user",
145                       @retstat, @dbname)
146               return (1)
147           end
148       end
149   
150       /* 
151       ** Send apropriate audit records, already determined user has one
152       ** of the roles or the permission. 
153       */
154       if (@gp_enabled = 0)
155       begin
156           if (@status1 = 0)
157               select @dummy = proc_role("sso_role")
158           if (@status2 = 0)
159               select @dummy = proc_role("sa_role")
160       end
161       else
162       begin
163           select @dummy = proc_auditperm("manage any user",
164                   @retstat, @dbname)
165       end
166   
167       /*
168       **  Check to make sure that the @loginame has an account.
169       */
170       select @suid = suser_id(@loginame)
171       if @suid is NULL
172       begin
173           /*
174           ** 17231, "No login with the specified name exists."
175           */
176           raiserror 17231
177           return (1)
178       end
179   
180       /*
181       ** If the user owns any objects return after raising error. 
182       */
183       select @objectcount = count(*)
184       from sysobjects
185       where loginame = @loginame
186   
187       if @objectcount > 0
188       begin
189           if @option = 'force'
190           begin
191               /* 18935, "Warning: You have forced the drop of the alias for
192               ** login '%1!' which owns objects in the database. This
193               ** may result in errors when those objects are accessed
194               ** from or contain references to another database."
195               */
196               exec sp_getmessage 18935, @msg output
197               print @msg, @loginame
198           end
199           else
200           begin
201               /* 18790, "You cannot drop the alias for login '%1!' because
202               ** '%2!' owns objects in the database."
203               */
204               raiserror 18790, @loginame, @loginame
205               select name, type from sysobjects
206               where loginame = @loginame
207               return (1)
208           end
209   
210       end
211       /*
212       ** If the user owns any thresholds return after raising error.
213       */
214       select @threshcount = count(*)
215       from systhresholds
216       where suid = @suid
217   
218       if @threshcount > 0
219       begin
220           /* 18806, "You cannot drop the alias for login '%1!' because
221           **	'%2!' owns thresholds in the database."
222           */
223           raiserror 18806, @loginame, @loginame
224           select "Segment name" = g.name, "Free pages" = t.free_space
225           from syssegments g, systhresholds t
226           where t.suid = @suid
227               and t.segment = g.segment
228           return (1)
229       end
230   
231   
232   
233       /*
234       **  Delete the alias, if any, from sysalternates.
235       */
236   
237       /* 
238       ** This transaction also writes a log record for replicating the
239       ** invocation of this procedure. If logexec() fails, the transaction
240       ** is aborted.
241       **
242       ** IMPORTANT: The name rs_logexec is significant and is used by
243       ** Replication Server.
244       */
245       begin transaction rs_logexec
246   
247   
248   
249       delete from sysalternates
250       where suid = suser_id(@loginame)
251   
252       /*
253       **  If nothing happened (rowcount = 0), there was no alias.
254       */
255       if (@@rowcount = 0)
256       begin
257           /*
258           ** 17481, "No alias for specified user exists."
259           */
260           raiserror 17481
261   
262           rollback transaction rs_logexec
263           return (1)
264       end
265   
266       /*
267       ** Write the log record to replicate this invocation 
268       ** of the stored procedure.
269       */
270       if (logexec() != 1)
271       begin
272           /*
273           ** 17756, "The execution of the stored procedure '%1!'
274           ** 	   in database '%2!' was aborted because there
275           ** 	   was an error in writing the replication log
276           **	   record."
277           */
278           select @dbname = db_name()
279           raiserror 17756, "sp_dropalias", @dbname
280   
281           rollback transaction rs_logexec
282           return (1)
283       end
284   
285   
286   
287       commit transaction rs_logexec
288   
289       /* Update protection timestamp in Resource */
290       grant all to null
291   
292       /*
293       ** 17480, "Alias user dropped."
294       */
295       exec sp_getmessage 17480, @msg output
296       print @msg
297   
298       return (0)
299   
300   clean_all:
301       rollback transaction rs_logexec
302       return (1)
303   


exec sp_procxmode 'sp_dropalias', 'AnyMode'
go

Grant Execute on sp_dropalias to public
go
RESULT SETS
sp_dropalias_rset_002
sp_dropalias_rset_001

DEFECTS
 MURC 6 Unreachable Code 300
 MURC 6 Unreachable Code 301
 MURC 6 Unreachable Code 302
 QJWI 5 Join or Sarg Without Index 227
 TNOI 4 Table with no index sybsystemprocs..syssegments sybsystemprocs..syssegments
 TNOI 4 Table with no index sybsystemprocs..systhresholds sybsystemprocs..systhresholds
 MGTP 3 Grant to public sybsystemprocs..sp_dropalias  
 MGTP 3 Grant to public sybsystemprocs..sysalternates  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..syssegments  
 MGTP 3 Grant to public sybsystemprocs..systhresholds  
 MNER 3 No Error Check should check return value of exec 196
 MNER 3 No Error Check should check @@error after delete 249
 MNER 3 No Error Check should check return value of exec 295
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 263
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 282
 MUCO 3 Useless Code Useless Brackets 298
 MUCO 3 Useless Code Useless Brackets 302
 QCRS 3 Conditional Result Set 205
 QCRS 3 Conditional Result Set 224
 QISO 3 Set isolation level 106
 QNAJ 3 Not using ANSI Inner Join 225
 VNRD 3 Variable is not read @dummy 163
 MRST 2 Result Set Marker 205
 MRST 2 Result Set Marker 224
 MTR1 2 Metrics: Comments Ratio Comments: 61% 69
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 17dec - 8exi + 2 69
 MTR3 2 Metrics: Query Complexity Complexity: 107 69
 PRED_QUERY_COLLECTION 2 {s=sybsystemprocs..syssegments, t=sybsystemprocs..systhresholds} 0 224

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syssegments  
reads table sybsystemprocs..sysobjects  
writes table sybsystemprocs..sp_dropalias_rset_002 
writes table sybsystemprocs..sp_dropalias_rset_001 
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
writes table sybsystemprocs..sysalternates  
reads table sybsystemprocs..systhresholds  
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)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)