Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 279
Raju Avatar asked Sep 05 '25 17:09

Raju


2 Answers

I went into the database, security, users, right-click on the user, properties. Membership, checked db_owner.

like image 172
Phillip Senn Avatar answered Sep 07 '25 18:09

Phillip Senn


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 #.

like image 36
Ram Avatar answered Sep 07 '25 18:09

Ram