asked
problem with implicit schema creation
Hi All, 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? Regards, Waqar
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. Oleg
@Waqar_lionheart The only way it is possible if the SQL Server cannot determine the default schema for the user who is a member of multiple AD groups where some of these groups do exist as logins. In this case, dbo schema is used. However, if the SQL Server can determine the default schema (for example when the user is a member of only one group which has been created with default schema named after the login name) then this default schema is used, as is the case with only one of your developers. I am not sure exactly how it works in 2008 R2, but in 2012+ for those folks who are members of multiple groups and the groups exists as logins, the default schema of the AD group with the lowest ID (set when the group is first created in AD) is used. There is a paragraph in the remarks section of the [CREATE USER][1] page explaining the details. The bottom line is that not all your devs have "the same DNA", one of them is a member, or not, of some different AD group. Also possible is the situation when all but one devs are also somehow members of the sysadmin role which ignores default schema specs. [1]:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql
Oleg, I had already tried that but it says I can't set a default schema for a group account or an account mapped to credentials. My other problem with this mystery is that all other members are doing just fine. grrrrrh
@Waqar_lionheart This means that the SQL Server instance you have is 2008 R2 or earlier, this was not mentioned in your question. It is true that assigning a default schema to the login created from the AD group is not possible in these versions of SQL Server. The issue has been resolved in all versions beginning with SQL Server 2012. There you can assign default schema either via SSMS User Mapping or simply by the means of the script: alter user [domain\devgroup] with default_schema = dbo; go Unfortunately, this means that until you upgrade your instance to 2012 or newer you are going to have to force the developers to never forget to fully qualify the object names. Perhaps 10 push-ups per violation should do the trick.
@oleg: Many thanks for the quick reply. lolz for the push-ups. Yes I second that. I knew all of this before but whats bugging me is that why only one member of dev team. The rest are automatically defaulting to dbo schema. My company will be upgrading soon. At least thats the plan to I think 2014 may be and this should go away.
18 People are following this question.
