Tempdb datareader revoke when restart server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Tempdb datareader revoke when restart server

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2004-01-13 : 01:25:16

Dear All,
There is a user who is granted with datareader role of tempdb. However, everytime the server is reboot, the datareader of tempdb is revoked.

Is there any problem with this?

Thanks.

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-01-13 : 02:19:37

The tempdb database is everytime recreated when the server is restarted, so the changes are revoked.

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

mr_mist
Grunnio

1870 Posts

Posted - 2004-01-13 : 04:01:50

It's created based on the model db, so if you wanted you could assign permission to that. Of course, all the other dbs you create are based on that, too...

I'm not quite sure what you were asking, though.

-------
Moo. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-13 : 11:57:29

Why does ANYONE need datareader role in the tempdb database?

Tara

geossl
Yak Posting Veteran

85 Posts

Posted - 2004-01-14 : 03:00:51

I reason why is I am using the dynamic crosstab stored procedure and it takes this server role.

quote:
Originally posted by tduggan

Why does ANYONE need datareader role in the tempdb database?

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-01-14 : 03:49:25

Only temp tables should be put in tempdb, and SQL Server handles this automatically. You should never explicitely create a table or any other database object in tempdb. If your crosstab proc is doing this, it is probably a fairly simple matter to fix it.

-ec

geossl
Yak Posting Veteran

85 Posts

Posted - 2004-01-14 : 04:57:10

I am using the stored proc in [url]http://www.sqlteam.com/item.asp?ItemID=2955[/url] . It must be granted with datareader role in tempdb, whereas there will be strange error...

quote:
Originally posted by eyechart

Only temp tables should be put in tempdb, and SQL Server handles this automatically. You should never explicitely create a table or any other database object in tempdb. If your crosstab proc is doing this, it is probably a fairly simple matter to fix it.

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-14 : 12:11:21

You don't need to grant access to anyone for tempdb. What is the error that you are receiving?

Tara

geossl
Yak Posting Veteran

85 Posts

Posted - 2004-01-14 : 21:16:00

In the stored proc,

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

It will return different information and cause the proceeding statement to fail without the datareader role in tempdb.

Any recommendation?

quote:
Originally posted by tduggan

You don't need to grant access to anyone for tempdb. What is the error that you are receiving?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 12:12:24

The stored procedure should not be referencing tempdb.

The INFORMATION_SCHEMA views are located in the master database. But you do not need to specify master.

SELECT *
FROM INFORMATION_SCHEMA.TABLES

The above code will work from any database.

Tara

geossl
Yak Posting Veteran

85 Posts

Posted - 2004-01-15 : 22:31:23

This doesn't work.
The INFORMATION_SCHEMA in master lacks a lot of fields....

quote:
Originally posted by tduggan

The stored procedure should not be referencing tempdb.

The INFORMATION_SCHEMA views are located in the master database. But you do not need to specify master.

SELECT *
FROM INFORMATION_SCHEMA.TABLES

The above code will work from any database.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-16 : 12:14:36

Then what are you doing? We could come up with a solution that works if you let us know what you need.

Don't add any objects to tempdb directly. Add them to a different database.

Tara

geossl
Yak Posting Veteran

85 Posts

Posted - 2004-01-18 : 20:32:14

Thanks Tara.

The purpose of the stored procedure is provide dynamic cross table result set given the sql statement and the pivot fields as paramters. The stored proc. creates a temp table to aggregate the data, and as it is dynamic in nature, it gets the column name by reading the table schema in the tempdb.

The crosstab stored proc. is at [url]http://www.sqlteam.com/ItemPrint.asp?ItemID=2955[/url]
as attached:


CREATE PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

Thanks.

quote:
Originally posted by tduggan

Then what are you doing? We could come up with a solution that works if you let us know what you need.

Don't add any objects to tempdb directly. Add them to a different database.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources