01 November, 2020

Creating a Schema and not having to grant db_datareader and db_datawriter

 It seems that using the default "dbo" as the owner of objects in the database is still an active practice.  And it seems that some developers still default to granting "db_datareader" and "db_datawriter".

Here I show that it is not necessary to use "dbo" as the schema and certainly not necessary "db_datareader"and "db_datawriter".

Microsoft introduced the option to create a Schema (a collection of database objects) with a name other than "dbo" some versions ago.

Thus, the default schemas are visble as "dbo", "guest", "sys" and "INFORMATION_SCHEMA" in this database called "second_db" :

Listing of Schemas


I can create a new schema ("imventory" and one table ("inventory_master") in it.  I have refreshed the listing of database tables, users and schemas :

CREATE Statements


I can associate an existing SQL Login "hemant_sql" with the "inventory" schema in this database called "second_db" :

Login associated with Schema in Database


With that, the database user "hemant_sql" is created in "second_db".  I can now grant privileges specifically on this new table without having granted "db_datareader" and "db_datawriter"


Specify Grants for the new User


Note how the user does not have any roles :

Listing of Role Memberships for "hemant_sql"


The user can only view the target table and not any other table in the database :

Tables accessible by the Use



"hemant_sql" can now connect to the database and insert into the target table 

Execute INSERT statement



No comments:

Post a Comment