1
2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3
4 create procedure sp_column_privileges(
5 @table_name varchar(257),
6 @table_owner varchar(32) = null,
7 @table_qualifier varchar(32) = null,
8 @column_name varchar(257) = null)
9
10 as
11
12 declare @owner_id int
13 declare @full_table_name char(287)
14 declare @tab_id int /* object id of the table specified */
15
16 set nocount on
17 /*
18 ** set the transaction isolation level
19 */
20 if @@trancount = 0
21 begin
22 set chained off
23 end
24 else
25 begin
26 /* we are inside a transaction. catalog procedure sp_column privileges
27 ** can't be run inside a transaction
28 */
29 raiserror 18040, "sp_column_privileges"
30 return (1)
31 end
32
33 set transaction isolation level 1
34
35 /*
36 ** Check to see that the table is qualified with database name
37 */
38 if @table_name like "%.%.%"
39 begin
40 /* 18021, "Object name can only be qualified with owner name" */
41 raiserror 18021
42 return (1)
43 end
44
45 /* If this is a temporary table; object does not belong to
46 ** this database; (we should be in our temporary database)
47 */
48 if (@table_name like "#%" and db_name() != db_name(tempdb_id()))
49 begin
50 /*
51 ** 17676, "This may be a temporary object. Please execute
52 ** procedure from your temporary database."
53 */
54 raiserror 17676
55 return (1)
56 end
57
58 /*
59 ** The table_qualifier should be same as the database name. Do the sanity check
60 ** if it is specified
61 */
62 if (@table_qualifier is null) or (@table_qualifier = '')
63 /* set the table qualifier name */
64 select @table_qualifier = db_name()
65 else
66 begin
67 if db_name() != @table_qualifier
68 begin
69 raiserror 18039
70 return (1)
71 end
72 end
73
74 /*
75 ** if the table owner is not specified, it will be taken as the id of the
76 ** user executing this procedure. Otherwise find the explicit table name prefixed
77 ** by the owner id
78 */
79 if (@table_owner is null) or (@table_owner = '')
80 select @full_table_name = @table_name
81 else
82 begin
83 if (@table_name like "%.%") and
84 substring(@table_name, 1, charindex(".", @table_name) - 1) != @table_owner
85 begin
86 /* 18011, Object name must be qualified with the owner name * */
87 raiserror 18011
88 return (1)
89 end
90
91 if not (@table_name like "%.%")
92 select @full_table_name = @table_owner + '.' + @table_name
93 else
94 select @full_table_name = @table_name
95
96 end
97
98 /*
99 ** check to see if the specified table exists or not
100 */
101
102 select @tab_id = object_id(@full_table_name)
103 if (@tab_id is null)
104 begin
105 raiserror 17492
106 return (1)
107 end
108
109
110 /*
111 ** check to see if the @tab_id indeeed represents a table or a view
112 */
113
114 if not exists (select *
115 from sysobjects
116 where (@tab_id = id) and
117 ((type = 'U') or
118 (type = 'S') or
119 (type = 'V')))
120 begin
121 raiserror 17492
122 return (1)
123 end
124
125
126 /*
127 ** if the column name is not specified, set the column name to wild
128 ** character such it matches all the columns in the table
129 */
130 if @column_name is null
131 select @column_name = '%'
132
133 else
134 begin
135 /*
136 ** check to see if the specified column is indeed a column belonging
137 ** to the table
138 */
139 if not exists (select *
140 from syscolumns
141 where (id = @tab_id) and
142 (name like @column_name))
143 begin
144 raiserror 17563, @column_name
145 return (1)
146 end
147 end
148
149 /* Create temp table to store results from sp_aux_computeprivs */
150 create table #results_table
151 (table_qualifier varchar(32),
152 table_owner varchar(32),
153 table_name varchar(257),
154 column_name varchar(257) NULL,
155 grantor varchar(32),
156 grantee varchar(32),
157 privilege varchar(32),
158 is_grantable varchar(3))
159
160
161 /*
162 ** declare cursor to cycle through all possible columns
163 */
164 declare cursor_columns cursor
165 for select name from syscolumns
166 where (id = @tab_id)
167 and (name like @column_name)
168
169 /*
170 ** For each column in the list, generate privileges
171 */
172 open cursor_columns
173 fetch cursor_columns into @column_name
174 while (@@sqlstatus = 0)
175 begin
176
177 /*
178 ** compute the table owner id
179 */
180
181 select @owner_id = uid
182 from sysobjects
183 where id = @tab_id
184
185
186 /*
187 ** get table owner name
188 */
189
190 select @table_owner = name
191 from sysusers
192 where uid = @owner_id
193
194 exec sp_aux_computeprivs @table_name, @table_owner, @table_qualifier,
195 @column_name, 1, @tab_id
196
197 set nocount off
198
199 fetch cursor_columns into @column_name
200 end
201
202 close cursor_columns
203 deallocate cursor cursor_columns
204
205 /* Print out results */
206 /* Adaptive Server has expanded all '*' elements in the following statement */ select distinct #results_table.table_qualifier, #results_table.table_owner, #results_table.table_name, #results_table.column_name, #results_table.grantor, #results_table.grantee, #results_table.privilege, #results_table.is_grantable into #temp_result from #results_table
207
208 exec sp_autoformat @fulltabname = #temp_result,
209 @orderby = "order by column_name, grantee"
210
211 drop table #temp_result
212 drop table #results_table
213 return (0)
214
exec sp_procxmode 'sp_column_privileges', 'AnyMode'
go
Grant Execute on sp_column_privileges to public
go