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?
Thanks in advance!