I've been spending the last few days cleaning up database permissions, grouping them all into AD groups and removing orphaned users.

This has lead me to a recurring scenario:

  1. Find a user that owns a schema
  2. Transfer schema ownership to SA
  3. Remove user

The schema's are not subject to regular change, and most of the time it's a user owning the dbo schema.

I've been researching what the reasoning might be behind giving these users ownership of schema s, however I can't really find a proper reason. Most documentation speaks of schema membership, using schema's for permissions, but I'm hard pressed finding much about schema ownership.

What might be a correct reason for giving users ownership of schema's?
Are there any hidden dangers in removing schema ownership from orphaned users?

  • 1
    Schema's are a mechanism for fine tuning security on objects. However, in my experience, most schemas are used merely for identifying groups of objects for a specific purpose. I would add a couple steps to your to do list, 1.5 check to see if there are any objects owned by the schema and 1.75 are there any special permissions defined for the schema. You'll run across 1.5 more often than 1.75, but you'll want to check these before just transferring ownership to the most powerful owner on the box. – Steve Mangiameli Jun 25 '15 at 19:13
  • @SteveMangiameli You lost me somewhat there. The only way I see the owner of a schema being changed having an impact is if the code that pulls data from the schema does not use the fqdn and instead relies on the users schema definitions. However if the user is already an orphan that can't be a problem any more? – Reaces Jun 29 '15 at 7:29
  • Sorry Reaces, I should have answered the original question. The owner is not important. At least, like you I have not found that ownership, has mattered much. In an environment where it would be possible to have Steve and Reaces as schema owners, but neither having permissions to access each others objects, it might be important. But that is what I was getting at - schemas aren't typically used this way. As for the additional checks, I misread what you were doing; object ownership would only be important if you were removing the schema. – Steve Mangiameli Jun 29 '15 at 19:41

An owner of a database object has full rights on it automatically and unrevokably. But of course you can give an user all rights to a schema without making him the owner.

|improve this answer|||||
  • While this is a consequence of the action, I can't justify it being a good reason to grant schema ownership. And it's definitely not a risk when removing orphaned users. +1 though. – Reaces Jul 1 '15 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.