I used to learn about SQL, especially MySQL. Then now my job requires me to understand Microsoft SQL Server (mssql). So I make these notes to guide me and others who want to transition to mssql. I usually summarized what I found and understood so far from browsing the internet.
dbostands for DataBase Owner.
We can't remove privileges from an object owner and we can't drop users from a database if they own objects in it.
schemais a named container for database objects, which allows us to group objects into separate namespaces. The
schemais the database object that owns the table. For example, the AdventureWorks sample database contains schemas for Production, Sales, and HumanResources.
The four-part naming syntax for referring to objects specifies the schema name.
Schemas can be owned by any database principal and a single principal can own multiple schemas. Principals are entities that can request SQL Server resources.
Schemas that contain objects cannot be dropped. The following schemas cannot be dropped:
INFORMATION_SCHEMAschemas are reserved for system objects. We cannot create objects in these schemas and we cannot drop them.
dboschema is the default schema of every database for all users. By default, users created with the
CREATE USERTransact-SQL command have
dboas their default schema. The
dboschema is owned by the
For example, the name of a table called orders owned by
dbo.orders. If the table’s ownership is transferred to user
abc, the table will now be named
Users who are assigned the
dboas default schema don't inherit the permissions of the
dbouser account. No permissions are inherited from a schema by users; schema permissions are inherited by the database objects contained in the schema.
dbouser account is not the same as the
db_ownerfixed database role and the
db_ownerfixed database role is not the same as the user account that is recorded as the owner of the database.
The default schema for a user is solely used for object-reference in case the user omits the schema when querying objects. Users in the database will be able to access any object owned by
dbowithout specifying the owner as long as the user has appropriate permission.
Top comments (0)