Solved

Create table in tempdb

Posted on 1998-06-04
4
1,215 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?
0
Comment
Question by:kuk010998
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Expert Comment

by:baryonic
ID: 1091427
Tempdb will be cleared down on every restart, and so any tables defined by users will be lost. Is this what you want?
0
 
LVL 4

Accepted Solution

by:
vvk earned 100 total points
ID: 1091428
May be i 'm not clear about your problem. I think you can do following
In Enterprise manager go to tempdb->Groups/Users->public ->dbo doubleclick on it. Select from available logins names you need and click Add. now this users has dbo permissions in database.

0
 
LVL 4

Expert Comment

by:vvk
ID: 1091429
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
0
 
LVL 1

Author Comment

by:kuk010998
ID: 1091430
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...
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question