DatabaseProcApplicationCreatedLinks
sybsystemprocssp_defaultdb  31 Aug 14Defects 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 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    
93        /*
94        ** Initialize some constants
95        */
96        select @db_rep_level_all = - 1,
97            @db_rep_level_none = 0,
98            @db_rep_level_l1 = 1,
99            @lt_rep_get_failed = - 2
100   
101       select @HA_CERTIFIED = 0
102   
103       select @sa_role = charindex("sa_role", show_role()),
104           @sso_role = charindex("sso_role", show_role())
105   
106   
107   
108       /* check to see if we are using HA specific SP for a HA enabled server */
109       exec @retstat = sp_ha_check_certified 'sp_defaultdb', @HA_CERTIFIED
110       if (@retstat != 0)
111           return (1)
112   
113       /*
114       ** Do not allow this system procedure to be run from within a transaction
115       ** to avoid creating a multi-database transaction where the 'master'
116       ** database is not the co-ordinating database.
117       */
118       if @@trancount > 0
119       begin
120           /*
121           ** 17260, "Can't run %1! from within a transaction."
122           */
123           raiserror 17260, "sp_defaultdb"
124           return (1)
125       end
126       else
127       begin
128           set chained off
129       end
130   
131       set transaction isolation level 1
132   
133       /*
134       ** Get the replication status of the 'master' database
135       */
136       select @log_for_rep = getdbrepstat(1)
137       if (@log_for_rep = @lt_rep_get_failed)
138       begin
139           raiserror 18409, "getdbrepstat"
140           return (1)
141       end
142   
143       /*
144       ** Convert the replication status to a boolean
145       */
146       if (@log_for_rep != @db_rep_level_none)
147           select @log_for_rep = 1
148       else
149           select @log_for_rep = 0
150   
151       /*
152       ** If we are logging this system procedure for replication, we must be in
153       ** the 'master' database to avoid creating a multi-database transaction
154       ** which could make recovery of the 'master' database impossible.
155       */
156       if (@log_for_rep = 1) and (db_name() != "master")
157       begin
158           raiserror 18388, "sp_defaultdb"
159           return (1)
160       end
161   
162       /*
163       **  Only the Account Owner or
164       **  Accounts with SA role or SSO role can execute it.
165       **  proc_role will also do auditing if required and
166       **  will also print error message if required.
167       */
168       if ((suser_name() != @loginame) and
169               (@sa_role = 0) and (@sso_role = 0))
170       begin
171           select @dummy = proc_role("sa_role")
172           select @dummy = proc_role("sso_role")
173           return (1)
174       end
175       else
176       begin
177           if (@sa_role > 0)
178           begin
179               select @dummy = proc_role("sa_role")
180           end
181           if (@sso_role > 0)
182           begin
183               select @dummy = proc_role("sso_role")
184           end
185       end
186   
187       /*
188       **  Check that the account exists.
189       */
190       if not exists (select *
191               from master.dbo.syslogins
192               where name = @loginame
193                   and (status & 512) != 512) /* not LOGIN PROFILE */
194       begin
195           /*
196           ** 17231, "No login with the specified name exists."
197           */
198           raiserror 17231
199           return (1)
200       end
201   
202       /*
203       **  Check that the database name is valid.
204       */
205       if not exists (select *
206               from master.dbo.sysdatabases
207               where name = @defdb)
208       begin
209           /*
210           ** 17440, "Database name not valid -- default not changed."
211           */
212           raiserror 17440
213           return (1)
214       end
215   
216       /*
217       **  Check that the database name is useable on all instances of cluster.
218       **  If specified default database is a local temporary database then
219       **  fail the command to avoid problems at connection time.
220       */
221       if db_instanceid(db_id(@defdb)) is not null
222       begin
223           /*
224           ** 19822, "A local temporary database is not permitted as the 
225           ** default database for a login."
226           */
227           raiserror 19822
228           return (1)
229       end
230   
231   
232   
233       if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
234           begin tran rs_logexec
235   
236   
237   
238       /*
239       ** User cannot change the default database if automatic login script
240       ** is enabled.
241       */
242       if exists (select * from master.dbo.syslogins
243               where name = @loginame and procid is not NULL)
244       begin
245           /*
246           ** 17445, "Cannot change default database since login trigger for
247           ** user '%1!' is currently active."
248           */
249           exec sp_getmessage 17445, @msg output
250           print @msg, @loginame
251           return (1)
252       end
253   
254       /*
255       **  Change the database
256       */
257   
258       update master.dbo.syslogins
259       set dbname = @defdb, procid = NULL
260       where name = @loginame
261   
262       if @@rowcount = 1
263           select @rtn_code = 0
264       else
265           select @rtn_code = 1
266   
267   
268   
269       if (@rtn_code = 0)
270       begin
271           if (@log_for_rep = 1)
272           begin
273               /*
274               ** If the 'master' database is marked for replication, the
275               ** T-SQL built-in 'logexec()' will log for replication the
276               ** execution instance of this system procedure.  Otherwise,
277               ** the T-SQL built-in 'logexec()' is a no-op.
278               */
279               if (logexec(1) != 1)
280               begin
281                   raiserror 17756, "sp_defaultdb", "master"
282                   goto clean_all
283               end
284           end
285   
286           if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
287               commit tran rs_logexec
288   
289           /*
290           ** 17442, "Default database changed." 
291           */
292           exec sp_getmessage 17442, @msg output
293           print @msg
294           return (0)
295       end
296       else
297       begin
298           /*
299           ** 17443, "Error in updating the default database."
300           */
301           raiserror 17443
302           goto clean_all
303       end
304   
305       return (0)
306   
307   clean_all:
308       if ((@log_for_rep = 1) or (@HA_CERTIFIED = 1))
309           rollback tran rs_logexec
310       return (1)
311   
312   


exec sp_procxmode 'sp_defaultdb', 'AnyMode'
go

Grant Execute on sp_defaultdb to public
go
DEFECTS
 MURC 6 Unreachable Code 305
 MRIT 5 Return in Transaction trancount is 1 251
 MCTR 4 Conditional Begin Tran or Commit Tran 234
 MCTR 4 Conditional Begin Tran or Commit Tran 287
 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 249
 MNER 3 No Error Check should check @@error after update 258
 MNER 3 No Error Check should check return value of exec 292
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 251
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 271
 MUCO 3 Useless Code Useless Brackets 279
 MUCO 3 Useless Code Useless Brackets 286
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 308
 MUCO 3 Useless Code Useless Brackets 310
 QISO 3 Set isolation level 131
 VNRD 3 Variable is not read @db_rep_level_all 96
 VNRD 3 Variable is not read @db_rep_level_l1 98
 VNRD 3 Variable is not read @dummy 183
 MSUB 2 Subquery Marker 190
 MSUB 2 Subquery Marker 205
 MSUB 2 Subquery Marker 242
 MTR1 2 Metrics: Comments Ratio Comments: 61% 74
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 19 = 27dec - 10exi + 2 74
 MTR3 2 Metrics: Query Complexity Complexity: 113 74

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

CALLERS
called by proc sybsystemprocs..sp_modifylogin