DatabaseProcApplicationCreatedLinks
sybsystemprocssp_defaultdb  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     /*	4.2	28.1	05/14/90	sproc/src/defaultdb */
4     
5     /*
6     ** Generated by spgenmsgs.pl on Thu Feb  2 00:39:15 2006 
7     */
8     /*
9     ** raiserror Messages for defaultdb [Total 7]
10    **
11    ** 17231, "No login with the specified name exists."
12    ** 17260, "Can't run %1! from within a transaction."
13    ** 17440, "Database name not valid -- default not changed."
14    ** 17443, "Error in updating the default database."
15    ** 17445, "Cannot change default database since login trigger for user '%1!' is currently active."
16    ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record."
17    ** 18388, "You must be in the master database in order to run '%1!'."
18    ** 18409, "The built-in function %1! failed. Please see the other messages printed along with this message."
19    ** 19822, "A local temporary database is not permitted as the default database for a login."
20    */
21    /*
22    ** sp_getmessage Messages for defaultdb [Total 4]
23    **
24    ** 17442, "Default database changed."
25    ** 17444, "Automatic login script for user '%1!' is disabled. Use sp_modifylogin to enable execution of auto login script for the new database."
26    ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on the companion server '%2!'."
27    ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins."
28    */
29    /*
30    ** End spgenmsgs.pl output.
31    */
32    
33    /* 
34    ** IMPORTANT: Please read the following instructions before
35    **   making changes to this stored procedure.
36    **
37    **	To make this stored procedure compatible with High Availability (HA),
38    **	changes to certain system tables must be propagated 
39    **	to the companion server under some conditions.
40    **	The tables include (but are not limited to):
41    **		syslogins, sysservers, sysattributes, systimeranges,
42    **		sysresourcelimits, sysalternates, sysdatabases,
43    **		syslanguages, sysremotelogins, sysloginroles,
44    **		sysalternates (master DB only), systypes (master DB only),
45    **		sysusers (master DB only), sysprotects (master DB only)
46    **	please refer to the HA documentation for detail.
47    **
48    **	Here is what you need to do: 
49    **	For each insert/update/delete statement, add three sections to
50    **	-- start HA transaction prior to the statement
51    **	-- add the statement
52    **	-- add HA synchronization code to propagate the change to the companion
53    **
54    **	For example, if you are adding 
55    **		insert master.dbo.syslogins ......
56    **	the code should look like:
57    **	1. Before that SQL statement:
58    **		
59    **	2. Now, the SQL statement:
60    **		insert master.dbo.syslogins ......
61    **	3. Add a HA synchronization section right after the SQL statement:
62    **		
63    **
64    **	You may need to do similar change for each built-in function you
65    **	want to add.
66    **
67    **	Finally, add a separate part at a place where it can not
68    **	be reached by the normal execution path:
69    **	clean_all:
70    **		
71    **		return (1)
72    */
73    
74    create or replace procedure sp_defaultdb
75        @loginame varchar(30), /* login name of the user */
76        @defdb varchar(30) /* default db for the user */
77    as
78    
79        declare @msg varchar(1024)
80        declare @rtn_code int
81        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
82        declare @retstat int
83        declare @dummy int
84        declare @sa_role int /* has sa role */
85        declare @sso_role int /* has sso role */
86    
87        declare @log_for_rep int
88        declare @db_rep_level_all int
89        declare @db_rep_level_none int
90        declare @db_rep_level_l1 int
91        declare @lt_rep_get_failed int
92        declare @nullarg char(1)
93        declare @gp_enabled int
94        declare @status1 int
95        declare @status2 int
96        declare @manage_any_login int
97    
98        select @status1 = 1
99        select @status2 = 1
100       select @sa_role = 0
101       select @sso_role = 0
102       select @manage_any_login = 0
103   
104   
105       /*
106       ** Initialize some constants
107       */
108       select @db_rep_level_all = - 1,
109           @db_rep_level_none = 0,
110           @db_rep_level_l1 = 1,
111           @lt_rep_get_failed = - 2
112   
113       select @HA_CERTIFIED = 0
114   
115       /*
116       **  If granular permissions is not enabled, 
117       **  check whether sa_role or sso_role are set.
118       **  If granular permissions is enabled then check if 
119       ** 'manage any login' permission is set.
120       */
121       select @nullarg = NULL
122       execute @status1 = sp_aux_checkroleperm "sa_role", "manage any login",
123           @nullarg, @gp_enabled output
124   
125       execute @status2 = sp_aux_checkroleperm "sso_role", @nullarg, @nullarg,
126           @gp_enabled output
127   
128       if (@gp_enabled = 0)
129       begin
130           if (@status1 = 0)
131           begin
132               select @sa_role = 1
133           end
134   
135           if (@status2 = 0)
136           begin
137               select @sso_role = 1
138           end
139       end
140       else
141       begin
142           if (@status1 = 0)
143           begin
144               select @manage_any_login = 1
145           end
146       end
147   
148   
149   
150       /* check to see if we are using HA specific SP for a HA enabled server */
151       exec @retstat = sp_ha_check_certified 'sp_defaultdb', @HA_CERTIFIED
152       if (@retstat != 0)
153           return (1)
154   
155       /*
156       ** Do not allow this system procedure to be run from within a transaction
157       ** to avoid creating a multi-database transaction where the 'master'
158       ** database is not the co-ordinating database.
159       */
160       if @@trancount > 0
161       begin
162           /*
163           ** 17260, "Can't run %1! from within a transaction."
164           */
165           raiserror 17260, "sp_defaultdb"
166           return (1)
167       end
168       else
169       begin
170           set chained off
171       end
172   
173       set transaction isolation level 1
174   
175       /*
176       ** Get the replication status of the 'master' database
177       */
178       select @log_for_rep = getdbrepstat(1)
179       if (@log_for_rep = @lt_rep_get_failed)
180       begin
181           raiserror 18409, "getdbrepstat"
182           return (1)
183       end
184   
185       /*
186       ** Convert the replication status to a boolean
187       */
188       if (@log_for_rep != @db_rep_level_none)
189           select @log_for_rep = 1
190       else
191           select @log_for_rep = 0
192   
193       /*
194       ** If we are logging this system procedure for replication, we must be in
195       ** the 'master' database to avoid creating a multi-database transaction
196       ** which could make recovery of the 'master' database impossible.
197       */
198       if (@log_for_rep = 1) and (db_name() != "master")
199       begin
200           raiserror 18388, "sp_defaultdb"
201           return (1)
202       end
203   
204       /*
205       **  Only the Account Owner or
206       **  Accounts with SA role or SSO role can execute it.
207       **  proc_role will also do auditing if required and
208       **  will also print error message if required.
209       */
210       if ((suser_name() != @loginame) and (@sa_role = 0) and (@sso_role = 0) and
211               (@manage_any_login = 0))
212       begin
213           if (@gp_enabled = 0)
214           begin
215               select @dummy = proc_role("sa_role")
216               select @dummy = proc_role("sso_role")
217           end
218           else
219           begin
220               select @dummy = proc_auditperm("manage any login",
221                       @status1)
222           end
223           return (1)
224       end
225       else
226       begin
227           if (@sa_role > 0)
228           begin
229               select @dummy = proc_role("sa_role")
230           end
231           if (@sso_role > 0)
232           begin
233               select @dummy = proc_role("sso_role")
234           end
235           if (@manage_any_login > 0)
236           begin
237               select @dummy = proc_auditperm("manage any login",
238                       @status1)
239           end
240       end
241   
242       /*
243       **  Check that the account exists.
244       */
245       if not exists (select *
246               from master.dbo.syslogins
247               where name = @loginame
248                   and (status & 512) != 512) /* not LOGIN PROFILE */
249       begin
250           /*
251           ** 17231, "No login with the specified name exists."
252           */
253           raiserror 17231
254           return (1)
255       end
256   
257       /*
258       **  Check that the database name is valid.
259       */
260       if not exists (select *
261               from master.dbo.sysdatabases
262               where name = @defdb)
263       begin
264           /*
265           ** 17440, "Database name not valid -- default not changed."
266           */
267           raiserror 17440
268           return (1)
269       end
270   
271       /*
272       **  Check that the database name is useable on all instances of cluster.
273       **  If specified default database is a local temporary database then
274       **  fail the command to avoid problems at connection time.
275       */
276       if db_instanceid(db_id(@defdb)) is not null
277       begin
278           /*
279           ** 19822, "A local temporary database is not permitted as the 
280           ** default database for a login."
281           */
282           raiserror 19822
283           return (1)
284       end
285   
286   
287   
288       if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
289           begin tran rs_logexec
290   
291   
292   
293       /*
294       ** User cannot change the default database if automatic login script
295       ** is enabled.
296       */
297       if exists (select * from master.dbo.syslogins
298               where name = @loginame and procid is not NULL)
299       begin
300           /*
301           ** 17445, "Cannot change default database since login trigger for
302           ** user '%1!' is currently active."
303           */
304           exec sp_getmessage 17445, @msg output
305           print @msg, @loginame
306           return (1)
307       end
308   
309       /*
310       **  Change the database
311       */
312   
313       update master.dbo.syslogins
314       set dbname = @defdb, procid = NULL
315       where name = @loginame
316   
317       if @@rowcount = 1
318           select @rtn_code = 0
319       else
320           select @rtn_code = 1
321   
322   
323   
324       if (@rtn_code = 0)
325       begin
326           if (@log_for_rep = 1)
327           begin
328               /*
329               ** If the 'master' database is marked for replication, the
330               ** T-SQL built-in 'logexec()' will log for replication the
331               ** execution instance of this system procedure.  Otherwise,
332               ** the T-SQL built-in 'logexec()' is a no-op.
333               */
334               if (logexec(1) != 1)
335               begin
336                   raiserror 17756, "sp_defaultdb", "master"
337                   goto clean_all
338               end
339           end
340   
341           if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
342               commit tran rs_logexec
343   
344           /*
345           ** 17442, "Default database changed." 
346           */
347           exec sp_getmessage 17442, @msg output
348           print @msg
349           return (0)
350       end
351       else
352       begin
353           /*
354           ** 17443, "Error in updating the default database."
355           */
356           raiserror 17443
357           goto clean_all
358       end
359   
360       return (0)
361   
362   clean_all:
363       if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
364           rollback tran rs_logexec
365       return (1)
366   
367   


