Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Talking Unanswered: Permission to use table variable?

    I am trying to get around business logic that does not give the user permissions to create any temp tables or tables.

    If a user falls into this category, will they still be allowed to create "table variables"? (Declare @x table (i int)

    ...I'm obviously trying to find a way around this permission issue.

    Thanks...

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Your question sounds strange...Why do you need users to have CREATE TABLE permissions? And creating temporary tables permission is transcendent from the default database security settings of TEMPDB and PUBLIC role...Unless whoever setup your business rules messed it up?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2004
    Posts
    53
    I definitely need to clarify...I am writing stored procedures that require the use of temp tables or some sort of temporary storage for several result sets.

    So I am investigating using table variables as another option because my stored procedures will not be giving my DBA's "blessing"....

    He wants to eliminate all use of temp tables and he'll have a hissy fit if I have "create table" anywhere in my code. He also stated that the users are not allowed to create tables. I am not sure if that's how he set it up but that's what I was told.

    I just want to make sure I won't have any issues with permissions he might have set up which is why I am looking at using table variables instead.

    The "table" itself will have two columns and probably 100 rows (from the result sets).

    My specific question would be, when initializing a table variable, is it treated just like any other variable?

    Or is there potential that I can't declare a table variable if I am also not allowed to create temp tables?

    Hope thats some what clearer...thanks

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    2-column 100-row? Well, I guess your "DBA" doesn't trust your code

    We can trick your DBAby avoiding CREATE statements all together:
    Code:
    exec sp_addlogin 'test1', 'test1'
    exec sp_adduser 'test1'
    go
    create table test (f1 int null, f2 int null)
    insert test select 1,1 union select 2,2
    go
    create procedure sp_test2 as select * into #tmp from dbo.test; select * from #tmp
    go
    grant execute on sp_test2 to public
    go
    setuser 'test1'
    exec sp_test2
    setuser
    go
    drop procedure sp_test2
    drop table test
    go
    exec sp_dropuser 'test1'
    exec sp_droplogin 'test1'
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2004
    Posts
    53
    sigh...how I wish I could do something like this He will have to see this code as well and since there is a create table statement in it, I dont think it'll go through

    I have a test database that I can run this on but I dont think it'll go to production.

    Is it a bad idea to use a table variable to hold a two column 100 row set of data?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, if creating temp tables is the only thing your DBA is worried about, - I'd try to look for a replacement, or a better job. With memory tables (variable tables, SQL arrays, whatever) I can kill your server even faster, why bother with tempdb when you can get the brain of the machine much faster?
    declare @tbl table (col1 int null, col2 int null)
    declare @cnt int
    select @cnt = count(*) from @tbl
    while (case when (0>=@cnt) then 1 else @cnt end) >= 1 or /*should be AND */ @cnt < 100 begin
    insert @tbl select (select count(*)-1 from @tbl where col2-1 > col1-2),
    (select count(*)+1 from @tbl where col2+1 < col1+2)
    select @cnt = count(*) from @tbl
    end
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •