Using your schema in MS SQL
You follow an example of how to create an external table in MS SQL using your own schema.
-
Using MS SQL, create an external table based on a user-defined schema.
CREATE DATABASE world; USE world; CREATE SCHEMA bob; CREATE TABLE bob.country ( id int, name varchar(20) ); insert into bob.country values (1, 'India'); insert into bob.country values (2, 'Russia'); insert into bob.country values (3, 'USA'); CREATE SCHEMA alice; CREATE TABLE alice.country ( id int, name varchar(20) ); insert into alice.country values (4, 'Italy'); insert into alice.country values (5, 'Greece'); insert into alice.country values (6, 'China'); insert into alice.country values (7, 'Japan');
-
In MS SQL, create a user and associate them with a default schema.
CREATE LOGIN greg WITH PASSWORD = 'GregPass123!$'; CREATE USER greg FOR LOGIN greg WITH DEFAULT_SCHEMA=bob;
-
Allow the user to connect to the database and run queries. For example:
GRANT CONNECT, SELECT TO greg;