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... :DYour 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?|||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 :o|||2-column 100-row? Well, I guess your "DBA" doesn't trust your code ;)
We can trick your DBAby avoiding CREATE statements all together:
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|||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?|||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
dreameR.78 wrote:
