USE MyDB
GO

-- create two schemas
CREATE SCHEMA A;
GO
CREATE SCHEMA B;
GO

-- create a table in each schema
CREATE TABLE A.TestA(id INT);
CREATE TABLE B.TestB(id INT);
GO

-- create a user in the database (only public role, no other perms)
CREATE USER [TestPermUser] FOR LOGIN [TestPermUser]
GO

-- change context to the new user
EXECUTE AS USER = 'TestPermUser';
GO

-- try to select - both selects should fail.
SELECT * FROM A.TestA;
SELECT * FROM B.TestB;
GO

-- revert back to the original context.
REVERT;
GO

-- grant permission on schema a to the user
GRANT SELECT ON SCHEMA::A TO TestPermUser
GO

-- change context again to the new user
EXECUTE AS USER = 'TestPermUser';
GO

-- can select from A.TestA, but not from B.TestB
SELECT * FROM A.TestA;
SELECT * FROM B.TestB;
GO

REVERT;
GO

quote:

---

If I change this, what is the impact on the user associated wtih the application? Is it just a matter of adding the application login to the schema permission?

---

quote:

---

What about new tables added to the database by the software developer? Do they need to be manually added to the schema once created?

---