locked
User Schema Enumeration RRS feed

  • Question

  • Hi,

    How do I get the list of user-created (non system) schemas from sys.schemas
    (or where ever) in SQL 2008?  I've come up with a couple of queries that
    work in simple cases, but I can think of ways to break them in allowable
    circumstances.

    select *
    from sys.schemas
    where name not in (
      select name from model.sys.schemas
    )

    select *
    from sys.schemas
    where name not in (
      select name from sys.database_principals
    )
     
    select *
    from sys.schemas
    where schema_id < 16000
    and schema_id not in (1,2,3,4)

    I'm just not finding a "is_system_schema" (or "is_user_schema") flag.

    --
    Thank you,

    Daniel Jameson
    SQL Server DBA
    Children's Oncology Group
    www.childrensoncologygroup.org


    • Edited by JediSQL Saturday, March 14, 2009 3:07 AM
    Wednesday, March 4, 2009 1:23 AM

Answers

  • Raul,

    I tried your suggestion above.  Your query returns a lot of users tied to SQL Server Authentication logins.  Here is the query that seems to work for me:

    select  *  
    from    sys.schemas  
    where   schema_id < 16000 -- no fixed database roles
    and     schema_id not in (1,2,3,4)  -- no fixed database users
    and     name not in (select name from sys.database_principals) -- no CREATE USER users

    The "schema_id < 16000" condition accomplishes the same as your "users.is_fixed_role <> 1".
    • Edited by JediSQL Friday, March 20, 2009 11:33 PM clarify
    • Marked as answer by JediSQL Friday, March 20, 2009 11:33 PM
    Friday, March 20, 2009 11:32 PM

All replies

  •  SQL server has a loose notion of a system schema. Pretty much the system schemas are:

    Schema name

    Schema Id

    dbo

    1

    guest

    2

    INFORMATION_SCHEMA

    3

    sys

    4

     

    Any other schema is user defined.

    I hope this information helps,


    -Raul garcia
      SDE/T
      SQL Server Engine
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 4, 2009 1:29 AM
  • In sys.schemas I'm finding every user added to the database.  Even the fixed database roles exist in sys.schemas.  I want a list of only the ones I created with the "CREATE SCHEMA" command.

    - Dan
    Wednesday, March 4, 2009 7:54 PM
  •    As I mentioned, system schemas are limited to the ones I mentioned before. The schemas created out of the box for the out-of-the-box principals (DB fixed roles) exist for backwards compatibility since in SQL Server 2000 schemas and users were tightly coupled.

      Also, for backwards compatibility, when using the old SPs to create users instead of the CREATE USER DDL, a schema with the same name as the user will be automatically created.

      Unfortunately there is no MD distinction between a schema created implicitly (i.e. as a side effect of creating a user) and the ones explicitly created.

      The following query should help you to find the schemas not created by default, but it will show all other schemas (implicitly and explicitly created):

    1 SELECT schms.* FROM   
    2       sys.schemas schms,  
    3       sys.database_principals users  
    4       WHERE schms.principal_id = users.principal_id  
    5         AND schms.schema_id not in (1,2,3,4)  
    6         AND users.is_fixed_role <> 1  
    7 go  


      I hope this information helps.

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, March 5, 2009 4:26 AM
  • Raul,

    Thank you.  At least now I know I'm not crazy.  I'm always surprised at some of the things that are overlooked.

    BTW, what does MD stand for?

    - Dan

     

    Friday, March 13, 2009 3:06 AM
  • Raul,

    I tried your suggestion above.  Your query returns a lot of users tied to SQL Server Authentication logins.  Here is the query that seems to work for me:

    select  *  
    from    sys.schemas  
    where   schema_id < 16000 -- no fixed database roles
    and     schema_id not in (1,2,3,4)  -- no fixed database users
    and     name not in (select name from sys.database_principals) -- no CREATE USER users

    The "schema_id < 16000" condition accomplishes the same as your "users.is_fixed_role <> 1".
    • Edited by JediSQL Friday, March 20, 2009 11:33 PM clarify
    • Marked as answer by JediSQL Friday, March 20, 2009 11:33 PM
    Friday, March 20, 2009 11:32 PM