exec sp_procxmode 'sp_defaultdb', 'AnyMode'
go

Grant Execute on sp_defaultdb to public
go
DEFECTS
 MURC 6 Unreachable Code 360
 MRIT 5 Return in Transaction trancount is 1 306
 MCTR 4 Conditional Begin Tran or Commit Tran 289
 MCTR 4 Conditional Begin Tran or Commit Tran 342
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..syslogins  
 MGTP 3 Grant to public sybsystemprocs..sp_defaultdb  
 MNER 3 No Error Check should check return value of exec 122
 MNER 3 No Error Check should check return value of exec 304
 MNER 3 No Error Check should check @@error after update 313
 MNER 3 No Error Check should check return value of exec 347
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 201
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 254
 MUCO 3 Useless Code Useless Brackets 268
 MUCO 3 Useless Code Useless Brackets 283
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 306
 MUCO 3 Useless Code Useless Brackets 324
 MUCO 3 Useless Code Useless Brackets 326
 MUCO 3 Useless Code Useless Brackets 334
 MUCO 3 Useless Code Useless Brackets 341
 MUCO 3 Useless Code Useless Brackets 349
 MUCO 3 Useless Code Useless Brackets 360
 MUCO 3 Useless Code Useless Brackets 363
 MUCO 3 Useless Code Useless Brackets 365
 QISO 3 Set isolation level 173
 VNRD 3 Variable is not read @db_rep_level_all 108
 VNRD 3 Variable is not read @db_rep_level_l1 110
 VNRD 3 Variable is not read @dummy 237
 MSUB 2 Subquery Marker 245
 MSUB 2 Subquery Marker 260
 MSUB 2 Subquery Marker 297
 MTR1 2 Metrics: Comments Ratio Comments: 57% 74
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 27 = 35dec - 10exi + 2 74
 MTR3 2 Metrics: Query Complexity Complexity: 143 74

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

CALLERS
called by proc sybsystemprocs..sp_modifylogin