DatabaseProcApplicationCreatedLinks
sybsystemprocssp_displaylevel  31 Aug 14Defects Dependencies

1     
2     /* Stored procedure for assigning config display level for a user login.
3     **
4     */
5     
6     /*
7     ** Generated by spgenmsgs.pl on Thu Feb  2 00:39:16 2006 
8     */
9     /*
10    ** raiserror Messages for displaylevel [Total 5]
11    **
12    ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there was an error in writing the replication log record."
13    ** 18150, "The login '%1!' does not exist."
14    ** 18152, "Invalid display level. The valid values are 'basic', 'intermediate' or 'comprehensive'."
15    ** 18388, "You must be in the master database in order to run '%1!'."
16    ** 18409, "The built-in function '%1!' failed. Please see the other messages printed along with this message."
17    */
18    /*
19    ** sp_getmessage Messages for displaylevel [Total 2]
20    **
21    ** 18151, "The current display level for login '%1!' is '%2!'."
22    ** 18153, "The display level for login '%1!' has been changed to '%2!'."
23    */
24    /*
25    ** End spgenmsgs.pl output.
26    */
27    
28    create procedure sp_displaylevel
29        @loginame varchar(30) = NULL, /* login name */
30        @level varchar(30) = NULL /* config display level */
31    as
32    
33        declare @userid int
34        declare @username varchar(30)
35        declare @cur_level_int int
36        declare @cur_level_char varchar(20)
37        declare @msg varchar(1024)
38    
39        declare @log_for_rep int
40        declare @db_rep_level_all int
41        declare @db_rep_level_none int
42        declare @db_rep_level_l1 int
43        declare @lt_rep_get_failed int
44    
45        /*
46        ** Initialize some constants
47        */
48        select @db_rep_level_all = - 1,
49            @db_rep_level_none = 0,
50            @db_rep_level_l1 = 1,
51            @lt_rep_get_failed = - 2
52    
53        select @userid = NULL
54        select @cur_level_int = NULL
55    
56        /*
57        ** Get the replication status of the 'master' database
58        */
59        select @log_for_rep = getdbrepstat(1)
60        if (@log_for_rep = @lt_rep_get_failed)
61        begin
62            raiserror 18409, "getdbrepstat"
63            return (1)
64        end
65    
66        /*
67        ** Convert the replication status to a boolean
68        */
69        if (@log_for_rep != @db_rep_level_none)
70            select @log_for_rep = 1
71        else
72            select @log_for_rep = 0
73    
74        /*
75        ** If we are logging this system procedure for replication, we must be in
76        ** the 'master' database to avoid creating a multi-database transaction
77        ** which could make recovery of the 'master' database impossible.
78        */
79        if (@log_for_rep = 1) and (db_name() != "master")
80        begin
81            raiserror 18388, "sp_displaylevel"
82            return (1)
83        end
84    
85        /*
86        ** set the user login name
87        */
88        if (@loginame is NULL)
89            select @loginame = suser_name()
90    
91        /*
92        ** set the user id
93        */
94        select @userid = suser_id(@loginame)
95    
96        /*
97        ** check for invalid @loginame
98        */
99        if (@userid is NULL)
100       begin
101           /*  18150, "The login '%s' does not exist." */
102           raiserror 18150, @loginame
103           return (1)
104       end
105   
106       /*
107       ** check if user has permission to change the display level
108       ** only the user himself or user has sa role can has the permission
109       */
110       select @username = suser_name()
111       if (@loginame != @username)
112       begin
113           /* check if user has sa role, proc_role will also do auditing
114           ** if required. proc_role will also print error message if required.
115           */
116           if (proc_role("sa_role") = 0)
117               return (1)
118       end
119   
120       /*
121       ** if @level are NULL, just display the user config level
122       */
123       if (@level is NULL)
124       begin
125           /*
126           ** retrieve the user config level from sysattributes
127           */
128           select @cur_level_int = int_value from master.dbo.sysattributes where
129               class = 4 and
130               attribute = 0 and
131               object_type = 'L' and
132               object = @userid
133   
134           if (@cur_level_int = 10)
135               select @cur_level_char = "comprehensive"
136           else if (@cur_level_int = 5)
137               select @cur_level_char = "intermediate"
138           else if (@cur_level_int = 1)
139               select @cur_level_char = "basic"
140           else
141               select @cur_level_char = "comprehensive"
142   
143           exec sp_getmessage 18151, @msg output
144           print @msg, @loginame, @cur_level_char
145   
146           return (0)
147       end
148   
149       /*
150       ** check if the @level is valid
151       ** the current valid value is 'basic', 'intermediate', and 'comprehensive'.
152       */
153       if (@level = "basic")
154           select @cur_level_int = 1
155       else if (@level = "intermediate")
156           select @cur_level_int = 5
157       else if (@level = "comprehensive")
158           select @cur_level_int = 10
159       else
160       begin
161           /*  18152, "Invalid display level. 
162           **	The valid values are '1', '5' or '10'."
163           */
164           raiserror 18152
165           return (1)
166       end
167   
168       if (@log_for_rep = 1)
169           begin tran rs_logexec
170   
171       /*
172       ** Figure out if we need to insert or update from sysattributes. 
173       */
174       if exists (select * from master.dbo.sysattributes where
175                   class = 4 and
176                   attribute = 0 and
177                   object_type = 'L' and
178                   object = @userid)
179       begin
180           update master.dbo.sysattributes
181           set int_value = @cur_level_int
182           where class = 4 and
183               attribute = 0 and
184               object_type = 'L' and
185               object = @userid
186       end
187       else
188       begin
189           insert into master.dbo.sysattributes
190           (class, attribute, object_type, object, int_value)
191           VALUES
192           (4,
193               0,
194               'L',
195               @userid,
196               @cur_level_int)
197       end
198   
199       if (@log_for_rep = 1)
200       begin
201           /*
202           ** If the 'master' database is marked for replication, the T-SQL
203           ** built-in 'logexec()' will log for replication the execution
204           ** instance of this system procedure.  Otherwise, the T-SQL
205           ** built-in 'logexec()' is a no-op.
206           */
207           if (logexec(1) != 1)
208           begin
209               raiserror 17756, "sp_displaylevel", "master"
210   
211               if (@log_for_rep = 1)
212                   rollback tran rs_logexec
213               return (1)
214           end
215       end
216   
217       if (@log_for_rep = 1)
218           commit tran rs_logexec
219   
220       exec sp_getmessage 18153, @msg output
221       print @msg, @loginame, @level
222   
223       return (0)
224   


