DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropalias  31 Aug 14Defects 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 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    
85        select @HA_CERTIFIED = 0
86    
87    
88    
89        /* check to see if we are using HA specific SP for a HA enabled server */
90        exec @retstat = sp_ha_check_certified 'sp_dropalias', @HA_CERTIFIED
91        if (@retstat != 0)
92            return (1)
93    
94        if @@trancount = 0
95        begin
96            set chained off
97        end
98    
99        set transaction isolation level 1
100   
101       /*
102       **  Only the Database Owner (DBO) or
103       **  Accounts with SA or SSO role can execute it.
104       **  If the user has SA role, it's uid will
105       **  be DBO uid (1).
106       */
107       if ((user_id() != 1) and (charindex("sso_role", show_role()) = 0))
108       begin
109           /* 
110           ** proc_role() will raise permission errors  
111           ** and send audit records to the audit trail
112           */
113           select @dummy = proc_role("sa_role")
114           select @dummy = proc_role("sso_role")
115           return (1)
116       end
117   
118       /* Send apropriate audit records. */
119       if (charindex("sa_role", show_role()) > 0)
120           select @dummy = proc_role("sa_role")
121       if (charindex("sso_role", show_role()) > 0)
122           select @dummy = proc_role("sso_role")
123   
124       /*
125       **  Check to make sure that the @loginame has an account.
126       */
127       select @suid = suser_id(@loginame)
128       if @suid is NULL
129       begin
130           /*
131           ** 17231, "No login with the specified name exists."
132           */
133           raiserror 17231
134           return (1)
135       end
136   
137       /*
138       ** If the user owns any objects return after raising error. 
139       */
140       select @objectcount = count(*)
141       from sysobjects
142       where loginame = @loginame
143   
144       if @objectcount > 0
145       begin
146           if @option = 'force'
147           begin
148               /* 18935, "Warning: You have forced the drop of the alias for
149               ** login '%1!' which owns objects in the database. This
150               ** may result in errors when those objects are accessed
151               ** from or contain references to another database."
152               */
153               exec sp_getmessage 18935, @msg output
154               print @msg, @loginame
155           end
156           else
157           begin
158               /* 18790, "You cannot drop the alias for login '%1!' because
159               ** '%2!' owns objects in the database."
160               */
161               raiserror 18790, @loginame, @loginame
162               select name, type from sysobjects
163               where loginame = @loginame
164               return (1)
165           end
166   
167       end
168       /*
169       ** If the user owns any thresholds return after raising error.
170       */
171       select @threshcount = count(*)
172       from systhresholds
173       where suid = @suid
174   
175       if @threshcount > 0
176       begin
177           /* 18806, "You cannot drop the alias for login '%1!' because
178           **	'%2!' owns thresholds in the database."
179           */
180           raiserror 18806, @loginame, @loginame
181           select "Segment name" = g.name, "Free pages" = t.free_space
182           from syssegments g, systhresholds t
183           where t.suid = @suid
184               and t.segment = g.segment
185           return (1)
186       end
187   
188   
189   
190       /*
191       **  Delete the alias, if any, from sysalternates.
192       */
193   
194       /* 
195       ** This transaction also writes a log record for replicating the
196       ** invocation of this procedure. If logexec() fails, the transaction
197       ** is aborted.
198       **
199       ** IMPORTANT: The name rs_logexec is significant and is used by
200       ** Replication Server.
201       */
202       begin transaction rs_logexec
203   
204   
205   
206       delete from sysalternates
207       where suid = suser_id(@loginame)
208   
209       /*
210       **  If nothing happened (rowcount = 0), there was no alias.
211       */
212       if (@@rowcount = 0)
213       begin
214           /*
215           ** 17481, "No alias for specified user exists."
216           */
217           raiserror 17481
218   
219           rollback transaction rs_logexec
220           return (1)
221       end
222   
223       /*
224       ** Write the log record to replicate this invocation 
225       ** of the stored procedure.
226       */
227       if (logexec() != 1)
228       begin
229           /*
230           ** 17756, "The execution of the stored procedure '%1!'
231           ** 	   in database '%2!' was aborted because there
232           ** 	   was an error in writing the replication log
233           **	   record."
234           */
235           select @dbname = db_name()
236           raiserror 17756, "sp_dropalias", @dbname
237   
238           rollback transaction rs_logexec
239           return (1)
240       end
241   
242   
243   
244       commit transaction rs_logexec
245   
246       /* Update protection timestamp in Resource */
247       grant all to null
248   
249       /*
250       ** 17480, "Alias user dropped."
251       */
252       exec sp_getmessage 17480, @msg output
253       print @msg
254   
255       return (0)
256   
257   clean_all:
258       rollback transaction rs_logexec
259       return (1)
260   


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 257
 MURC 6 Unreachable Code 258
 MURC 6 Unreachable Code 259
 QJWI 5 Join or Sarg Without Index 184
 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 153
 MNER 3 No Error Check should check @@error after delete 206
 MNER 3 No Error Check should check return value of exec 252
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 212
 MUCO 3 Useless Code Useless Brackets 220
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 239
 MUCO 3 Useless Code Useless Brackets 255
 MUCO 3 Useless Code Useless Brackets 259
 QCRS 3 Conditional Result Set 162
 QCRS 3 Conditional Result Set 181
 QISO 3 Set isolation level 99
 QNAJ 3 Not using ANSI Inner Join 182
 VNRD 3 Variable is not read @dummy 122
 MRST 2 Result Set Marker 162
 MRST 2 Result Set Marker 181
 MTR1 2 Metrics: Comments Ratio Comments: 66% 69
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 12dec - 7exi + 2 69
 MTR3 2 Metrics: Query Complexity Complexity: 84 69
 PRED_QUERY_COLLECTION 2 {s=sybsystemprocs..syssegments, t=sybsystemprocs..systhresholds} 0 181

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
writes table sybsystemprocs..sysalternates  
reads table sybsystemprocs..syssegments  
reads table sybsystemprocs..sysobjects  
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 master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages