SQL Server Performance

Table ownership within procedures

Discussion in 'SQL Server 2005 General Developer Questions' started by rogilvie, Apr 13, 2007.

  1. rogilvie New Member

    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"+@mth+"_t
    ")

    exec("
    create table ##temp"+@mth+"_t
    (
    stuff
    )
    ")

    exec("
    insert into ##temp"+@mth+"_t
    stuff

    ")

    exec("
    insert into [dbmyname].final_table_t select * from ##temp"+@mth+"_t

    ")
  2. Adriaan New Member

    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.
  3. rogilvie New Member

    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.
  4. MohammedU New Member

    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.
  5. satya Moderator

    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.
  6. rogilvie New Member

    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_"+@mth+"_t
    ")

    exec("
    create table ##ro_cust_query_"+@mth+"_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_"+@mth+"_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_"+@mth+"_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_"+@mth+"_t

    ")
    go
  7. rogilvie New Member

    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?
  8. rogilvie New Member

    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.
  9. rogilvie New Member

    problem solved. thanks
  10. satya Moderator

    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.
  11. rogilvie New Member

    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.
  12. Adriaan New Member

    Excellent troubleshooting!

Share This Page