Posted by: Denny Cherry
Permissions, SQL Server 2005, SQL Server 2008, T/SQL, Tables
When a user that doesn’t have sysadmin rights creates objects by default they will be created in the schema that is the users default schema. Now the catch to this is that if you grant the user rights into the database via a domain group that domain group then the user doesn’t have a default schema.
So, now how do you fix this? Unfortunately the only fix to this is to grant the users Windows login as a separate login, then grant this login rights into the database. You can then grant the user which is mapped directly to the users Windows login a default schema of dbo.
Because of this the user should specify the schema when creating objects.
The downside to this is that they won’t be able to use the object editor to create new tables. All new tables will need to be created in T/SQL directly.