SQL Server Performance Forum – Threads Archive
Table ownership within procedures
I have written a procedure which creates several temporary tables within it. I have granted exec for the procedure and insert + select for the final table to other users, but find that this will not work when other users try to run it. See the code below for a brief description. Any thoughts / ideas to circumvent this would be appreciated. Thanks create proc procedure_p @mth as char(6) as exec("drop table ##temp"[email protected]+"_t
") exec("
create table ##temp"[email protected]+"_t
(
stuff
)
") exec("
insert into ##temp"[email protected]+"_t
stuff ") exec("
insert into [dbmyname].final_table_t select * from ##temp"[email protected]+"_t ")
Not quite sure what to make of this seemingly two-part object name – [dbmyname].final_table_t – do you have a login called "dbmyname"? Does the current db have several copies of the final_table_t table, owned by various logins? If you want the data to be inserted into the copy of the table owned by the current login, then do not mention the specific owner.
Yes, that is my login – i posted it a little obtusely. Just think of it as [myname]. There is only one copy of the final table and is listed under my ownership. I want other users to be able to run my procedure and insert into my table when I am unable to do so myself. I dont want them to create copies of the table as that would require us to redirect several pivots.
What are you doing in your procedure… You may the error when you are dropping the table without checking the existence… You are using global temp table… it can be seen by others as long as connection is open…so if one user created the table other can drop… Can post the complete code… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
You can use EXECUTE AS privilege for the users that are not in the privileged role, checkhttp://www.sommarskog.se/grantperm.html link. Also the possibility is to use a view or table valued function by granting the SELECT permission. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thanks for all the suggestions so far. A full listing of the procedure code is given below: The other users will just want to run a piece of code along the lines of:
ro_cust_query_p ‘200701’ ——————————————————- set quoted_identifier off
go
drop proc ro_cust_query_p
go
create proc ro_cust_query_p @mth as char(6) as exec("
drop table ##ro_cust_query_"[email protected]+"_t
") exec("
create table ##ro_cust_query_"[email protected]+"_t
(
acct_id bigint null,
acct_nmbr char (16) null,
identifier varchar (100) null,
prin_name varchar (100) null,
first_name varchar (100) null,
sur_name varchar (100) null,
stmt_mth1 char(6) null,
stmt_mth char (10),
stmt_year numeric (4),
proc_date varchar (20) null,
ovlimfee numeric (13,2),
latefee numeric (13,2)
)
")
exec("
insert into ##ro_cust_query_"[email protected]+"_t
select
a.acct_id
,a.acct_nmbr
,a.identifier
,a.prin_name
,a.first_name
,a.sur_name
,convert (char(6),b.date_maint,112) as stmt_mth1
,datename(month,b.date_maint) as stmt_mth
,datepart(year,b.date_maint) as stmt_year
,cast ((datename(day,getdate())+’ ‘+ datename(month,getdate())) as varchar) as proc_date
,ovlimfee = sum(isnull(b.ovlim_fee_amnt,0))
,latefee = sum(isnull(b.late_fee_amnt,0))
from ##ro_cust_query2_t a left join cardsuser..per_trans_accums_"[email protected]+"_v b on a.acct_id = b.acct_id
group by
a.acct_id
,a.acct_nmbr
,a.identifier
,a.prin_name
,a.first_name
,a.sur_name
,convert (char(6),b.date_maint,112)
,datename(month,b.date_maint)
,datepart(year,b.date_maint) insert into [europaogilvir].ro_cust_query_weekly_output_t select * from ##ro_cust_query_"[email protected]+"_t ")
go
One of my worries was that if I create a temporary table using someone else’s procedure the ownership of the table created will default to the owner of the procedure. Is this the case?
Another problem: I’m pretty sure the version of SQL we are using is pre-SQL 2005, so I can’t implement SELECT AS or create certificates as far as I can tell.
problem solved. thanks
If that temp. table is created then it will be valid until that session is open and it will not be associated with another owner within the database, it will be nice how you have resolved the issue. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Fairly basic error on my part. The referencing for the procedure did not include the username extension. Bizarrely the user trying to run the procedure had managed to create an identically named procedure under his own name, making it possible for the code to run (and hence difficult to spot the basic mistake). Thus any modifications i had made to my procedure were not carried through to the code he was running.
Excellent troubleshooting!
]]>