x

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

more ▼

asked Jul 10 at 12:56 PM in Default

avatar image

Waqar_lionheart
101 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Jul 11 at 01:18 PM

avatar image

Oleg
18.9k 3 7 28

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

Jul 12 at 12:44 PM Waqar_lionheart

@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.

Jul 12 at 01:00 PM Oleg

@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.

Jul 12 at 01:04 PM Waqar_lionheart

@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 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.

Jul 12 at 01:33 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x108
x43
x18
x13
x8

asked: Jul 10 at 12:56 PM

Seen: 139 times

Last Updated: Jul 12 at 01:33 PM

Copyright 2017 Redgate Software. Privacy Policy