DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setup_table_transfer  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Messages for "sp_setup_table_transfer"         17280
4     **
5     ** 17260, "Can't run %1! from within a transaction." 
6     ** 18305, "Table '%1!' has already been created."
7     */
8     create procedure sp_setup_table_transfer
9     as
10    
11        declare @cmd varchar(100),
12            @procname varchar(30),
13            @procval int,
14            @tabname varchar(30)
15    
16        select @procname = 'sp_setup_table_transfer',
17            @tabname = 'spt_TableTransfer'
18    
19        /*
20        **  Create-table does not happen within a transaction.
21        */
22        if @@trancount > 0
23        begin
24            -- 17260, "Can't run %1! from within a transaction." 
25            raiserror 17260, @procname
26            return 1
27        end
28    
29        set chained off
30        set transaction isolation level 1
31    
32        /*
33        **  Only the Database Owner (DBO) or accounts with SA role can execute this.
34        **  A user with SA role has UID '1' (DBO) just as the DBO does.
35        */
36        if (user_id() != 1)
37        begin
38            /* user_id() is not DBO hence user does not have SA role
39            ** audit this as a failed sa command execution.
40            */
41            select @procval = proc_role("sa_role")
42            return 1
43        end
44    
45        -- If user has sa role audit this as a successful sa command execution.
46        if charindex("sa_role", show_role()) > 0
47            select @procval = proc_role("sa_role")
48    
49        -- If spt_TableTransfer already exists, don't try to create it again.
50        if exists (select id from sysobjects where name = @tabname)
51        begin
52            -- 18305, "Table '%1!' has already been created."
53            raiserror 18305, @tabname
54            return 0
55        end
56    
57        -- Create the table.
58        create table spt_TableTransfer(
59            end_code unsigned smallint not null,
60            id int not null,
61            ts_floor bigint not null,
62            ts_ceiling bigint not null,
63            time_begin datetime not null,
64            time_end datetime not null,
65            row_count bigint not null,
66            byte_count bigint not null,
67            sequence_id int not null,
68            col_order tinyint not null,
69            output_to tinyint not null,
70            tracking_id int null,
71            pathname varchar(512) null,
72            row_sep varchar(64) null,
73            col_sep varchar(64) null,
74            primary key (id, sequence_id))
75        lock allpages
76            on 'default'
77    
78        if (@@error != 0)
79            return 1
80    
81        /*
82        ** Let everyone see its contents.  This unfortunately does not compile
83        ** if we create the table within this procedure, so we need to perform
84        ** it via 'exec immediate'.
85        */
86        select @cmd = 'grant select on ' + @tabname + ' to public'
87        exec (@cmd)
88    
89        if (@@error != 0)
90            return 1
91    
92        -- Done successfully
93        return 0
94    


exec sp_procxmode 'sp_setup_table_transfer', 'AnyMode'
go

Grant Execute on sp_setup_table_transfer to public
go
DEFECTS
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 8
 MGTP 3 Grant to public sybsystemprocs..sp_setup_table_transfer  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MUCO 3 Useless Code Useless Brackets 36
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 89
 MZMB 3 Zombie: use of non-existent object sybsystemprocs..spt_TableTransfer 58
 QISO 3 Set isolation level 30
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}		 50
 VNRD 3 Variable is not read @procval 47
 MDYS 2 Dynamic SQL Marker 87
 MSUB 2 Subquery Marker 50
 MTR1 2 Metrics: Comments Ratio Comments: 36% 8
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 4 = 6dec - 4exi + 2 8
 MTR3 2 Metrics: Query Complexity Complexity: 33 8

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects