My program creates databases and tables at runtime. My understanding of Schema is it is a conceptual folder for multiple databases and databases tables. So I wrote some meaningless code just to test out what schema would do to my code.
My program use SQL Server Authentication instead of Windows Authentication, and create database and tables under the username of TmpUser instead of sa.
If I create a table by CREATE TABLE TmpTable, I get dbo.TmpTable for table name. If I explicitly type a new schema in CREATE TABLE abc.TmpTable, I then get the following exception because the schema does not exist:
SqlException: The specify schema name either does not exist or you do not have permission to use it
I went into SSMS and manually create a schema by executing CREATE SCHEMA abc. SSMS outputs saying the schema of abc has been successfully created. But in SSMS Object Explorer > Security > I see no Schema name nor anything else named abc.
Where is my Schema? If abc was not created, then why was CREATE SCHEMA abc executed and what did it create?
I went back to Visual Studio and CREATE TABLE abc.TmpTable again, still I receive the same exception.
Your TmpUser has no right to access the schema.
CREATE SCHEMA abc AUTHORIZATION TmpUser;
Quoted from https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql:
"AUTHORIZATION owner_name Specifies the name of the database-level principal that will own the schema. This principal may own other schemas, and may not use the current schema as its default schema."
TmpUser will own the schema, therefore will have permission to access it.
Explicitly granting permission to the TmpUser:
GRANT SELECT, UPDATE, DELETE, INSERT SCHEMA abc TO TmpUser;
See usage on https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql
It's like Option 1, but you can fine grain permissions.
Put TmpUser to some database roles, e.g. db_datareader:
USE MyDatabase
GO
ALTER ROLE db_datareader ADD MEMBER TmpUser
TmpUser will have read access to all schemas in the database.
It is similar to Option 3, but instead of using built-in roles, create your own one:
USE MyDatabase
GO
CREATE ROLE myrole
GRANT SELECT, DELETE, INSERT, UPDATE, EXECUTE TO myrole
ALTER ROLE myrole ADD MEMBER TmpUser
Users in myrole will have read/write/execute access to all schemas in the database.