exec sp_procxmode 'sp_displaylevel', 'AnyMode'
go

Grant Execute on sp_displaylevel to public
go
DEFECTS
 MRIT 5 Return in Transaction trancount is 1 213
 MCTR 4 Conditional Begin Tran or Commit Tran 169
 MCTR 4 Conditional Begin Tran or Commit Tran 218
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 129
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 130
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 175
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 176
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 182
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 183
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sp_displaylevel  
 MNER 3 No Error Check should check return value of exec 143
 MNER 3 No Error Check should check @@error after update 180
 MNER 3 No Error Check should check @@error after insert 189
 MNER 3 No Error Check should check return value of exec 220
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 155
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 217
 MUCO 3 Useless Code Useless Brackets 223
 QAFM 3 Var Assignment from potentially many rows 128
 QIWC 3 Insert with not all columns specified missing 10 columns out of 15 190
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}		 129
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}		 175
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}		 182
 VNRD 3 Variable is not read @db_rep_level_all 48
 VNRD 3 Variable is not read @db_rep_level_l1 50
 MSUB 2 Subquery Marker 174
 MTR1 2 Metrics: Comments Ratio Comments: 42% 28
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 21 = 27dec - 8exi + 2 28
 MTR3 2 Metrics: Query Complexity Complexity: 107 28

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..sysattributes (1)