SQL Server--What is the db_datreader/db_datawriter/db_owner/... "Schema" Useage?

只是為了可以向下兼容之前的SQL Server版本.

對於新版SQL Server沒有用途, 效果與dbo一樣

不過, 使用這些Schema意味著還將附加帶有隱藏的權限

Those default schemas such as db_datareader, db_datawriter etc. are for backwards compatibility reasons.

In SQL Server 2000, schema is as the same as the user and the schema must be created by the corresponding user.

So if migrated from 2000, it must be match each other otherwise there is an error.

But the schema and fixed roles are not necessary to be there.

The database fixed roles such as “db_datareader” are owned by dbo.

But the schemas are ownded by corresponding roles. Both of the above are re-configurable.

“db_datareader” and “db_datawriter” can perform SELECT/INSERT/DELETE/UPDATE operation on any table or view with any schema which due to “Implicit Permissions” of fixed roles.

But in SSMS, for an user with db_datareader role, he can only view the tables/views with the schema db_datareader or dbo or other schemas owned by db_datareader or dbo or the user itself.

參考來源:

https://tonybigworld.wordpress.com/2012/09/28/sql-server-2008-schema-logins-and-roles/

tags: SQL Server

• ← SQL Server--使用另一個Windows Account操作SSMS, 錯誤訊息 : 請輸入用於c:\program file\sql server management studio 18\common7\ide\ssms.exe
• SQL Server--The OLE DB provider Microsoft.ACE.OLEDB.12.0 has not been registered error →