Database	Proc	Application	Created	Links
sybsystemprocs	sp_changedbowner		31 Aug 14	Defects 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 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    
63        select @HA_CERTIFIED = 0
64    
65    
66        /* check to see if we are using HA specific SP for a HA enabled server */
67        exec @retstat = sp_ha_check_certified 'sp_changedbowner', @HA_CERTIFIED
68        if (@retstat != 0)
69            return (1)
70    
71        if @@trancount = 0
72        begin
73            set chained off
74        end
75    
76        set transaction isolation level 1
77    
78        /* check if user has sa role, proc_role will also do auditing
79        ** if required. proc_role will also print error message if required.
80        */
81    
82        if (proc_role("sa_role") = 0)
83            return (1)
84    
85        /*
86        **  Can't change the owner of the master, model, tempdb or sybsystemprocs  database.
87        */
88        if db_name() in ("master", "tempdb", "model", "sybsystemprocs")
89        begin
90            if @@clustermode = "shared disk cluster"
91            begin
92                /*
93                ** 19857, "Can't change the owner of the master, model, 
94                ** sybsystemprocs, tempdb or local system temporary 
95                ** databases."
96                */
97                raiserror 19857
98            end
99    
100           else
101           begin
102               /*
103               ** 17361, "Can't change the owner of the master, model, 
104               ** tempdb or sybsystemprocs  database."
105               */
106               raiserror 17361
107           end
108   
109           return (1)
110       end
111   
112       /*
113       ** In SDC, can't change the owner of the local system tempdb either.
114       */
115       if @@clustermode = "shared disk cluster"
116       begin
117           declare @localsystempdbbit int
118           select @localsystempdbbit = number
119           from master.dbo.spt_values
120           where type = "D3" and name = "local system temp db"
121   
122           if exists (select 1 from master.dbo.sysdatabases
123                   where dbid = db_id() and
124                       (status3 & @localsystempdbbit) = @localsystempdbbit)
125           begin
126               /*
127               ** 19857, "Can't change the owner of the master, model, 
128               ** sybsystemprocs, tempdb or local system temporary 
129               ** databases."
130               */
131               raiserror 19857
132               return (1)
133           end
134       end
135   
136       /*
137       **  Make sure that @loginame exists and has a login.
138       */
139       select @suid = suid
140       from master.dbo.syslogins
141       where name = @loginame
142           and ((status & 512) != 512) /* not LOGIN PROFILE */
143   
144       if @suid is NULL
145       begin
146           /*
147           ** 17231, "No login with the specified name exists."
148           */
149           raiserror 17231
150           return (1)
151       end
152   
153       /*
154       **  Make sure that @loginame isn't already a user or alias in the database.
155       */
156       if exists (select *
157               from sysusers
158               where suid = @suid)
159       begin
160           /*
161           ** 17362, "The proposed new db owner already is a user in the database or owns the database."
162           */
163           raiserror 17362
164           return (1)
165       end
166       if exists (select *
167               from sysalternates
168               where suid = @suid)
169       begin
170           /*
171           ** 17363, "The proposed new db owner already is aliased in the database."
172           */
173           raiserror 17363
174           return (1)
175       end
176   
177       /* 
178       ** find old (current) dbo's suid
179       */
180       select @oldsuid = suid
181       from sysusers
182       where uid = 1
183   
184       /*
185       ** Allow IMDB/RDDB to be the coordinator of a multi-db transaction.
186       ** We need this override because we are about to update current database
187       ** and the master database in the same transaction.
188       */
189       select @coord_override = 0
190       if exists (select * from master.dbo.sysdatabases
191               where dbid = db_id()
192                   and durability != 1)
193       begin
194           /* 
195           ** If the switch is not turned ON in session or serverwide, 
196           ** turn it ON and remember that we turned it ON here
197           */
198           if (switchprop("allow_nondurable_db_as_coorddb") = 0)
199           begin
200               select @coord_override = 1
201               set switch on allow_nondurable_db_as_coorddb
202               with override, no_info
203           end
204       end
205   
206       begin transaction
207   
208       /*
209       **  Now change the suid of the owner of the database to the suid of @loginame.
210       */
211       update sysusers
212       set suid = @suid
213       where uid = 1
214   
215       /*
216       ** if the user requested that aliases be mapped to new dbo, do that.
217       */
218       /* 17431, "true" */
219       exec sp_getmessage 17431, @true out
220       if lower(@map) in ("true", @true)
221       begin
222   
223           if exists (select *
224                   from sysalternates
225                   where altsuid = @oldsuid)
226           begin
227               update sysalternates
228               set altsuid = @suid
229               where altsuid = @oldsuid
230   
231               /*
232               ** 17364, "The dependent aliases were mapped to the new dbo."
233               */
234               exec sp_getmessage 17364, @msg output
235               print @msg
236           end
237       end
238       /* else drop the aliases to the old dbo */
239       else
240       begin
241   
242           if exists (select *
243                   from sysalternates
244                   where altsuid = @oldsuid)
245           begin
246               delete from sysalternates
247               where altsuid = @oldsuid
248   
249               /*
250               ** 17365, "The dependent aliases were dropped."
251               */
252               exec sp_getmessage 17365, @msg output
253               print @msg
254           end
255       end
256   
257       /*
258       **  Reflect the new owner of the database in master.dbo.sysdatabases.
259       */
260       update master.dbo.sysdatabases
261       set suid = @suid
262       where dbid = db_id()
263   
264       /* Update owner of the associated thresholds in systhreshods table */
265       begin
266           if exists (select *
267                   from systhresholds
268                   where suid = @oldsuid)
269           begin
270               update systhresholds
271               set suid = @suid
272               where suid = @oldsuid
273           end
274       end
275   
276       commit transaction
277   
278       /* Disable the override if we enabled it earlier */
279       if (@coord_override = 1)
280       begin
281           select @coord_override = 0
282           set switch off allow_nondurable_db_as_coorddb with no_info
283       end
284   
285       /* 
286       ** To check if the new owner has all the roles the "currauth" 
287       ** specifies. If he does not have all the roles required, 
288       ** this change may cause the stored procedure UN-EXECUTABLE 
289       ** later when the threshold is crossed. So we raise a 
290       ** warning error.
291       */
292       begin
293           declare auth_procname cursor
294           for select currauth, proc_name from systhresholds
295           where suid = @suid
296           for read only
297   
298           open auth_procname
299   
300           fetch auth_procname into @currauth, @proc_name
301           while @@sqlstatus <> 2
302           begin
303               select @count = count(*)
304               from master..syssrvroles a,
305                   systhresholds b,
306                   master.dbo.spt_values c
307               where c.type = "P"
308                   and c.number = a.srid
309                   and c.low <= datalength(b.currauth)
310                   and convert(tinyint,
311                   substring(currauth, c.low, 1))
312                   & c.high != 0
313                   and a.srid not in
314                       (select srid
315                       from master..sysloginroles
316                       where suid = @suid)
317   
318               /*
319               ** If the new dbo does not have all the 
320               ** roles specified in the current 
321               ** authorization bitmask "currauth",
322               ** we print an warning message 
323               */
324   
325               if @count > 0
326               begin
327                   /* 
328                   ** 19575, "Warning: The stored 
329                   ** procedure '%1!' may not execute; 
330                   ** check database owner's threshold 
331                   ** authorization."
332                   */
333                   exec sp_getmessage 19575, @msg output
334                   print @msg, @proc_name
335               end
336               fetch auth_procname into @currauth, @proc_name
337           end
338           close auth_procname
339           deallocate cursor auth_procname
340       end
341   
342       /*
343       ** Update the dbinfo and dbtable uid fields for this database.
344       */
345       select @dbname = db_name()
346       dbcc dbrepair(@dbname, "updowner")
347   
348       /*
349       ** 17366, "Database owner changed."
350       */
351       exec sp_getmessage 17366, @msg output
352       print @msg
353   
354       return (0)
355   
356   --} /* End of create stored proc. */
357



