CODEWITHSUNDEEP
sqlsql-servertemporal-tables
HelloWoRlD
HelloWoRlD

Reputation: 139

Msg 2760, Level 16, State 1, Line 5 The specified schema name "items" either does not exist or you do not have permission to use it

I am trying to create a temporal table in my SQL Server database. Is anyone familiar with this error in SQL Server:

Msg 2760, Level 16, State 1, Line 5 The specified schema name "items" either does not exist or you do not have permission to use it.

I think there may be something wrong with my user privileges. Any help would be great.

Here's the code:

CREATE TABLE [items].[items_Temporal](
    [itemsID] [int] NOT NULL, 
    [item_Name] [varchar](50) NOT NULL, 
    [item_Number] [int] NOT NULL, 
    [item_Price] [float] NOT NULL, 
    ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, 
    ValidTo datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

    CONSTRAINT [PK_items_Temporal_items1ID] PRIMARY KEY CLUSTERED
    (
        [itemsID] ASC
    ), 
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)

WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = [items].[items_Temporal_History]));

Upvotes: 3

Views: 12272

Answers (2)

jophab
jophab

Reputation: 5519

From the answer of Yogesh, you can find whether the schema exists or not.

If the schema items not exist, try running

CREATE SCHEMA items

If you don't have permission to create Schema,

USE <database_name>;  
    GRANT CREATE SCHEMA TO <user_name>;  
GO

Then create schema and run the code for creating the table.

In the case you have the schema and you are not having the permission to use it,

USE <database_name>;  
    GRANT CREATE TABLE TO <user_name>;  
GO

Then run the code to create table.

Read Docs here

Upvotes: 6

Yogesh
Yogesh

Reputation: 685

Run below query to check actually your are trying to run query in database has item schema in it or not

SELECT * FROM sys.schemas WHERE name = 'item'

If you have rows in above query then check login user from which you are trying to run this query. Also check does this user has rights to create table by this way you will have idea why this error is coming

Upvotes: 1

Related Questions