A typical pattern is schemas based on permissions, so you'd have WebGUI
, Desktop
etc for code so all objects have the same permissons from the schema.
If you have clear user groups then you can permission on that, but you'll end up with overlapping and messy permissions at some point. I tend to defer the user/group checks to some check inside code and not permissions objects: say you have Admin and HR Excel users: these all run Desktop
code.
Data is usually shared so I'd have a Data
schema, maybe a History
or Archive
schema.
Some code isn't public (like a UDF or internal proc) so I'd use a Helper
schema for code that shouldn't be run by client code.
Finally, schemas like Staging
or System
or Maintenance
are useful sometimes.
Although there are no user objects in the dbo
schema, the user dbo
owns all the schemas.