1
2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3
4 create procedure sp_table_privileges(
5 @table_name varchar(257),
6 @table_owner varchar(257) = null,
7 @table_qualifier varchar(257) = null)
8 as
9
10 declare @owner_id int
11 declare @full_table_name varchar(520)
12 declare @tab_id int /* object id of the table specified */
13
14
15 set nocount on
16 /*
17 ** set the transaction isolation level
18 */
19 if @@trancount = 0
20 begin
21 set chained off
22 end
23 else
24 begin
25 /* 18040, "Catalog procedure %1! can not be run in a transaction." */
26 raiserror 18040, "sp_column_privileges"
27 return (1)
28 end
29
30 set transaction isolation level 1
31
32 /*
33 ** Check to see that the table is qualified with the database name
34 */
35 if @table_name like "%.%.%"
36 begin
37 /* 18021, "Object name can only be qualified with owner name." */
38 raiserror 18021
39 return (1)
40 end
41
42 /* If this is a temporary table; object does not belong to
43 ** this database; (we should be in our temporary database)
44 */
45 if (@table_name like "#%" and db_name() != db_name(tempdb_id()))
46 begin
47 /*
48 ** 17676, "This may be a temporary object. Please execute
49 ** procedure from your temporary database."
50 */
51 raiserror 17676
52 return (1)
53 end
54
55
56 /*
57 ** The table_qualifier should be same as the database name. Do the sanity check
58 ** if it is specified
59 */
60 if (@table_qualifier is null) or (@table_qualifier = '')
61 /* set the table qualifier name */
62 select @table_qualifier = db_name()
63 else
64 begin
65 if db_name() != @table_qualifier
66 begin
67 /* 18039, "Table qualifier must be name of current database." */
68 raiserror 18039
69 return (1)
70 end
71 end
72
73 /*
74 ** if the table owner is not specified, it will be taken as the id of the
75 ** user executing this procedure. Otherwise find the explicit table name prefixed
76 ** by the owner id
77 */
78 if (@table_owner is null) or (@table_owner = '')
79 select @full_table_name = @table_name
80 else
81 begin
82 if (@table_name like "%.%") and
83 substring(@table_name, 1, charindex(".", @table_name) - 1) != @table_owner
84 begin
85 /* 18011, Object name must be qualified with the owner name */
86 raiserror 18011
87 return (1)
88 end
89
90 if not (@table_name like "%.%")
91 select @full_table_name = @table_owner + '.' + @table_name
92 else
93 select @full_table_name = @table_name
94 end
95
96 /*
97 ** check to see if the specified table exists or not
98 */
99
100 select @tab_id = object_id(@full_table_name)
101 if (@tab_id is null)
102 begin
103 /* 17492, "The table or view named doesn't exist in the current database." */
104 raiserror 17492
105 return (1)
106 end
107
108
109 /*
110 ** check to see if the @tab_id indeeed represents a table or a view
111 */
112
113 if not exists (select *
114 from sysobjects
115 where (@tab_id = id) and
116 ((type = 'U') or
117 (type = 'S') or
118 (type = 'V')))
119 begin
120 /* 17492, "The table or view named doesn't exist in the current database." */
121 raiserror 17492
122 return (1)
123 end
124
125 /*
126 ** compute the table owner id
127 */
128
129 select @owner_id = uid
130 from sysobjects
131 where id = @tab_id
132
133
134
135 /*
136 ** get table owner name
137 */
138
139 select @table_owner = name
140 from sysusers
141 where uid = @owner_id
142
143
144 /* Create temp table to store results from sp_aux_computeprivs */
145 create table #results_table
146 (table_qualifier varchar(257),
147 table_owner varchar(257),
148 table_name varchar(257),
149 column_name varchar(257) NULL,
150 grantor varchar(257),
151 grantee varchar(257),
152 privilege varchar(257),
153 is_grantable varchar(3))
154
155 exec sp_aux_computeprivs @table_name, @table_owner, @table_qualifier,
156 NULL, 0, @tab_id
157
158 set nocount off
159
160 return (0)
161
162
exec sp_procxmode 'sp_table_privileges', 'AnyMode'
go
Grant Execute on sp_table_privileges to public
go
