What is dbo in SQL Server?
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. My takeaways:
- dbo stands 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.
- schema is a named container for database objects, which allows us to group objects into separate namespaces. The schema is 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.
Server.Database.DatabaseSchema.DatabaseObject Enter fullscreen mode
Exit fullscreen mode
- 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: dbo , guest , sys , INFORMATION_SCHEMA .
- The sys and INFORMATION_SCHEMA schemas are reserved for system objects. We cannot create objects in these schemas and we cannot drop them.
- The dbo schema is the default schema of every database for all users. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema. The dbo schema is owned by the dbo user account.
- For example, the name of a table called orders owned by dbo is dbo.orders . If the table’s ownership is transferred to user abc , the table will now be named abc.orders .
- Users who are assigned the dbo as default schema don’t inherit the permissions of the dbo user account. No permissions are inherited from a schema by users; schema permissions are inherited by the database objects contained in the schema.
- the dbo user account is not the same as the db_owner fixed database role and the db_owner fixed 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 dbo without specifying the owner as long as the user has appropriate permission.
Объясните доходчиво что такое dbo
Здравствуйте.Нашел что db0 это используемая по умолчанию схема SQL Server. Схемы являются частью метаданных и предоставляются СУБД для конкретной БД. Как обращаться к схеме и о ее возможностях знаю например
SELECT TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM pubs.INFORMATION_SCHEMA.columns
WHERE table_name = ‘authors’
Можете теперь помочь разобраться в понимание понятия схемы.Что это такое и зачем она нужна.Так же прочел что можно создавать свои схемы.Вопрос зачем это делают и что это дает.
Зарание спасибо.
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
Объясните что такое INNER JOIN?
Объясните что такое INNER JOIN?
Кто может доходчиво объяснить, что такое Delegate?
Только не отсылайте меня пожалуйста на англоязычные сайты. Если это возможно — то вкратце своими.
Объясните доходчиво Exit и Halt
Ребят, я немного недопонимаю, в чем различия между Exit и Halt, если они оба завершают программу?
Объясните что такое k ?
Объясните пожалуйста что такое k. Чему равно его начальное значение? uses crt; var x:array of.
What’s dbo?
Don’t you hate it how certain words (well, acronyms in this case) get reused to mean multiple things? dbo seems to cause a great deal of confusion because of this.
First, dbo is an acronym standing for DataBase Owner. And there are three common usages for that I can think of off the top of my head. Feel free to point out others in the comments below and I’ll add them to the list ��
The actual owner of the database.
When talking about permissions this is the actual dbo. There is only one and they can’t be denied and they can do pretty much anything they want in the database.
Members of/have the permissions of the db_owner role.
“She’s dbo.” “He’s a member of dbo.” etc. This is a colloquial use of the term and isn’t technically accurate. The database owner (dbo) is not quite the same as the role db_owner. I generally prefer the more accurate terms (db_owner over dbo) but even I make mistakes here sometimes.
These first two are really not all that different. I’ve seen it create a little bit of confusion every now and again but not often and not much.
The schema.
This is where people start having difficulties. The schema dbo is the most commonly seen usage but it does not have anything to do with permissions. Schemas are containers that hold the objects within a database. They are third part of a fully defined four-part name (InstanceName.DatabaseName.SchemaName.ObjectName). The dbo schema is no different from the schema Sales. Well, other than the fact that most objects in almost every database are put in the dbo schema and I rarely see the Sales schema outside of AdventureWorks databases. Effectively though, they are both just schemas. The owner of the dbo schema has a lot of power, but only because so many objects are in that schema. If you create a database with a schema named Sales and put all of the objects under that schema rather than dbo then the owner of Sales has more power than the owner of dbo.
Obviously the difference between the permissions dbo and the schema dbo is pretty significant. In the end, you’ll have to use context to figure out which dbo you are working with, and if you aren’t sure then you need to look closer and/or ask questions.
dbo – Database Owner
In SQL Server, the dbo or Database Owner is a server-level principal that has full access to the owned database. Microsoft’s best practices recommend creating a discrete user, either an Active Directory domain user or group, or a SQL Server Authentication user, to use as the database owner. This post shows how to manage the dbo, or database owner.
Breakfast of a Young Man who is definitely not the dbo, or database owner! source
We need a Test Database
First, we’ll create a database:
Transact-SQL
IF EXISTS ( SELECT 1 FROM sys . databases d WHERE d . name = N ‘owner_demo’ )
ALTER DATABASE owner_demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
DROP DATABASE owner_demo ;
CREATE DATABASE owner_demo ;
We need a Login, Too!
Next, we’ll create a SQL Server login to serve as the database owner. Note, at this point the database above is owned by the login that created the database – us!
Transact-SQL
IF EXISTS ( SELECT 1
FROM sys . server_principals sp
WHERE sp . name = N ‘owner_demo_login’
AND sp . type_desc = ‘SQL_LOGIN’
DROP LOGIN owner_demo_login ;
CREATE LOGIN owner_demo_login
WITH PASSWORD = N ‘terrible_password’
, CHECK_POLICY = OFF
, CHECK_EXPIRATION = OFF ;
Changing the dbo, or database owner
Here’s the juicy bit, where we change the owner of the owner_demo database to owner_demo_login :
Transact-SQL
ALTER AUTHORIZATION on DATABASE :: owner_demo TO owner_demo_login ;
Now, let’s check the list of database principals to see who’s got access to it:
Transact-SQL
USE owner_demo ;
SELECT DatabasePrincipalName = dp . name
, [ Type ] = dp . type_desc
, dp . principal_id
FROM sys . database_principals dp
ORDER BY dp . name ;
╔═══════════════════════╦═══════════════╦══════════════╦════════════════════════════════════╗ ║ DatabasePrincipalName ║ Type ║ principal_id ║ sid ║ ╠═══════════════════════╬═══════════════╬══════════════╬════════════════════════════════════╣ ║ db_accessadmin ║ DATABASE_ROLE ║ 16385 ║ 0x01050000000000090400000000000. ║ ║ db_backupoperator ║ DATABASE_ROLE ║ 16389 ║ 0x01050000000000090400000000000. ║ ║ db_datareader ║ DATABASE_ROLE ║ 16390 ║ 0x01050000000000090400000000000. ║ ║ db_datawriter ║ DATABASE_ROLE ║ 16391 ║ 0x01050000000000090400000000000. ║ ║ db_ddladmin ║ DATABASE_ROLE ║ 16387 ║ 0x01050000000000090400000000000. ║ ║ db_denydatareader ║ DATABASE_ROLE ║ 16392 ║ 0x01050000000000090400000000000. ║ ║ db_denydatawriter ║ DATABASE_ROLE ║ 16393 ║ 0x01050000000000090400000000000. ║ ║ db_owner ║ DATABASE_ROLE ║ 16384 ║ 0x01050000000000090400000000000. ║ ║ db_securityadmin ║ DATABASE_ROLE ║ 16386 ║ 0x01050000000000090400000000000. ║ ║ dbo ║ SQL_USER ║ 1 ║ 0x0ABB8D9FCFB2DF4AACA93281390667F3 ║ ║ guest ║ SQL_USER ║ 2 ║ 0x00 ║ ║ INFORMATION_SCHEMA ║ SQL_USER ║ 3 ║ NULL ║ ║ public ║ DATABASE_ROLE ║ 0 ║ 0x01050000000000090400000083741. ║ ║ sys ║ SQL_USER ║ 4 ║ NULL ║ ╚═══════════════════════╩═══════════════╩══════════════╩════════════════════════════════════╝
If you look closely above, you’ll see the owner_demo_login is not listed. That’s because it’s listed as dbo , which I’ve highlighted in yellow. We can see the sid column for the dbo user matches up to the sid column in sys.server_principals for the owner_demo_login login with this query:
Transact-SQL
SELECT DatabaseName = d . name
, ServerPrincipalName = sp . name
, [ Type ] = sp . type_desc
, sp . principal_id
FROM sys . databases d
INNER JOIN sys . server_principals sp ON d . owner_sid = sp . sid
ORDER BY sp . name ;
╔══════════════╦═════════════════════╦═══════════╦══════════════╦════════════════════════════════════╗ ║ DatabaseName ║ ServerPrincipalName ║ Type ║ principal_id ║ sid ║ ╠══════════════╬═════════════════════╬═══════════╬══════════════╬════════════════════════════════════╣ ║ owner_demo ║ owner_demo_login ║ SQL_LOGIN ║ 280 ║ 0x0ABB8D9FCFB2DF4AACA93281390667F3 ║ ╚══════════════╩═════════════════════╩═══════════╩══════════════╩════════════════════════════════════╝
Test It!
We can “login” to the database without having to change our connection properties via the EXECUTE AS statement. That allows us to use the sys.fn_my_permissions dynamic management function to see the permissions the owner has in the database.
Transact-SQL
USE owner_demo ;
EXECUTE AS USER = N ‘dbo’ ;
SELECT UserName = USER_NAME ( )
, SystemLoginName = SUSER_SNAME ( ) ;
SELECT mp . entity_name
, mp . permission_name
FROM sys . fn_my_permissions ( NULL , N ‘DATABASE’ ) mp
The output first shows the results of USER_NAME() and SUSER_SNAME() to prove the permissions are for the owner_demo_login . That looks like:
╔══════════╦══════════════════╗ ║ UserName ║ SystemLoginName ║ ╠══════════╬══════════════════╣ ║ dbo ║ owner_demo_login ║ ╚══════════╩══════════════════╝
The permissions output looks like:
╔═════════════╦═══════════════════════════════════════════╗ ║ entity_name ║ permission_name ║ ╠═════════════╬═══════════════════════════════════════════╣ ║ database ║ CREATE TABLE ║ ║ database ║ CREATE VIEW ║ ║ database ║ CREATE PROCEDURE ║ ║ database ║ CREATE FUNCTION ║ ║ database ║ CREATE RULE ║ ║ database ║ CREATE DEFAULT ║ ║ database ║ BACKUP DATABASE ║ ║ database ║ BACKUP LOG ║ ║ database ║ CREATE DATABASE ║ ║ database ║ CREATE TYPE ║ ║ database ║ CREATE ASSEMBLY ║ ║ database ║ CREATE XML SCHEMA COLLECTION ║ ║ database ║ CREATE SCHEMA ║ ║ database ║ CREATE SYNONYM ║ ║ database ║ CREATE AGGREGATE ║ ║ database ║ CREATE ROLE ║ ║ database ║ CREATE MESSAGE TYPE ║ ║ database ║ CREATE SERVICE ║ ║ database ║ CREATE CONTRACT ║ ║ database ║ CREATE REMOTE SERVICE BINDING ║ ║ database ║ CREATE ROUTE ║ ║ database ║ CREATE QUEUE ║ ║ database ║ CREATE SYMMETRIC KEY ║ ║ database ║ CREATE ASYMMETRIC KEY ║ ║ database ║ CREATE FULLTEXT CATALOG ║ ║ database ║ CREATE CERTIFICATE ║ ║ database ║ CREATE DATABASE DDL EVENT NOTIFICATION ║ ║ database ║ CONNECT ║ ║ database ║ CONNECT REPLICATION ║ ║ database ║ CHECKPOINT ║ ║ database ║ SUBSCRIBE QUERY NOTIFICATIONS ║ ║ database ║ AUTHENTICATE ║ ║ database ║ SHOWPLAN ║ ║ database ║ ALTER ANY USER ║ ║ database ║ ALTER ANY ROLE ║ ║ database ║ ALTER ANY APPLICATION ROLE ║ ║ database ║ ALTER ANY COLUMN ENCRYPTION KEY ║ ║ database ║ ALTER ANY COLUMN MASTER KEY ║ ║ database ║ ALTER ANY SCHEMA ║ ║ database ║ ALTER ANY ASSEMBLY ║ ║ database ║ ALTER ANY DATABASE SCOPED CONFIGURATION ║ ║ database ║ ALTER ANY DATASPACE ║ ║ database ║ ALTER ANY EXTERNAL DATA SOURCE ║ ║ database ║ ALTER ANY EXTERNAL FILE FORMAT ║ ║ database ║ ALTER ANY MESSAGE TYPE ║ ║ database ║ ALTER ANY CONTRACT ║ ║ database ║ ALTER ANY SERVICE ║ ║ database ║ ALTER ANY REMOTE SERVICE BINDING ║ ║ database ║ ALTER ANY ROUTE ║ ║ database ║ ALTER ANY FULLTEXT CATALOG ║ ║ database ║ ALTER ANY SYMMETRIC KEY ║ ║ database ║ ALTER ANY ASYMMETRIC KEY ║ ║ database ║ ALTER ANY CERTIFICATE ║ ║ database ║ ALTER ANY SECURITY POLICY ║ ║ database ║ SELECT ║ ║ database ║ INSERT ║ ║ database ║ UPDATE ║ ║ database ║ DELETE ║ ║ database ║ REFERENCES ║ ║ database ║ EXECUTE ║ ║ database ║ ALTER ANY DATABASE DDL TRIGGER ║ ║ database ║ ALTER ANY DATABASE EVENT NOTIFICATION ║ ║ database ║ ALTER ANY DATABASE AUDIT ║ ║ database ║ ALTER ANY DATABASE EVENT SESSION ║ ║ database ║ KILL DATABASE CONNECTION ║ ║ database ║ VIEW ANY COLUMN ENCRYPTION KEY DEFINITION ║ ║ database ║ VIEW ANY COLUMN MASTER KEY DEFINITION ║ ║ database ║ VIEW DATABASE STATE ║ ║ database ║ VIEW DEFINITION ║ ║ database ║ TAKE OWNERSHIP ║ ║ database ║ ALTER ║ ║ database ║ ALTER ANY MASK ║ ║ database ║ UNMASK ║ ║ database ║ EXECUTE ANY EXTERNAL SCRIPT ║ ║ database ║ CONTROL ║ ╚═════════════╩═══════════════════════════════════════════╝
As you can see, owner_demo_login has complete access to the database.
Who owns what?
Use the following simple query to determine which logins own databases: