locked
Schema name same as User name RRS feed

  • Question

  • Hi.

    In some our dotabases I can see Schemas created with the same name as Domain User name (domain\username). Schema owner for those schemas is not dbo but the same user as in schema name. How this happens? Is any way to prevent or prohibit this?

    thanks

    Sunday, June 28, 2015 2:04 PM

Answers

  • What version of SQL Server?  In general the way to prevent this is to ensure that all the users have DEFAULT_SCHEMA set to dbo.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Sunday, June 28, 2015 2:47 PM
  • One way this can happen is if the deprecated sp_grantdbaccess proc is used instead of a CREATE USER DCL statement.  That proc creates a schema for the user for backwards compatibility.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by jori5 Monday, June 29, 2015 6:44 AM
    Sunday, June 28, 2015 4:27 PM
  • One way this can happen is if the deprecated sp_grantdbaccess proc is used instead of a CREATE USER DCL statement.  That proc creates a schema for the user for backwards compatibility.

    The same is true if you create users with the even older sp_adduser.

    In both these cases, the user's default schema is also set to their eponymous schema.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by jori5 Monday, June 29, 2015 6:44 AM
    Sunday, June 28, 2015 8:58 PM

All replies

  • Jori,

    Here is a link how to create schemas: https://msdn.microsoft.com/en-us/library/ms189462.aspx?f=255&MSPPError=-2147217396

    And to prohibit, use SQL policy management: https://msdn.microsoft.com/en-us/library/bb522659.aspx?f=255&MSPPError=-2147217396

    Hope it helps!


    Please click "Mark As Answer" if my post helped.

    Sunday, June 28, 2015 2:45 PM
  • What version of SQL Server?  In general the way to prevent this is to ensure that all the users have DEFAULT_SCHEMA set to dbo.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Sunday, June 28, 2015 2:47 PM
  • One way this can happen is if the deprecated sp_grantdbaccess proc is used instead of a CREATE USER DCL statement.  That proc creates a schema for the user for backwards compatibility.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by jori5 Monday, June 29, 2015 6:44 AM
    Sunday, June 28, 2015 4:27 PM
  • One way this can happen is if the deprecated sp_grantdbaccess proc is used instead of a CREATE USER DCL statement.  That proc creates a schema for the user for backwards compatibility.

    The same is true if you create users with the even older sp_adduser.

    In both these cases, the user's default schema is also set to their eponymous schema.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by jori5 Monday, June 29, 2015 6:44 AM
    Sunday, June 28, 2015 8:58 PM
  • In both these cases, the user's default schema is also set to their eponymous schema.

    Learned a new word today, and from a non-native speaker of the language :-)


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, June 29, 2015 10:25 AM
  • And..., correct me if I'm incorrect: If you upgrade a 2000 database, there will be a schema created for each user in the database - regardless of whether that user owned objects or not.

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, June 29, 2015 7:13 PM
  • And..., correct me if I'm incorrect: If you upgrade a 2000 database, there will be a schema created for each user in the database - regardless of whether that user owned objects or not.

    Yup, of course there will be, since this will be the default schema of these users.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, June 29, 2015 9:44 PM