I'm running SQL Server 2012 developer edition on a local machine.                                                                                   When I try to run DDL I just get the error message:          "The specified schema name ["mydbname"] either does not exist or you do not have permission to use it."                      The database name in the query is identical to the database name in the "Databases" list.  I am the dbo.   What might be the cause of this problem?  (Spent hours on it so far)          The query starts with USE dbname    GO

   A

What is your server level permissions ?

Can you post the full error from error log or SSMS ?

1) Does the user you run this query have SELECT permissions on this schema/table

2) Is it case sensitive collation (db/table/schema) names?

My guess is you've might have got the script generated from another server and it seems the schema that script refers is not existing on your current server and database.

"What is your server level permissions ?"

I thought that I had full permission.
How can I check this?

The image below shows the permissions against my

When you say the "Error Log" do you mean the Messages?
If so then there is just a succession of messages like this:
Msg 2760, Level 16, State 1, Line 2
The specified schema name "normadata" either does not exist or you do not have permission to use it.

If you mean the SQL Server Log then I can't see any additions since I started SQL Server this monrning.
I mean that when I execute the query, nothing is added to the current SQl Server Log.

 

1) Does the user you run this query have SELECT permissions on this schema/table

See the permissions image in my answer to Jinu.

2) Is it case sensitive collation (db/table/schema) names?

The collation of the database "normadata" is showing as Latin1_General_CI_AS

What else can I check?

The query script was generated by a tool on the same PC.

Then used copy and paste to add the script to a new query.

The SQLSMS "Databases" list has a database named "normadata".

I created this database using the "New Databases" option in SQLSMS

The first few lines of the query script look like this:

USE normadata
GO

CREATE TABLE normadata.DataType
(
    dataTypeName nchar(30) NOT NULL,
    description nvarchar(max) NOT NULL,
    DCILtype nvarchar(30),
    nrBytes tinyint,
    CONSTRAINT DataType_PK PRIMARY KEY(dataTypeName)
)
GO

GRANT SELECT ON normadata.DataType TO User

Have you ran the above statement?

Jinu, All

A step forward (maybe?)

I deleted the database and re-created it with an explict owner rather than the <default> option that I had been using.Then I ran the query again.

The original error message was:
"The specified schema name "normadata" either does not exist or you do not have permission to use it."
After re-running the query, this error message does not appear.

However, now I have a new error message:
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

???? 
Ken

Note: This is runnning in Win 7 Pro on a 64 bit high spec PC

And two steps back!

I stopped and restarted SQL Server and then re-ran the query.

Back to the original error messages :-(

Msg 2760, Level 16, State 1, Line 2
The specified schema name "normadata" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Line 3
The specified schema name "normadata" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Line 3
The specified schema name "normadata" either does not exist or you do not have permission to use it.
Msg 4902, Level 16, State 1, Line 3
Cannot find the object "normadata.DataTypeIsInGroup" because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 3
Cannot find the object "normadata.DataTypeIsUsedInSQLSVersion" because it does not exist or you do not have permissions.

Uri,

Why should I need to be this specific - doesn't my sysadmin credential include this?

Anyway, when I ran the query:

GRANT SELECT ON normadata.DataType TO User

I got this error message:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'User'

Ups, I missed the statement where you said that you are a sysadmin.

Please answer my second question I posted above

>>2) Is it case sensitive collation (db/table/schema) names?

Uri,

>>2) Is it case sensitive collation (db/table/schema) names?

Sorry, I'm not exactly sure how to check this or what you want me to check.
Does this help?

All,

To try to make progress, I used the SQL Server 2012 installation DVD to repair the installation.
Then I restarted the PC.

Here is the Logfile from the restart.

http://1drv.ms/1EICmQr

Does this help?

Yes, it is NOT case sensitive BUT I am not able to reproduce the problem

CREATE LOGIN uri1 WITH PASSWORD = 'test'

ALTER SERVER ROLE [sysadmin] ADD MEMBER [uri1]


CREATE DATABASE normadata
GO


USE normadata
GO
CREATE USER [uri] FOR LOGIN [uri1] WITH DEFAULT_SCHEMA=[DBO]

CREATE SCHEMA normadata AUTHORIZATION uri
GO
CREATE TABLE normadata.DataType
(
    dataTypeName nchar(30) NOT NULL,
    description nvarchar(max) NOT NULL,
    DCILtype nvarchar(30),
    nrBytes tinyint,
    CONSTRAINT DataType_PK PRIMARY KEY(dataTypeName)
)

SELECT * FROM normadata.DataType ----does  not throw an error

 

Hi Uri,

Thanks for your help - which made me think a lot.
The good news: I have now fixed the problem.

The bad news: I don't understand why the original SQL query did not work.

