I've got the following problem that I need help with:
Our team can be roughly divided into 2 groups:
Group 1: Only needs read access to the database (data reader)
Group 2: Needs to be able to do anything within the database (dbo)
Every time a new database is created, we have historically added each user (with either dbo or data reader privileges).
At some point we've realized that it would make sense to create 2 windows domain groups with appropriate users, and then, as a new database is created we'll just grant data_reader access to group_1 and grant dbo access to group_2. And this is what we did, until we ran into the following problem:
Once a user X (which belongs to group_2) creates a new table (without using dbo schema as a prefix), a new schema is created for that user, and this user is created in the database (with a red arrow pointing downward next to his/her name). If dbo.Table_Name is used, then the user and schema are not created.
When I asked our DBA why this is happening, the response was that dbo should always be used in front of the object name when the object is created. This sounds fine, but not always possible to enforce and also when a specific user is created the default schema can be set to dbo, but for the domain group it seems not to be possible.
1. Is what I am observing the expected behavior?
2. How do people deal with the issue - do they enforce all the users to specify dbo or is there a way to set the default schema at the group level?
sql_er, in answer to your question #1, yes this is the expected behavior. When a user creates a table without prefacing it with another schema name or using "dbo" as the default, the table he/she creates will automatically be created in the schema the user owns.
One way to address this is by ensuring that the default schema that's created by users in group_2 are always created in the "dbo" schema. To do this you can do one of two things:
1. Check the default schema that's currently set for the database in question. Change it to dbo if needed.
2. Have your DBA run this statement for each user:
ALTER USER UserName WITH DEFAULT_SCHEMA = NewSchemaName
The proposed solution, however, does not seem to work for the microsoft domain group, to which the users in group_2 belong. That is, I can't assign the default schema to this type of group, which is potentially "SQL Server 2008 R2 and versions before" limitation.
My apologies. I didn't realize that you're on sql 2008. I'm sure you know by now that sql 20012 has solved this problem. You're correct, this is a big issue with both sql 2005 and 2008. My only suggestion would be to have your DBA(s) transfer the offending objects to the dbo user as in the following script example:
alter schema dbo transfer user1.myTable
Obviously you need to consider the consequences of doing this and do full regression testing to make sure that none of your apps/processes break after the change. The draw back with this approach as you know is that you're DBA group will need to run this every so often.
Of course you could setup a template script that your DBA can run at regular intervals to determine if there are any objects that are owned by specific users/domains that should be moved to the dbo user.