I have a small problem with implicit schema creation. I have several members in my dev team that are member of a group (domain\devgroup). The problem is that all of these group member but one when creating a table or an object use the dbo schema where this one user when logs in and creates any object creates a schema and a user for himself in the database (this one user is not explicitly define anywhere on the server or on the database). Anything I can do other than asking him to qualify his create statements? Still this begs the question, why only one?
asked Jul 10, 2017 at 12:56 PM in Default
One of the properties of the database user is called "Default schema". In your case, you don't have the users explicitly defined because you have created a login for members of domain\\devgroup and then mapped the login to database(s). So in every mapped database you only see but one user for that login, and anyone who is a member of the AD group has access.
Open the property pages of that login from Security -> Logins. Click User Mapping in the "select a page" frame. There you can observe the value of the "Default Schema" value for the "users mapped to this login". The odds are that the current value is domain\\devgroup and this causes a problem when the user in question does not qualify the tables' names. Change the value to dbo and save your changes. This way, every table created by any user will default to **dbo"" schema if they don't qualify the table name when creating one.
That said, it is a very poor practice to NOT qualify the table names and you should instruct the users of your databases to always do. This includes their DML statements such as select, insert and update.
Hope this helps.
answered Jul 11, 2017 at 01:18 PM