Here is the procedure I used to find a solution:
1) Script a table from one of my databases that worked.
2) The scripted query had the following differences:
a) USE [normadata]   not USE normadata

b) The table references said "dbo.tablename" rather than "normadata.tablename"

So I:

a) added the square brackets: USE [normadata]

b) changed all of the table references from normadata.tablename to dbo.tablename

c) ran the query and it completed successfully.

Is this normal SQL Server 2012 behaviour?

>>>Is this normal SQL Server 2012 behaviour?

No but I have a suspicion that you have some space in the name of the database.. If you copy the database  name (right click on db name in SSMS--rename and then copy) to the notepad, do  you some spaces  within a name like

  norm adata  or even ' normadata'

or something like that?

Good suggestion but the notepad test shows "normadata" and nothing else. No spaces.
Any other ideas Sherlock? :-)

You meant "normadata" , am I right?

>>>b) changed all of the table references from normadata.tablename to >>>dbo.tablename

Why id you change to DBO schema? If your login is a member of sysadmin server role, there is no difference to issue SELECT & FROM schema.tbl or dbo.tbl. If not can you run the below statement 

-- give Ken permission on normadata schema

GRANT ALTER ON SCHEMA::normadata TO Ken 

Yes "normadata" - typo now fixed in earlier message.

I used the dbo prefix because it worked.

Here is the script of my login:

=================================
USE [master]
GO
/****** Object:  Login [D8700\Ken]    Script Date: 06/09/2015 14:43:29 ******/
CREATE LOGIN [D8700\Ken] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [D8700\Ken]
GO
============================

So since I'm a sysadmin, what is the point of using: 
GRANT ALTER ON SCHEMA::normadata TO Ken

Isn't this already granted within sysadmin?

I tried

USE [normadata]
GO
GRANT ALTER ON SCHEMA::normadata TO Ken

GO

===========

this is the result
Msg 15151, Level 16, State 1, Line 1
Cannot find the schema 'normadata', because it does not exist or you do not have permission.

======================

I also tried GRANT ALTER ON SCHEMA::normadata TO [D8700\Ken]- - Same error message

b) changed all of the table references from normadata.tablename to dbo.tablename

The root cause of the error was the malformed qualified object name, a 2-part name that specified a database name instead of schema name.  A 2-part name is interpreted as <SchemaName>.<ObhectName> but <DatabaseName>.<ObhectName> was specified.  The error message makes sense because "normadata" is not a schema.

A 3-part name is needed for names qualified with a database name. The schema name must be included, or an empty place holder to indicate the default schema:

<DatabaseName>.<SchemaName>.<ObjectName>
<DatabaseName>..<ObjectName>

Ken, normadata is a database, not a schema. If you want to grant ALTER permissions on the dbo schema to a user, the correct syntax is:

USE normadata;
GRANT ALTER ON SCHEMA::dbo TO Ken;

"A 3-part name is needed for names qualified with a database name. The schema name must be included, or an empty place holder to indicate the default schema:"

<DatabaseName>.<SchemaName>.<ObjectName>

Thanks Dan.
That makes it clear that I have been using DatabaseName and SchemaName as synonyms.
So to make it crystal clear , what would be the schema name?
mormadata,???,tablename

Can you give me an example drawn from the following snippet?

===================

USE [normadata]
GO

CREATE TABLE dbo.DataType
(
    dataTypeName nchar(30) NOT NULL,
    description nvarchar(max) NOT NULL,
    DCILtype nvarchar(30),
    nrBytes tinyint,
    CONSTRAINT DataType_PK PRIMARY KEY(dataTypeName)
)
GO
======================

In other words, within SQL Server 2012, what's a schema?

In other words, within SQL Server 2012, what's a schema?

A schema is basically a namespace, logically part of the object name.  In your script, the USE statement specifies the database name ("normadata") and the CREATE TABLE statement specifies both the schema name ("dbo") and table name ("DataType"). 

The USE statement sets the database context so subsequent 1-part and 2-part names will refer to objects in this database.  You can use 3-part object names but that is redundant when the object exists in the current database.  Schema-qualified (2-part) names are a best practice to avoid ambiguity since different objects with the same object name may exist in different schemas.  Below are Examples.

-- This 1-part name references table "DataType" in your default schema (normally "dbo") in the current database ("normadata")
SELECT * FROM DataType;

-- This 2-part name references table "DataTupe" in the "dbo" schema in the current database ("normadata")
SELECT * FROM dbo.DataType;

-- This 3-part name references object "dbo.DataType" in the "normadata" database regardless of current database context
SELECT * FROM normadata.dbo.DataType;

Hi Dan,

That is very helpful.

Thanks a lot.