Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Unanswered: [SQL2008] Query Editor Says No Permission to Schema

    I'm just starting to use SQL Server 2008. I have created two schemas to separate some tables. One schema is "lists" for master lists of things like statuses, etc. The other schema is "data" for the actual data tables.

    In Management Studio, I am logged in as "sa". My script creates the DB, the two schemas, and so far two tables. However, the query editor is showing the "red squiggly line" under the schema name in the CREATE TABLE statement. The tooltip text says the schema doesn't exist or I do not have permissions to use it.

    The whole script is nearly identical to a previous script to do the same thing for a different, example database. I create the schemas the same way in both scripts, both DBs are owned by "sa", and both are named lists. I don't know why the query editor is balking at one script and not the other.

    Here are the relevant parts of the scripts:

    The original script without the red line:
    Code:
    -- Create schema "data"
    IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'data')
    DROP SCHEMA [data]
    GO
    
    CREATE SCHEMA [data] AUTHORIZATION [db_owner]
    GO
    
    -- Create schema "lists"
    IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'lists')
    DROP SCHEMA 
    [lists]
    GO
    
    CREATE SCHEMA 
    [lists] AUTHORIZATION [db_owner]
    GO
    
    -- snip
    
    CREATE TABLE 
    [lists].[gpo](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [name] [varchar](128) NOT NULL,
        [description] [varchar](512) NULL,
     CONSTRAINT [PK_gpo] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =  OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    The new script with the red line under "
    [lists]" after CREATE TABLE:
    Code:
    -- Create schema "data"
    IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'data')
        DROP SCHEMA [data]
    GO
    
    CREATE SCHEMA [data] AUTHORIZATION [db_owner]
    GO
    
    -- Create schema "lists"
    IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'lists')
        DROP SCHEMA 
    [lists]
    GO
    
    CREATE SCHEMA 
    [lists] AUTHORIZATION [db_owner]
    GO
    
    -- snip
    
    CREATE TABLE 
    [lists].[gpo](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [name] [varchar](64) NOT NULL,
        [description] [varchar](512) NULL,
     CONSTRAINT [PK_gpo] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =  OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    CREATE UNIQUE NONCLUSTERED INDEX [UIX_gpo_name] ON 
    [lists].[gpo] 
    (
        [name] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB =  OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO
    I am new to SQL 2008, so any ideas are appreciated.
    Attached Thumbnails Attached Thumbnails schemas.png  
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have to tell you, I would not create separate schemas for related data under the scenario you describe. You can see how already it is complicating security and permissions.

    I have one schema for the production data (including lists), and then generally add schemas for admin scripts and reporting scripts as well. The idea is that the production data is in no way dependent upon code stored in the admin and reporting schemas.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Okay. It looks like I am not understanding the use of schemas. I'll revisit the topic. Thanks!
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are used to Oracle, schemas in MSSQL are a little conceptually different. Oracle schemas almost correspond more closely to MSSQL databases.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •