We help IT Professionals succeed at work.

Create table in tempdb

kuk010998
kuk010998 asked
on
1,247 Views
Last Modified: 2008-03-06
Hi guys&gals,

I just noticed that CREATE TABLE rights on tempdb disappear on every SQL Server Service restart. I want users to have that right, so I toyed with the idea to put the GRANT of this right into a startup procedure, which runs under the sa context. I do not, however, want to make tempdb the standard database for sa, so I find myself unable to do what I want - one of my attempts follows:

CREATE PROCEDURE sp_GrantTempCreateTable AS
DECLARE @cmd varchar(255), @og varchar(255)
SELECT @og = char(13) + char(10) + 'G' + 'O' + char(13) + char(10)
SELECT @cmd='CREATE PROCEDURE tempdb.dbo.sp_GrantCreateTable AS GRANT CREATE TABLE, CREATE VIEW TO public'
EXEC (@cmd)
SELECT @cmd='EXEC tempdb.dbo.sp_GrantCreateTable'
EXEC (@cmd)
SELECT @cmd='DROP PROCEDURE tempdb.dbo.sp_GrantCreateTable'
EXEC (@cmd)
GO

sp_makestartup 'sp_GrantTempCreateTable'
GO

Ideas?
Comment
Watch Question

Commented:
Tempdb will be cleared down on every restart, and so any tables defined by users will be lost. Is this what you want?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
vvk

Commented:
Sorry i was wrong about name of daabase. It must be model.
But if you make this updates all new created database will have same list of alias under dbo permissions. You will need to manually remove logins that will not have dbo permissions from this list

Author

Commented:
baryonic - absolutely. As some of the users have Win95, I must prepare for crashes, which could prevent my code from cleaning up. Putting the stuff in tempdb is a logical answer - more so if a service restart is scheduled weekly or so.

vvk - granting the right on model might be the answer! Will test later this day - make that immediately - did it. Should take out the code with the embedded sa password now...

btw, did you know a lack of CREATE TABLE rights does not prevent a DROP TABLE??? Smells like a bug...

Now to rate this - vvk, aliasing everybody to dbo is valid but sub-optimal for my purposes. Still you taught me a valuable lesson just mentioning the model db...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.