DatabaseProcApplicationCreatedLinks
sybsystemprocssp_changedbowner  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Mon Nov  6 11:22:17 2006 
4     */
5     /*
6     ** raiserror Messages for changedbowner [Total 5]
7     **
8     ** 17231, "No login with the specified name exists."
9     ** 17361, "Can't change the owner of the master, model, tempdb or sybsystemprocs database."
10    ** 17362, "The proposed new db owner already is a user in the database or owns the database."
11    ** 17363, "The proposed new db owner already is aliased in the database."
12    ** 17368, "Your curwrite label needs to be set correctly before you attempt to change the database owner."
13    
14    */
15    /*
16    ** sp_getmessage Messages for changedbowner [Total 5]
17    **
18    ** 17364, "The dependent aliases were mapped to the new dbo."
19    ** 17365, "The dependent aliases were dropped."
20    ** 17366, "Database owner changed."
21    ** 17431, "true"
22    ** 19575, "Warning: The stored procedure '%1!' may not execute; check database owner's threshold authorization."
23    */
24    /*
25    ** End spgenmsgs.pl output.
26    */
27    /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
28    /*	4.8	1.1	06/14/90	sproc/src/changedbowner */
29    
30    /*
31    ** Messages for "sp_changedbowner"      17360
32    **
33    ** 17231, "No login with the specified name exists."
34    ** 17361, "Can't change the owner of the master, model, tempdb or sybsystemprocs  database."
35    ** 17362, "The proposed new db owner already is a user in the database or owns the database."
36    ** 17363, "The proposed new db owner already is aliased in the database."
37    ** 17364, "The dependent aliases were mapped to the new dbo."
38    ** 17365, "The dependent aliases were dropped."
39    ** 17366, "Database owner changed."
40    ** 17431, "true"
41    ** 17368, "Your curwrite label needs to be set correctly before you attempt to change the database owner."
42    ** 19857, "Can't change the owner of the master, model, sybsystemprocs, tempdb or local system temporary databases."
43    */
44    
45    create or replace procedure sp_changedbowner --{
46        @loginame varchar(30), /* login to become dbo */
47        @map varchar(10) = NULL /* True to map aliases, else drop  */
48    as
49    
50        declare @suid int
51        declare @oldsuid int
52        declare @msg varchar(1024)
53        declare @true varchar(10)
54        declare @dbname varchar(255)
55        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
56        declare @retstat int
57    
58        declare @currauth varbinary(255)
59        declare @proc_name varchar(255)
60        declare @count int
61        declare @coord_override int
62        declare @nullarg char(1)
63        declare @dummy int
64    
65        declare @gp_enabled int
66    
67    
68    
69        select @HA_CERTIFIED = 0
70    
71    
72        /* check to see if we are using HA specific SP for a HA enabled server */
73        exec @retstat = sp_ha_check_certified 'sp_changedbowner', @HA_CERTIFIED
74        if (@retstat != 0)
75            return (1)
76    
77        if @@trancount = 0
78        begin
79            set chained off
80        end
81    
82        set transaction isolation level 1
83    
84        /* check if user has sa role, proc_role will also do auditing
85        ** if required. proc_role will also print error message if required.
86        */
87    
88        select @nullarg = NULL
89        execute @retstat = sp_aux_checkroleperm "sa_role",
90            "own any database", @nullarg, @gp_enabled output
91    
92        if (@gp_enabled = 0)
93        begin
94            if (proc_role("sa_role") = 0) return 1
95        end
96        else
97        begin
98            select @dummy = proc_auditperm("own any database", @retstat)
99        end
100       if (@retstat > 0)
101           return (1)
102   
103       /*
104       **  Can't change the owner of the master, model, tempdb or sybsystemprocs  database.
105       */
106       if db_name() in ("master", "tempdb", "model", "sybsystemprocs")
107       begin
108           if @@clustermode = "shared disk cluster"
109           begin
110               /*
111               ** 19857, "Can't change the owner of the master, model, 
112               ** sybsystemprocs, tempdb or local system temporary 
113               ** databases."
114               */
115               raiserror 19857
116           end
117   
118           else
119           begin
120               /*
121               ** 17361, "Can't change the owner of the master, model, 
122               ** tempdb or sybsystemprocs  database."
123               */
124               raiserror 17361
125           end
126   
127           return (1)
128       end
129   
130       /*
131       ** In SDC, can't change the owner of the local system tempdb either.
132       */
133       if @@clustermode = "shared disk cluster"
134       begin
135           declare @localsystempdbbit int
136           select @localsystempdbbit = number
137           from master.dbo.spt_values
138           where type = "D3" and name = "local system temp db"
139   
140           if exists (select 1 from master.dbo.sysdatabases
141                   where dbid = db_id() and
142                       (status3 & @localsystempdbbit) = @localsystempdbbit)
143           begin
144               /*
145               ** 19857, "Can't change the owner of the master, model, 
146               ** sybsystemprocs, tempdb or local system temporary 
147               ** databases."
148               */
149               raiserror 19857
150               return (1)
151           end
152       end
153   
154       /*
155       **  Make sure that @loginame exists and has a login.
156       */
157       select @suid = suid
158       from master.dbo.syslogins
159       where name = @loginame
160           and ((status & 512) != 512) /* not LOGIN PROFILE */
161   
162       if @suid is NULL
163       begin
164           /*
165           ** 17231, "No login with the specified name exists."
166           */
167           raiserror 17231
168           return (1)
169       end
170   
171       /*
172       **  Make sure that @loginame isn't already a user or alias in the database.
173       */
174       if exists (select *
175               from sysusers
176               where suid = @suid)
177       begin
178           /*
179           ** 17362, "The proposed new db owner already is a user in the database or owns the database."
180           */
181           raiserror 17362
182           return (1)
183       end
184       if exists (select *
185               from sysalternates
186               where suid = @suid)
187       begin
188           /*
189           ** 17363, "The proposed new db owner already is aliased in the database."
190           */
191           raiserror 17363
192           return (1)
193       end
194   
195       /* 
196       ** find old (current) dbo's suid
197       */
198       select @oldsuid = suid
199       from sysusers
200       where uid = 1
201   
202       /*
203       ** Allow IMDB/RDDB to be the coordinator of a multi-db transaction.
204       ** We need this override because we are about to update current database
205       ** and the master database in the same transaction.
206       */
207       select @coord_override = 0
208       if exists (select * from master.dbo.sysdatabases
209               where dbid = db_id()
210                   and durability != 1)
211       begin
212           /* 
213           ** If the switch is not turned ON in session or serverwide, 
214           ** turn it ON and remember that we turned it ON here
215           */
216           if (switchprop("allow_nondurable_db_as_coorddb") = 0)
217           begin
218               select @coord_override = 1
219               set switch on allow_nondurable_db_as_coorddb
220               with override, no_info
221           end
222       end
223   
224       begin transaction
225   
226       /*
227       **  Now change the suid of the owner of the database to the suid of @loginame.
228       */
229       update sysusers
230       set suid = @suid
231       where uid = 1
232   
233       /*
234       ** if the user requested that aliases be mapped to new dbo, do that.
235       */
236       /* 17431, "true" */
237       exec sp_getmessage 17431, @true out
238       if lower(@map) in ("true", @true)
239       begin
240   
241           if exists (select *
242                   from sysalternates
243                   where altsuid = @oldsuid)
244           begin
245               update sysalternates
246               set altsuid = @suid
247               where altsuid = @oldsuid
248   
249               /*
250               ** 17364, "The dependent aliases were mapped to the new dbo."
251               */
252               exec sp_getmessage 17364, @msg output
253               print @msg
254           end
255       end
256       /* else drop the aliases to the old dbo */
257       else
258       begin
259   
260           if exists (select *
261                   from sysalternates
262                   where altsuid = @oldsuid)
263           begin
264               delete from sysalternates
265               where altsuid = @oldsuid
266   
267               /*
268               ** 17365, "The dependent aliases were dropped."
269               */
270               exec sp_getmessage 17365, @msg output
271               print @msg
272           end
273       end
274   
275       /*
276       **  Reflect the new owner of the database in master.dbo.sysdatabases.
277       */
278       update master.dbo.sysdatabases
279       set suid = @suid
280       where dbid = db_id()
281   
282       /* Update owner of the associated thresholds in systhreshods table */
283       begin
284           if exists (select *
285                   from systhresholds
286                   where suid = @oldsuid)
287           begin
288               update systhresholds
289               set suid = @suid
290               where suid = @oldsuid
291           end
292       end
293   
294       commit transaction
295   
296       /* Disable the override if we enabled it earlier */
297       if (@coord_override = 1)
298       begin
299           select @coord_override = 0
300           set switch off allow_nondurable_db_as_coorddb with no_info
301       end
302   
303       /* 
304       ** To check if the new owner has all the roles the "currauth" 
305       ** specifies. If he does not have all the roles required, 
306       ** this change may cause the stored procedure UN-EXECUTABLE 
307       ** later when the threshold is crossed. So we raise a 
308       ** warning error.
309       */
310       begin
311           declare auth_procname cursor
312           for select currauth, proc_name from systhresholds
313           where suid = @suid
314           for read only
315   
316           open auth_procname
317   
318           fetch auth_procname into @currauth, @proc_name
319           while @@sqlstatus <> 2
320           begin
321               select @count = count(*)
322               from master..syssrvroles a,
323                   systhresholds b,
324                   master.dbo.spt_values c
325               where c.type = "P"
326                   and c.number = a.srid
327                   and c.low <= datalength(b.currauth)
328                   and convert(tinyint,
329                   substring(currauth, c.low, 1))
330                   & c.high != 0
331                   and a.srid not in
332                       (select srid
333                       from master..sysloginroles
334                       where suid = @suid)
335   
336               /*
337               ** If the new dbo does not have all the 
338               ** roles specified in the current 
339               ** authorization bitmask "currauth",
340               ** we print an warning message 
341               */
342   
343               if @count > 0
344               begin
345                   /* 
346                   ** 19575, "Warning: The stored 
347                   ** procedure '%1!' may not execute; 
348                   ** check database owner's threshold 
349                   ** authorization."
350                   */
351                   exec sp_getmessage 19575, @msg output
352                   print @msg, @proc_name
353               end
354               fetch auth_procname into @currauth, @proc_name
355           end
356           close auth_procname
357           deallocate cursor auth_procname
358       end
359   
360       /*
361       ** Update the dbinfo and dbtable uid fields for this database.
362       */
363       select @dbname = db_name()
364       dbcc dbrepair(@dbname, "updowner")
365   
366       /*
367       **  We need to invalidate the protection cache since objects have
368       **  changed ownership.  This command will invalidate the current
369       **  protection cache so when protections are checked the new and
370       **  correct protections will be used.
371       */
372       grant all to null
373   
374       /*
375       ** 17366, "Database owner changed."
376       */
377       exec sp_getmessage 17366, @msg output
378       print @msg
379   
380       return (0)
381   
382   --} /* End of create stored proc. */
383   


