| Author |
Topic |
|
larus
Starting Member
17 Posts |
 Posted - 2010-06-09 : 13:08:29
|
| I have an Active Directory group which has db_ddladmin rights for certain database. I have assigned a user into this group in AD. When this user creates a new table in this database, a new schema is created by SQL Server based on this user (the schema becomes <domain\user>). Also this new table is named based on schema, it becomes domain\user.tablename (<schema>.<table>). How can I avoid this behavior? I would like to give dbo schema as a default schema for his AD group but it seems impossible (I tried but I got an error message). |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
larus
Starting Member
17 Posts |
Posted - 2010-06-09 : 13:42:33
|
| Ok, thanks, but why SQL Server creates a new schema based on username (domain\user)? How this is prevented? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
larus
Starting Member
17 Posts |
Posted - 2010-06-09 : 13:51:17
|
| Many thanks. One further question. Would you suggest to give db_owner rights instead of db_ddladmin rights for a group (developers) because of this behavior? My fear is that when e.g. four developers create tables in this database, there will be problem with these "odd" schemas created by default by SQL Server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
larus
Starting Member
17 Posts |
Posted - 2010-06-09 : 14:02:06
|
| Yes, we're testing these groups and rights in the testing instance, and when we are "sure" that our user rights managing method works, we are planning to use this on production instance. Actually I did not understand what do you mean by saying "we do not create their schemas there"? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
larus
Starting Member
17 Posts |
Posted - 2010-06-10 : 01:38:08
|
| Ok, many thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Problem: new schema by user, logging via AD group