• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1227
  • Last Modified:

Create table in tempdb

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
kuk010998
Asked:
kuk010998
  • 2
1 Solution
 
baryonicCommented:
Tempdb will be cleared down on every restart, and so any tables defined by users will be lost. Is this what you want?
0
 
vvkCommented:
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
 
vvkCommented:
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
 
kuk010998Author 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...
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now