sql-servercreate-table

CREATE TABLE permission denied in database 'tempdb'


First time I installed SQL Server Management Studio Express and Visual Studio 2005 on my system. Now I am trying to create table by using below script. But if once I execute I am facing error like

CREATE TABLE permission denied in database 'tempdb'.

Why it it? Can anyone help me how to resolve this ?

USE [tempdb]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee]
([FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Thanks Raju


Solution

  • My initial guess is you do not have CREATE table rights on this server. Can you please run these set of queries below?

    
    -- get login first 
    select suser_name() 
    --or 
    SELECT SYSTEM_USER
    
    -- Now get the permissions assigned to you by the server administrator 
    use tempDB 
    GO 
    
    ;with getPermissions as ( SELECT * FROM fn_my_permissions (NULL, 'DATABASE') ) 
    select permission_name from getPermissions 
    where permission_name like 'create%' 
    GO
    
    

    If permission_name column returns 0 rows then it means you do not have CREATE permission on this DB. contact your DBA to grant db_ddladmin for tempDB. However as Andomar noted the temp tables are automatically created in tempDB when pre-appended with #.