Changing the default owner when creating objects

Published On: 2009-07-20By:

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.

Denny


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link