?
Solved

Create table in tempdb

Posted on 1998-06-04
4
Medium Priority
?
1,218 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 300 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

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 ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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