Database | Proc | Application | Created | Links |
sybsystemprocs | sp_defaultdb ![]() | ![]() | 31 Aug 14 | Defects 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
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 ![]() |