If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Users, Groups, and Schemas

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Posts: 56
Users, Groups, and Schemas

Hi guys,

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.

Questions:

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!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Location: Woodland Hills, CA
Posts: 18
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Location: Woodland Hills, CA
Posts: 18
It goes without saying that you should perform full regression testing in your QA environment before making these changes in your production environment.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Posts: 56
YesAgile,

Thank you for your reply.

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.

Please let me know with any other suggestions.

Thanks in advance!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Location: Woodland Hills, CA
Posts: 18
sql_er,
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.

YesAgile
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Posts: 56
YesAgile,

Thank you for the suggestions!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On