exec sp_procxmode 'sp_changedbowner', 'AnyMode'
go

Grant Execute on sp_changedbowner to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 327
 QJWI 5 Join or Sarg Without Index 331
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
138
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
325
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 141
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 209
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 280
 TNOI 4 Table with no index sybsystemprocs..systhresholds sybsystemprocs..systhresholds
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 TNOU 4 Table with no unique index master..sysloginroles master..sysloginroles
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysloginroles  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public master..syssrvroles  
 MGTP 3 Grant to public sybsystemprocs..sp_changedbowner  
 MGTP 3 Grant to public sybsystemprocs..sysalternates  
 MGTP 3 Grant to public sybsystemprocs..systhresholds  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after update 229
 MNER 3 No Error Check should check return value of exec 237
 MNER 3 No Error Check should check @@error after update 245
 MNER 3 No Error Check should check return value of exec 252
 MNER 3 No Error Check should check @@error after delete 264
 MNER 3 No Error Check should check return value of exec 270
 MNER 3 No Error Check should check @@error after update 278
 MNER 3 No Error Check should check @@error after update 288
 MNER 3 No Error Check should check return value of exec 351
 MNER 3 No Error Check should check return value of exec 377
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Begin-End Pair 283
 MUCO 3 Useless Code Useless Brackets 297
 MUCO 3 Useless Code Useless Begin-End Pair 310
 MUCO 3 Useless Code Useless Brackets 380
 QAFM 3 Var Assignment from potentially many rows 136
 QISO 3 Set isolation level 82
 QNAJ 3 Not using ANSI Inner Join 322
 QNUA 3 Should use Alias: Column currauth should use alias b 329
 QNUA 3 Should use Alias: Table master..sysloginroles 333
 QSWV 3 Sarg with variable @suid, Candidate Index: sysusers.csysusers clustered(suid) F 176
 QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F 334
 QTJ1 3 Table only appears in inner join clause 323
 VNRD 3 Variable is not read @dummy 98
 VNRD 3 Variable is not read @coord_override 299
 CRDO 2 Read Only Cursor Marker (has for read only clause) 312
 MSUB 2 Subquery Marker 140
 MSUB 2 Subquery Marker 174
 MSUB 2 Subquery Marker 184
 MSUB 2 Subquery Marker 208
 MSUB 2 Subquery Marker 241
 MSUB 2 Subquery Marker 260
 MSUB 2 Subquery Marker 284
 MSUB 2 Subquery Marker 332
 MTR1 2 Metrics: Comments Ratio Comments: 49% 45
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 30dec - 7exi + 2 45
 MTR3 2 Metrics: Query Complexity Complexity: 160 45

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysloginroles (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
read_writes table sybsystemprocs..sysusers  
read_writes table sybsystemprocs..sysalternates  
read_writes table sybsystemprocs..systhresholds  
reads table master..spt_values (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
read_writes table master..sysdatabases (1)  
reads table master..syssrvroles (1)  
reads table master..syslogins (1)