exec sp_procxmode 'sp_changedbowner', 'AnyMode'
go

Grant Execute on sp_changedbowner to public
go

DEFECTS
QJWI 5 Join or Sarg Without Index	309
QJWI 5 Join or Sarg Without Index	313
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type}	120
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type}	307
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	123
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	191
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	262
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..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	211
MNER 3 No Error Check should check return value of exec	219
MNER 3 No Error Check should check @@error after update	227
MNER 3 No Error Check should check return value of exec	234
MNER 3 No Error Check should check @@error after delete	246
MNER 3 No Error Check should check return value of exec	252
MNER 3 No Error Check should check @@error after update	260
MNER 3 No Error Check should check @@error after update	270
MNER 3 No Error Check should check return value of exec	333
MNER 3 No Error Check should check return value of exec	351
MUCO 3 Useless Code Useless Brackets	68
MUCO 3 Useless Code Useless Brackets	69
MUCO 3 Useless Code Useless Brackets	82
MUCO 3 Useless Code Useless Brackets	83
MUCO 3 Useless Code Useless Brackets	109
MUCO 3 Useless Code Useless Brackets	132
MUCO 3 Useless Code Useless Brackets	150
MUCO 3 Useless Code Useless Brackets	164
MUCO 3 Useless Code Useless Brackets	174
MUCO 3 Useless Code Useless Brackets	198
MUCO 3 Useless Code Useless Begin-End Pair	265
MUCO 3 Useless Code Useless Brackets	279
MUCO 3 Useless Code Useless Begin-End Pair	292
MUCO 3 Useless Code Useless Brackets	354
QAFM 3 Var Assignment from potentially many rows	118
QISO 3 Set isolation level	76
QNAJ 3 Not using ANSI Inner Join	304
QNUA 3 Should use Alias: Column currauth should use alias b	311
QNUA 3 Should use Alias: Table master..sysloginroles	315
QSWV 3 Sarg with variable @suid, Candidate Index: sysusers.csysusers clustered(suid) F	158
QSWV 3 Sarg with variable @suid, Candidate Index: sysloginroles.csysloginroles clustered(suid) F	316
QTJ1 3 Table only appears in inner join clause	305
VNRD 3 Variable is not read @coord_override	281
CRDO 2 Read Only Cursor Marker (has for read only clause)	294
MSUB 2 Subquery Marker	122
MSUB 2 Subquery Marker	156
MSUB 2 Subquery Marker	166
MSUB 2 Subquery Marker	190
MSUB 2 Subquery Marker	223
MSUB 2 Subquery Marker	242
MSUB 2 Subquery Marker	266
MSUB 2 Subquery Marker	314
MTR1 2 Metrics: Comments Ratio Comments: 49%	45
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 23 = 27dec - 6exi + 2	45
MTR3 2 Metrics: Query Complexity Complexity: 148	45