A few questions on Schema

SQL Server Administration
1

I have been playing with schema and I think I finally understood what it is used for. Basically, how I understand the concept of schema is a way to create miniature databases within a database. In other words, it is really used for segregation of objects (tables, stored procedures, etc...) for security purpose.

Is this pretty much it? Any other usage?

How I would use schema is based on departments (HR, IT, Accounting, Marketing, etc...) Is this the thinking and usage?

This brings me to the next question. There are debates to avoid using schema. What are your thoughts on this? If schema are not used, what is the alternative?


Last when it comes to implementing schema, there are two methods in the following link where I can assign a schema directly to a user or a schema to a database role. Should I use a schema with a database role which I think is the correct way?

2

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server

From Microsoft itsefl.

  1. I think of it more from a logical way of grouping things. Security is another side benefit of it.

  2. If schemas are not used
    You can create separate databases for hr, it etc
    Or you could add a departmenttype column that is a fk to a departmenttypes table

  3. Best to always assign role based security

My 2 cents

1 Like