Database | Proc | Application | Created | Links |
sybsystemprocs | sp_dropalias ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_dropalias" 17480 6 ** 7 ** 17231, "No login with the specified name exists." 8 ** 17480, "Alias user dropped." 9 ** 17481, "No alias for specified user exists." 10 ** 17756, "The execution of the stored procedure '%1!' in database 11 ** '%2!' was aborted because there was an error in writing the 12 ** replication log record." 13 ** 18337, "Setting curwrite label to data_low for deleting from sysalternates 14 ** table failed." 15 ** 18790, "You cannot drop the alias for login '%1!' because '%2!' owns 16 ** objects in the database." 17 ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'" 18 ** 18778, "A login with Login name '%1!' AND login id '%2!' could not be found in syslogins." 19 ** 18806, "You cannot drop the alias for login '%1!' because '%2!' owns 20 ** thresholds in the database." 21 ** 18935, "Warning: You have forced the drop of the alias for login '%1!' 22 ** which owns objects in the database. This may result in errors when 23 ** those objects are accessed from or contain references to another 24 ** database." 25 */ 26 27 /* 28 ** IMPORTANT: Please read the following instructions before 29 ** making changes to this stored procedure. 30 ** 31 ** To make this stored procedure compatible with High Availability (HA), 32 ** changes to certain system tables must be propagated 33 ** to the companion server under some conditions. 34 ** The tables include (but are not limited to): 35 ** syslogins, sysservers, sysattributes, systimeranges, 36 ** sysresourcelimits, sysalternates, sysdatabases, 37 ** syslanguages, sysremotelogins, sysloginroles, 38 ** sysalternates (master DB only), systypes (master DB only), 39 ** sysusers (master DB only), sysprotects (master DB only) 40 ** please refer to the HA documentation for detail. 41 ** 42 ** Here is what you need to do: 43 ** For each insert/update/delete statement, add three sections to 44 ** -- start HA transaction prior to the statement 45 ** -- add the statement 46 ** -- add HA synchronization code to propagate the change to the companion 47 ** 48 ** For example, if you are adding 49 ** insert master.dbo.syslogins ...... 50 ** the code should look like: 51 ** 1. Before that SQL statement: 52 ** 53 ** 2. Now, the SQL statement: 54 ** insert master.dbo.syslogins ...... 55 ** 3. Add a HA synchronization section right after the SQL statement: 56 ** 57 ** 58 ** You may need to do similar change for each built-in function you 59 ** want to add. 60 ** 61 ** After that, you need to add a separate part at a place where it can not 62 ** be reached by the normal execution path: 63 ** clean_all: 64 ** 65 ** return (1) 66 ** 18790, "You cannot drop the alias for login '%1!' because '%2!' owns objects in the database." 67 */ 68 69 create procedure sp_dropalias 70 @loginame varchar(255), /* account name of the user with the alias */ 71 @option varchar(30) = NULL /* 'force' to force dropping the alias, even if 72 ** there are objects created by the alias. 73 */ 74 as 75 76 declare @msg varchar(1024) 77 declare @dbname varchar(255) 78 declare @suid int /* suid of the user */ 79 declare @objectcount int /* count of objects owned by login */ 80 declare @threshcount int /* count of thresholds owned by login */ 81 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 82 declare @retstat int 83 declare @dummy int 84 85 select @HA_CERTIFIED = 0 86 87 88 89 /* check to see if we are using HA specific SP for a HA enabled server */ 90 exec @retstat = sp_ha_check_certified 'sp_dropalias', @HA_CERTIFIED 91 if (@retstat != 0) 92 return (1) 93 94 if @@trancount = 0 95 begin 96 set chained off 97 end 98 99 set transaction isolation level 1 100 101 /* 102 ** Only the Database Owner (DBO) or 103 ** Accounts with SA or SSO role can execute it. 104 ** If the user has SA role, it's uid will 105 ** be DBO uid (1). 106 */ 107 if ((user_id() != 1) and (charindex("sso_role", show_role()) = 0)) 108 begin 109 /* 110 ** proc_role() will raise permission errors 111 ** and send audit records to the audit trail 112 */ 113 select @dummy = proc_role("sa_role") 114 select @dummy = proc_role("sso_role") 115 return (1) 116 end 117 118 /* Send apropriate audit records. */ 119 if (charindex("sa_role", show_role()) > 0) 120 select @dummy = proc_role("sa_role") 121 if (charindex("sso_role", show_role()) > 0) 122 select @dummy = proc_role("sso_role") 123 124 /* 125 ** Check to make sure that the @loginame has an account. 126 */ 127 select @suid = suser_id(@loginame) 128 if @suid is NULL 129 begin 130 /* 131 ** 17231, "No login with the specified name exists." 132 */ 133 raiserror 17231 134 return (1) 135 end 136 137 /* 138 ** If the user owns any objects return after raising error. 139 */ 140 select @objectcount = count(*) 141 from sysobjects 142 where loginame = @loginame 143 144 if @objectcount > 0 145 begin 146 if @option = 'force' 147 begin 148 /* 18935, "Warning: You have forced the drop of the alias for 149 ** login '%1!' which owns objects in the database. This 150 ** may result in errors when those objects are accessed 151 ** from or contain references to another database." 152 */ 153 exec sp_getmessage 18935, @msg output 154 print @msg, @loginame 155 end 156 else 157 begin 158 /* 18790, "You cannot drop the alias for login '%1!' because 159 ** '%2!' owns objects in the database." 160 */ 161 raiserror 18790, @loginame, @loginame 162 select name, type from sysobjects 163 where loginame = @loginame 164 return (1) 165 end 166 167 end 168 /* 169 ** If the user owns any thresholds return after raising error. 170 */ 171 select @threshcount = count(*) 172 from systhresholds 173 where suid = @suid 174 175 if @threshcount > 0 176 begin 177 /* 18806, "You cannot drop the alias for login '%1!' because 178 ** '%2!' owns thresholds in the database." 179 */ 180 raiserror 18806, @loginame, @loginame 181 select "Segment name" = g.name, "Free pages" = t.free_space 182 from syssegments g, systhresholds t 183 where t.suid = @suid 184 and t.segment = g.segment 185 return (1) 186 end 187 188 189 190 /* 191 ** Delete the alias, if any, from sysalternates. 192 */ 193 194 /* 195 ** This transaction also writes a log record for replicating the 196 ** invocation of this procedure. If logexec() fails, the transaction 197 ** is aborted. 198 ** 199 ** IMPORTANT: The name rs_logexec is significant and is used by 200 ** Replication Server. 201 */ 202 begin transaction rs_logexec 203 204 205 206 delete from sysalternates 207 where suid = suser_id(@loginame) 208 209 /* 210 ** If nothing happened (rowcount = 0), there was no alias. 211 */ 212 if (@@rowcount = 0) 213 begin 214 /* 215 ** 17481, "No alias for specified user exists." 216 */ 217 raiserror 17481 218 219 rollback transaction rs_logexec 220 return (1) 221 end 222 223 /* 224 ** Write the log record to replicate this invocation 225 ** of the stored procedure. 226 */ 227 if (logexec() != 1) 228 begin 229 /* 230 ** 17756, "The execution of the stored procedure '%1!' 231 ** in database '%2!' was aborted because there 232 ** was an error in writing the replication log 233 ** record." 234 */ 235 select @dbname = db_name() 236 raiserror 17756, "sp_dropalias", @dbname 237 238 rollback transaction rs_logexec 239 return (1) 240 end 241 242 243 244 commit transaction rs_logexec 245 246 /* Update protection timestamp in Resource */ 247 grant all to null 248 249 /* 250 ** 17480, "Alias user dropped." 251 */ 252 exec sp_getmessage 17480, @msg output 253 print @msg 254 255 return (0) 256 257 clean_all: 258 rollback transaction rs_logexec 259 return (1) 260
exec sp_procxmode 'sp_dropalias', 'AnyMode' go Grant Execute on sp_dropalias to public go
RESULT SETS | |
sp_dropalias_rset_002 | |
sp_dropalias_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED calls proc sybsystemprocs..sp_ha_check_certified ![]() reads table tempdb..sysobjects (1) ![]() writes table sybsystemprocs..sysalternates ![]() reads table sybsystemprocs..syssegments ![]() reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..systhresholds ![]() calls proc sybsystemprocs..sp_getmessage ![]() reads table master..sysmessages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() |