Solved

Create table in tempdb

Posted on 1998-06-04
4
1,217 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

726 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